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.

[Marcel Chabot]

Je ne peux pas utiliser MINUS car mes deux tables n'ont pas les mêmes nombres de colonnes.

Table source : CODE_PAYS, (pk)
CODE_REGION_MONDIALE,
NOM_LONG_PAYS,
NOM_PAYS

Table historique : CODE_PAYS, (pk)
CODE_REGION_MONDIALE,
NOM_LONG_PAYS,
NOM_PAYS,
NO_VERSION_H, (pk)
HORODATE_AJOUT_H,
HORODATE_DESACTIVATION_H,
INDIC_ACTIF_H

Le code va toujours me ramener Faux.

On peut vraiment mélanger les données venant du dictionnaire Oracle (colonnes et pk) et en même temps les données dans les tables sans passer par une requête dynamique?

Code :

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

1
2
3
4
SELECT column_name
           FROM all_tab_columns
          WHERE owner = 'DEV'
          AND table_name = 'I_PAYS';

Donne les noms de colonnes

Code :

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

1
2
3
4
5
6
7
SELECT cols.column_name  
    FROM all_constraints cons, all_cons_columns cols
    WHERE cols.table_name = 'I_PAYS'
    AND cons.constraint_type = 'P'
    AND cons.constraint_name = cols.constraint_name
    AND cons.owner = 'DEV'
    ORDER BY cols.table_name, cols.position;

Donne la ou les primary key

[Waldar]

Je ne peux pas utiliser MINUS car mes deux tables n'ont pas les mêmes nombres de colonnes.

D'où l'utilité de rechercher la liste des colonnes sur la table de base, toutes les colonnes de celle-ci existent dans la table d'historique si j'ai bien compris votre modèle.

On peut vraiment mélanger les données venant du dictionnaire Oracle (colonnes et pk) et en même temps les données dans les tables sans passer par une requête dynamique ?

Non, c'est pour ça que j'ai bien parlé de construire une requête en rentrant ces informations !

Les deux requêtes que vous avez écrites n'étant pas coûteuses, vous pouvez faire un curseur dessus pour construire la chaîne de caractères nécessaire.

[Marcel Chabot]

Merci je comprends la logique de tout ce que j'ai à faire.

J'ai un petit bug dans cette requête.
Je ne peux faire un insert dans un «when matched» mais seulement dans un «when not matched». C'est possible?
J'ai fouillé pas mal sur le net et rien trouvé comme exemple :-(
Je suis obligé d'insérer car j'ai trouvé une différence dans le MINUS

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
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
 
