Discussion :

[Marcel Chabot]

Bonjour je suis avec Oracle 10G.

Je veux créer une procédure générique pour l'ensemble de tables afin de faire des comparaisons de données.

J'ai plusieurs tables et je ne veux pas faire une procédure pour chaque table.
Ma procédure générique aura en paramètre le nom d'une table et un ou plusieurs clé selon la table.
Donc je dois faire appel au Dictionnaire Oracle afin de sortir toute mon information pour les noms de colonnes et ensuite les données.
Je vais utiliser des vecteurs pour les noms de colonnes et un autre vecteur pour les données. Sure que je vais faire une boucle pour avoir toutes les lignes de la table.

Je pense avoir trouvé la façon de mettre les noms de colonnes dans un vecteur.

Mais c'est une autre histoire pour les données...
Est-ce que je vais devoir créer le vecteur pour les données et faire un curseur dynamique pour remplir ce dernier???
Est-ce que je peux comme les nom de colonnes faire un 'EXTEND' pour avoir les valeurs?
Mon premier vecteur est-il correct?
J'aurais besoin d'un bon coup de main là-dessus ;-)

Merci!

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
declare 
 
CURSOR lname_cur IS
   SELECT column_name
           FROM all_tab_columns
          WHERE owner = 'DEV'
            AND table_name = 'I_CARDINAL' ;
 
type lastname_type is varray(10) of ALL_TAB_COLUMNS.COLUMN_NAME%type;
last_name_varray lastname_type := lastname_type();
 
v_counter integer :=0;
 
begin
for name_rec in lname_cur loop
   v_counter := v_counter + 1;
   last_name_varray.EXTEND;
   last_name_varray(v_counter) := name_rec.column_name;
   DBMS_OUTPUT.PUT_LINE(last_name_varray(v_counter));
   end loop;
   end;

[SheikYerbouti]

les varrays ont un nombre d'éléments fixé dès la déclaration, donc ce n'est pas un choix judicieux. Sans chercher à comprendre le pourquoi du comment, il faut utiliser un autre type de collection plus dynamique commes les tables PL/SQL INDEX BY

Sinon, quel est le but de cette procédure ?
Pouvez-vous donner un example concret ?

[Marcel Chabot]

J'ai mis en pièce jointe ma procédure que j'ai fais pour deux tables en particulier.
C'est deux table sont identiques. La première est source et la deuxième est historique. La seule différence est que la table historique a 4 champs supplémentaires afin de pouvoir faire un historique d'une ligne qui a été modifié dans la table source.
Donc si un changement est apporté ou une nouvelle ligne dans la table source alors il y aura soit un insert, update dans la table historique.
On compare la table source et la table historique une fois semaine.
En gros si une modification est faite dans la source alors on insère une nouvelle ligne dans l'historique avec les nouvelle données et on met un indicateur a 1 pour actif (c’est une des colonnes qui est en plus dans la table historique) et l'indicateur de l'ancienne ligne dans l'historique à 0 pour la désactiver.
Le but est de faire une photo de la table source à un moment donné dans une table historique.
Je pourrais faire 75 procédures pour mes 75 tables sources et 75 tables historiques dans ma base de données avec tous les ‘select columns from table’ dans toutes les procédures mais c’est long !!!!
On me demande de faire une procédure qui va recevoir en paramètre les noms de tables et qui va faire la même action soit de comparer les données et si modification ou nouvelle ligne dans la table source alors insert ou update dans la table historique.
J’ai pensé à toucher au Dictionnaire Oracle qui me permet d’aller chercher les noms de colonnes sans les nommées et ensuite les données. Pour ensuite les comparer et faire les modifications appropriées dans la table historique.
C’est pour ça que j’ai penser au vecteurs.

[Vincent Rogier]

ou alors utiliser des nested tables (collection SQL)

[Waldar]

La procédure sur laquelle vous êtes parti n'est absolument pas performante, il me semble que ce conseil vous a déjà été donné dans une autre discussion.

L'idée c'est de chercher à faire de l'ensembliste.
Je n'ai pas réussi à le faire en une requête mais en deux (à cause du numéro de version).

À partir du nom de table, vous devez récupérer toutes les colonnes ainsi que celles qui forment la clef primaire (en utilisant les vues systèmes).

Après vous pourrez exécuter, en execute immediate ces deux requêtes :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
 merge into <tbl>_H hst
 using (select <list_column>
          from <tbl>
         minus
        select <list_column>
          from <tbl>_H
         where INDIC_ACTIF_H = 1) mvt
    ON (mvt.<pk> = hst.<pk>)
  when matched 
update hst.HORODATE_DESACTIVATION_H = trunc(sysdate)
       hst.INDIC_ACTIF_H = 0
 where hst.INDIC_ACTIF_H = 1
insert (hst.<list_column>, hst.HORODATE_AJOUT_H, hst.HORODATE_DESACTIVATION_H, hst.INDIC_ACTIF_H)
values (mvt.<list_column>, trunc(sysdate)      , date '3999-12-31'           , 1                )
  when not matched 
insert (hst.<list_column>, hst.HORODATE_AJOUT_H, hst.HORODATE_DESACTIVATION_H, hst.INDIC_ACTIF_H, hst.NO_VERSION_H)
values (mvt.<list_column>, trunc(sysdate)      , date '3999-12-31'           , 1                , 1               );
 
 
update <tbl>_H h1
   set h1.NO_VERSION_H = (select count(*)
                            from <tbl>_H h2
                           where h2.<pk> = h1.<pk>)
 where h1.NO_VERSION_H is null
   and h1.INDIC_ACTIF_H = 1;

Je n'ai pas fait de test parce qu'il est tard, mais c'est la direction à suivre.

Les deux principaux intérêts de ces requêtes par rapport à ce que vous avez écrit :

1. Ensembliste ! Toutes les données sont traitées en même temps.
2. Utilisation de minus qui évite la fastidieuse comparaison colonne par colonne

[mnitu]

Pour une des tables à historiser faite la procédure de mise à jour en suivant les conseils du @waldar. Testez et validez le principe de fonctionnement. Ensuite créez en outil pour générer le code de la procédure standard de sauvegarde en utilisant le sql dynamique.
Pour chaque nouvelle table à historiser ou suite à des modifications des structures des tables déjà historisées lancez l’outil pour recréer les procédures de historisation automatiquement.