Discussion :

[achraf87]

bonjour

je débute avec oracle PL/SQL

j'ai une tache a réalisé dans mon stage

j'ai besoin d'une procedure stockée paramétrée qui permet de faire l'archivage des données a partir d'une bd oracle de production vers une autre bd oracle d'archivage

" copier les données de la 1 ere bd vers la 2 eme bd "

sachant que les 2 bd ont la même structure

Merci

[Marc Musette]

hummm ... et tu connais les critères d'archivage de tes données ? parce que moi, pas ...

[McM]

Une procédure qui fait tout ça.. mouis c'est possible... mais ça risque d'être long.
S'il faut que ce soit dynamique (qui continue à marcher même en cas de modification de table), faut que tu passes par de l'execute immediate.

En gros:
1 Curseur sur les tables à archiver
1 curseur pour connaitre la pk
1 curseur pour avoir la liste des colonnes
faire un delete, un update, et un insert des données en fonction des pk et des colonnes, le tout en execute immediate.

Vu que les 2 bases sont différentes et que tu vas passer par un dblink, fait plutôt des minus que des exists, plus rapide (selon mon expérience)

[Mohamed.Houri]

bonjour

je débute avec oracle PL/SQL

j'ai une tache a réalisé dans mon stage

j'ai besoin d'une procedure stockée paramétrée qui permet de faire l'archivage des données a partir d'une bd oracle de production vers une autre bd oracle d'archivage

" copier les données de la 1 ere bd vers la 2 eme bd "

sachant que les 2 bd ont la même structure

Merci

Est-ce que les tables à archiver sont partitionées?

[achraf87]

bonjour Mr mohamed


je t'explique ma tache
j'ai 2 bases oracle 10 g de même structure une de production contient les données et l'autre pour l'archivage

j'ai besoin d'une procédure stockée paramétrée qui permet de faire l'archivage ( transférer les données de bd production vers bd archive) cette procédure sera lancer par un job/batch

cordialement

[achraf87]

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
50
51
52
53
54
55
56
57
58
59
60
61
CREATE OR REPLACE PROCEDURE archivage ( source IN VARCHAR2, destination IN VARCHAR2) 
     IS 
     source_cursor INTEGER; 
     destination_cursor INTEGER ; 
     ignorer INTEGER;
     i integer;
  BEGIN 
 
 /* - Préparer un curseur pour sélectionner à partir de la bd_source: */
     source_cursor: = dbms_sql.open_cursor; 
     DBMS_SQL.PARSE (source_cursor, 
         'SELECT table_name FROM dba_tables' | | source,DBMS_SQL.NATIVE);
         LOOP i in source_cursor
         /* soit j'utilise cette req 
         SELECT object_name FROM  dba_objects WHERE owner = 'SCOTT' AND object_type = 'TABLE'*/
 
     DBMS_SQL.DEFINE_COLUMN (source_cursor, i); 
   end loop
     ignorer: DBMS_SQL.EXECUTE = (source_cursor); 
 
 /* - Préparer un curseur à insérer dans la bd de destination: */
     destination_cursor: = DBMS_SQL.OPEN_CURSOR; 
     DBMS_SQL.PARSE (destination_cursor, 
                  'INSERT INTO' | | destination | |  DBMS_SQL.NATIVE); 
 
 /* - Récupère une ligne de la table source et l'insérer dans la table de destination: */
     loop
       if  DBMS_SQL.FETCH_ROWS (source_cursor)> 0 ALORS 
         - Obtenir des valeurs de colonne de la ligne 
         DBMS_SQL.COLUMN_VALUE (source_cursor,i); 
 
 
  /*- Rabattre la ligne dans le curseur qui s'insère dans la table de destination. Vous
  - Pourrait modifier cet exemple pour exiger l'utilisation de SQL dynamique en insérant un 
  - Si létat avant la liaison.*/ 
        DBMS_SQL.BIND_VARIABLE (destination_cursor,i); 
 
 
        ignorer: DBMS_SQL.EXECUTE = (destination_cursor); 
      ELSE 
 
 /* - Pas plus de lignes à copier: */
        EXIT; 
      End If; 
    END LOOP; 
 
  /*-fermer les curseurs: */
     COMMIT; 
     DBMS_SQL.CLOSE_CURSOR (source_cursor); 
     DBMS_SQL.CLOSE_CURSOR (destination_cursor); 
   EXCEPTION 
     WHEN OTHERS, THEN
       if
         DBMS_SQL.IS_OPEN (source_cursor)then
         DBMS_SQL.CLOSE_CURSOR (source_cursor); 
       End If; 
       if DBMS_SQL.IS_OPEN (destination_cursor) then 
         DBMS_SQL.CLOSE_CURSOR (destination_cursor); 
       End If; 
       RAISE; 
  END;