merge INTO I_PAYS_B hst
       USING (SELECT CODE_PAYS, CODE_REGION_MONDIALE,
                             NOM_LONG_PAYS, NOM_PAYS
                    FROM I_PAYS_A
                    minus
                  SELECT CODE_PAYS, CODE_REGION_MONDIALE,  
                             NOM_LONG_PAYS, NOM_PAYS
                    FROM I_PAYS_B 
                  WHERE INDIC_ACTIF_H = 1) mvt
       ON (mvt.CODE_PAYS = hst.CODE_PAYS)
  when  matched then
     UPDATE SET hst.INDIC_ACTIF_H = 0
          WHERE hst.INDIC_ACTIF_H = 1
              AND EXISTS (SELECT NULL
                                   FROM (SELECT CODE_PAYS,  
                                                        CODE_REGION_MONDIALE, 
                                                        NOM_LONG_PAYS, NOM_PAYS,  1, 1
                                             FROM
                                                     (SELECT CODE_PAYS,
                                                                 CODE_REGION_MONDIALE,  
                                                                 NOM_LONG_PAYS,
                                                                 NOM_PAYS
                                                         FROM I_PAYS_A
                                                        MINUS
                                                       SELECT CODE_PAYS, 
                                                                  CODE_REGION_MONDIALE, 
                                                                  NOM_LONG_PAYS,
                                                                  NOM_PAYS
                                                         FROM I_PAYS_B 
                                                       WHERE INDIC_ACTIF_H = 1
                                                     )
                                           ) SR
                            WHERE hst.code_pays = sr.code_pays)
  when NOT matched then
      INSERT ( CODE_PAYS, CODE_REGION_MONDIALE,NOM_LONG_PAYS,     
                   NOM_PAYS, no_version_h, INDIC_ACTIF_H) 
       values (SELECT CODE_PAYS, CODE_REGION_MONDIALE, 
                             NOM_LONG_PAYS, NOM_PAYS, 1,1
                   FROM
                          (SELECT CODE_PAYS, CODE_REGION_MONDIALE, 
                                      NOM_LONG_PAYS, NOM_PAYS
                            FROM I_PAYS_A
                           MINUS
                          SELECT CODE_PAYS, CODE_REGION_MONDIALE, 
                                     NOM_LONG_PAYS, NOM_PAYS
                            FROM I_PAYS_B 
                          WHERE INDIC_ACTIF_H = 1
                        );

[Waldar]

Effectivement, j'ai confondu avec delete.

Dans ce cas, séparer le merge en deux ordres :

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
26
27
28
29
-- Désactivation des données qui ont changées
UPDATE I_PAYS_B hst
   SET hst.INDIC_ACTIF_H = 0,
       hst.HORODATE_DESACTIVATION_H = trunc(sysdate)
 WHERE hst.INDIC_ACTIF_H = 1
   AND EXISTS (SELECT NULL
                 FROM (<le_minus>) SR
                WHERE hst.<pk> = sr.<pk>);
 
-- Insertion des nouvelles données
INSERT INTO I_PAYS_B hst (CODE_PAYS, CODE_REGION_MONDIALE, NOM_LONG_PAYS, NOM_PAYS, hst.HORODATE_AJOUT_H, hst.HORODATE_DESACTIVATION_H, INDIC_ACTIF_H)
SELECT CODE_PAYS, CODE_REGION_MONDIALE, NOM_LONG_PAYS, NOM_PAYS, trunc(sysdate), date '3999-12-31', 1
  FROM
(
SELECT CODE_PAYS, CODE_REGION_MONDIALE, NOM_LONG_PAYS, NOM_PAYS
  FROM I_PAYS_A
 MINUS
SELECT CODE_PAYS, CODE_REGION_MONDIALE, NOM_LONG_PAYS, NOM_PAYS
  FROM I_PAYS_B 
 WHERE INDIC_ACTIF_H = 1
);
 
-- Et toujours la mise à jour du numéro de version :
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;

[Marcel Chabot]

Bonjour le UPDATE fonctionne très bien avec les modifications mais pas le INSERT.

On dirais que le MERGE n'accepte pas un sous SELECT dans un INSERT

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
26
27
28
29
30
31
32
33
34
merge INTO I_PAYS_B hst
 USING (SELECT CODE_PAYS, CODE_REGION_MONDIALE, NOM_LONG_PAYS, NOM_PAYS
          FROM I_PAYS_A
          minus
        SELECT CODE_PAYS, CODE_REGION_MONDIALE, NOM_LONG_PAYS, NOM_PAYS
          FROM I_PAYS_B 
         WHERE INDIC_ACTIF_H = 1) mvt
    ON (mvt.CODE_PAYS = hst.CODE_PAYS)
  when  matched then
    UPDATE SET hst.INDIC_ACTIF_H = 0
     WHERE hst.INDIC_ACTIF_H = 1
          AND EXISTS (SELECT NULL
                                FROM (SELECT CODE_PAYS, CODE_REGION_MONDIALE, NOM_LONG_PAYS, NOM_PAYS,  1, 1
                                             FROM
                                                     (SELECT CODE_PAYS, CODE_REGION_MONDIALE, NOM_LONG_PAYS, NOM_PAYS
                                                         FROM I_PAYS_A
                                                        MINUS
                                                       SELECT CODE_PAYS, CODE_REGION_MONDIALE, NOM_LONG_PAYS, NOM_PAYS
                                                         FROM I_PAYS_B 
                                                       WHERE INDIC_ACTIF_H = 1
                                                     )
                                           ) SR
                          WHERE hst.code_pays = sr.code_pays)
  when NOT matched then
  INSERT ( CODE_PAYS, CODE_REGION_MONDIALE,NOM_LONG_PAYS, NOM_PAYS, no_version_h, INDIC_ACTIF_H) 
   values (SELECT CODE_PAYS, CODE_REGION_MONDIALE, NOM_LONG_PAYS, NOM_PAYS, 1,1
                FROM
                        (SELECT CODE_PAYS, CODE_REGION_MONDIALE, NOM_LONG_PAYS, NOM_PAYS
                            FROM I_PAYS_A
                           MINUS
                          SELECT CODE_PAYS, CODE_REGION_MONDIALE, NOM_LONG_PAYS, NOM_PAYS
                            FROM I_PAYS_B 
                          WHERE INDIC_ACTIF_H = 1
                        );

[Waldar]

Vous n'avez pas compris, on n'utilise plus le MERGE dans la dernière solution !

Juste l'update, juste l'insert (sans le mot VALUES quand on utilise une sous-requête), puis un dernier update pour mettre à jour le numéro de version.