Mais dans cette procédure je suis coincé sur le traitement des curseurs : le curseur qui parcours et sélectionne les données de chaque table et le curseur qui fait l'insertion des données.

Si quelqu'un peut m'aider !

cordialement

[Mohamed.Houri]

bonjour Mr mohamed


je t'explique ma tache
j'ai 2 bases oracle 10 g de même structure une de production contient les données et l'autre pour l'archivage

j'ai besoin d'une procédure stockée paramétrée qui permet de faire l'archivage ( transférer les données de bd production vers bd archive) cette procédure sera lancer par un job/batch

cordialement

Laissez-moi supposer que vos tables à archiver ne sont pas partitionnées. Dans ce case je dirai que votre procédure stockée doit s'évertuer à faire pour chaque table à archiver un code qui ressemble à ceci

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
insert into local_archived_table
      (col1
       ,col2
       ,col3)
select   
      col1
      ,col2
      ,col3
from distant_table_to_archive   dtac
where not exists (select null
                        from local_archived_table latb
                        where latb.pk = dtac.pk
);

Vous allez lancer la procédure à partir de la base archive qui, elle, va sélectionner les données (via un database link) de la base de production.

La clause not exists sert à éviter d’archiver ce qui l’a déjà été.

Si votre table local_archived_table ne possède pas de trigger (ce qui est fort probable) et qu’elle ne possède pas de contrainte d’intégrité (qui peut aussi se comprendre) et qu’elle ne subit jamais de suppression de données (ce qui est logique sinon ????) alors vous pouvez utiliser un ''direct path load'' qui accélérera votre insert sans conséquence sur le high water mark et sur la réutilisation de l’espace libre:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
insert /*+ append */ into local_archived_table
         (col1
         ,col2
         ,col3)
select   
           col1
          ,col2
          ,col3
from distant_table_to_archive   dtac
where not exists (select null
                        from local_archived_table latb
                        where latb.pk = dtac.pk
);

Si vous voulez que vos inserts se fassent malgré l’apparition de certaines erreurs, je vous conseille d’ajouter un ''DML error logging'' (disponible à partir de 10gR2) comme suit :

Code :

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

1
2
3
4
5
6
 
-- Create the error logging table.
BEGIN
  DBMS_ERRLOG.create_error_log (dml_table_name => local_archived_table);
END;
/

Et changez alors votre insert/select initial comme suit:

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
 
insert /*+ append */  into local_archived_table
      (col1
       ,col2
       ,col3)
select   
       col1
      ,col2
      ,col3
from distant_table_to_archive   dtac
where not exists (select null
                        from local_archived_table latb
                        where latb.pk = dtac.pk)
log errors into err$_ local_archived_table reject limit unlimited;
;

[achraf87]

je dois créer un script pour chaque table !!
mais non je crois pas
je veux une proc qui interroge toutes les table de la bd
source faire la sélection des données et les insérées ds la bd archive
j'ai postulé ma proc mai je bloque sur les curseurs

[Scriuiw]

je dois créer un script pour chaque table !!
mais non je crois pas

C'est au choix !
- Soit tu fais une procédure pour chaque table ce qui est, avouons-le, fastidieux et contraignant car si le modèle bouge, tu dois impacter le package.
- Soit tu fais une procédure entièrement dynamique ce qui permet d'avoir un package s'appuyant directement sur le modèle et prendre en compte automatiquement les modifications de ce dernier.

La première solution est longue à écrire et pénible à maintenir tandis que la seconde est plus complexe à mettre en place mais plus flexible.

Tout dépend aussi des "contraintes" que tu as/choisis pour ton archivage :
- Sauvegarde du différentiel
- Sauvegarde périodique
- Annule et remplace

Ton algorithme doit ressembler à quelque chose de ce style

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
DECLARATION
CURSEUR tables_to_save
 
DEBUT
  BOUCLE sur tables_to_save
    [..]
    EXECUTE 'INSERT INTO '||Table_destination||' (SELECT * FROM '||Table_source||')';
    [..]
  FIN BOUCLE;
FIN;
/

Après comme je l'ai évoqué, la complexité de l'insert va dépendre de ce qu'il te faut.
Si c'est de l'annule et remplace alors tu purges la table (TRUNCATE) avant de réinsérer l'intégralité des données
Si c'est périodique, tu peux t'en sortir avec une sélection de dates
Si par contre c'est au différentiel, il va falloir construire la clause NOT EXISTS évoquée par mohamed de manière dynamique en scannant les champs constituant la PK

Autre point à traiter aussi, existe t-il des dépendances de données sur ton schéma d'archivage.
Si oui, il faut dans ce cas ordonner le traitement des tables afin d'insérer les données mères avant les données filles, mais aussi supprimer les données filles avant de supprimer les données mères (selon la stratégie d'archivage observée).

Bref, il faut se poser les bonnes questions avant de commencer à coder pour prendre le meilleur chemin possible.

Donc pour ton archivage :
- Quelle est la stratégie choisie (Annule et remplace, différentiel...)
- Connais-tu bien le SQL dynamique pour te lancer dans une procédure qui peut s'avérer vite complexe
- Existe t-il des contraintes (PK / FK / Autres) sur ton schéma d'archivage

Autre possibilité, l'utilisation d'import/export de données

[Mohamed.Houri]

bonjour Mr mohamed


je t'explique ma tache
j'ai 2 bases oracle 10 g de même structure une de production contient les données et l'autre pour l'archivage

j'ai besoin d'une procédure stockée paramétrée qui permet de faire l'archivage ( transférer les données de bd production vers bd archive) cette procédure sera lancer par un job/batch

cordialement

J'ai eu un peu de temps pour travailler sur votre proposition sachant que, me concernant, je ne l'envisagerai qu'en dernier recours. Ce n'est vraiment pas ce que je préfère. Mais cela valait la peine d'être essayé.

J’ai créé une procédure stockée(p_archive_dynamically) qui va se baser sur l’ensemble des tables appartenant à un utilisateur passé en paramètre

Code :

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

1
2
3
4
 
select  tab.table_name 
        from all_tables tab
where tab.owner = piv_owner;

et qui va générer dynamiquement une instruction SQL du genre

Code :

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

1
2
3
4
5
6
7
8
9
 
insert /*+ append */ into table1 (col1,col2,…)
select col1,col2,…
from dist_table1 tab2
where not exists (select null from
                              table1 tab1
where tab1.pk = tab2.pk)
--log errors into err$_table1 reject limit unlimited
;

Ici aussi, j’ai supposé que les tables contenant les données ont le même nom que les tables à archiver avec un préfixe ‘dist’. Naturellement, vous pouvez adapter le script à votre convenance en ce qui concerne les tables cibles à archiver.

J’ai également mis en commentaire la partie DML Error logging en attendant de trouver comment lier dynamiquement une table à sa table Error logging

Afin de faire dérouler l’exemple, j’ai crée deux tables

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
 
mohamed@mhouri> desc t_real_time
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TRADE_ID                                  NOT NULL NUMBER
 TRADE_DATE                                         DATE
 TRADE_COMMENT                                      VARCHAR2(500)
 
mohamed@mhouri> desc dist_t_real_time
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TRADE_ID                                  NOT NULL NUMBER
 TRADE_DATE                                         DATE
 TRADE_COMMENT                                      VARCHAR2(500)
 
mohamed@mhouri> select count(1) from t_real_time;
 
  COUNT(1)                                                                      
----------                                                                      
         0                                                                      
 
mohamed@mhouri> select count(1) from dist_t_real_time;
 
  COUNT(1)                                                                      
----------                                                                      
         3

Et j’ai limité dans ma procédure les tables à archiver uniquement à la table t_real_time comme suit:

Code :

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

1
2
3
4
5
 
  select tab.table_name, 
        from all_tables tab
		where tab.owner = piv_owner
    and   tab.table_name = 'T_REAL_TIME';

Voilà ce que cela donne:

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
 
mohamed@mhouri> @p_archive_dynamically
 
Procedure created.
 
mohamed@mhouri> exec p_archive_dynamically(piv_owner => user);
 
PL/SQL procedure successfully completed.
 
mohamed@mhouri> select * from t_sql_statement;
 
SQL_TEXT                                                                        
--------------------------------------------------------------------------------
insert /*+ append */ into T_REAL_TIME (TRADE_ID,TRADE_DATE,TRADE_COMMENT)       
select tab2.TRADE_ID,tab2.TRADE_DATE,tab2.TRADE_COMMENT            
from DIST_T_REAL_TIME tab2                                         
where not exists (select null                                      
   		        from T_REAL_TIME tab1                                        
  		        where tab1.TRADE_ID=tab2.TRADE_ID                            
                          )                                   
			 --log errors into ERR$_T_REAL_TIME reject limit unlimited       
 
 
 
mohamed@mhouri> select count(1) from t_real_time;
 
  COUNT(1)                                                                      
----------                                                                      
         3

J'ajoute un enregistrement dans la table dist_real_time et je re-archive

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
 
mohamed@mhouri> insert into dist_t_real_time values (4, sysdate-4, 'added after archive');
 
1 row created.
 
mohamed@mhouri> commit;
 
Commit complete.
 
mohamed@mhouri> select count(1) from  t_real_time;
 
  COUNT(1)                                                                      
----------                                                                      
         3       
 
mohamed@mhouri> exec p_archive_dynamically(piv_owner => user);
 
PL/SQL procedure successfully completed.
 
mohamed@mhouri> select count(1) from t_real_time;
 
  COUNT(1)                                                                      
----------                                                                      
         4

Le code de la procédure est attaché ici dans cette réponse

[achraf87]

merci execution et compiulation effectuer mais quand je fait SELECT * FROM t_sql_statement; la table est vide

[Scriuiw]

mais que disent les traces ?
Les instructions exécutées impactent-elles les lignes de données ?
As-tu tracé les SQL%ROUCOUNT pour savoir si des données étaient bien insérées ?
N'est-ce pas un problème de COMMIT oublié ?

[Marc Musette]

Pourquoi réinventez la roue ?

Oracle offre pas mal d'option pour dupliquer une base de données (notammement les physical standby qui sont assez simple à mettre en place et surtout robuste), alors pourquoi vouloir à tout prix développer soi même quelque chose qui ne supportera pas la charge d'une application réelle , ni ne permettra de garantir l'intérgrité des données en cas de crash ...

[wafiwafi]

ni ne permettra de garantir l'intérgrité des données en cas de crash ...

Argumentation plus que pertinente !! A vraiment prendre en compte. Il ne s'agit pas de réaliser un programme qui marche mais surtout l'adapter tant que possible à la réalité auquel il sera confronté. Bien vu!

[Heaven93]

je ne comprends toujours pas le besoin.
"périodicité" sous-entend une photo à un instant T. Or, la notion d'archivage suggèrerait plus de stocker les données sur une durée glissante de 1, 2 ou 3 ans. mais on ne sait rien sur la structure des tables. si elles ont chacune un champ date, pourquoi ne pas créer des tables partitionnées by range et y insérer les données sur la base cible? si ce n'est pas le cas, on fait quoi des données si elles sont modifiées ou supprimées à J+1 ou plus de la marge alors qu'elles sont archivées et peut-être statiques dans la base cible? comment retrouver ses petits?
autre question, est-ce que la base source est purgée? à quelle fréquence?
qu'entendez-vous par archivage actif ou non?
j'ai l'intuition que le professeur essaie de vous orienter vers du partitionnement sur la base d'archivage, ce qui permettra de mettre en oeuvre une procédure de purge assez aisée avec un drop des partitions plus anciennes que 36 mois. avec une première clef de partitionnement technique, sur la date d'archivage, et une seconde ou sur le nom de la table, ou autre chose...

[achraf87]

Procédure PL/SQL terminée avec succès.

mais quand j’exécute

Code :

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

SELECT * FROM t_sql_statement;

:aucune ligne sélectionnée

[Scriuiw]

On ne pourra pas t'aider si tu ne t'aide pas toi-même !
Trace ce qu'il se passe dans ta procédure

Ajoute des dbms_output.put_line('Trace1'); à chaque étape du package, tracfe la valeur des variables, la valeur des query exécuter, le nombre de lignes impacter par les instructions (SQL%ROWCOUNT), etc ...

Suite à cela, on y verra tous un peu plus clair