Aide procédure stockée

**achraf87** · 07/06/2012, 15h51

*** Bonjour, ***

J'ai la procédure suivante :

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
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
CREATE OR REPLACE PROCEDURE p_archive_test(piv_owner varchar2) 
IS
 
lv_stmt  varchar2(2000)  := 'insert /*+ append */ into TABLE1 (COLUMNS) 
                                                    select INS_COLUMNS
                                                    from TABLE2 tab2@db1
                                                    where not exists (select null
                                                                      from TABLE1 tab1
                                                                      where PKCOLUMNS
                                            )        
                            ';  
 
   cursor c_tab IS
      SELECT tab.table_name,'ERR$_'||tab.table_name ERR_TABLE       
        FROM all_tables tab
        WHERE tab.owner = piv_owner
  -- and   tab.table_name = 'action'
      --order by atb.TABLE_ORDER
      ;
 
-- For each table get the primary key columns      
   cursor c_pk(civ_table_name IN all_tables.TABLE_NAME%type) 
   IS
      SELECT acl.COLUMN_NAME
        FROM all_cons_columns acl
            ,all_constraints acn
      WHERE acn.OWNER = piv_owner
         AND acn.CONSTRAINT_TYPE = 'P'
         AND acn.TABLE_NAME = civ_table_name
         AND acl.OWNER = acn.OWNER
         AND acl.TABLE_NAME = acn.table_name
         AND acl.CONSTRAINT_NAME = acn.CONSTRAINT_NAME
      ORDER BY acl.POSITION;
 
-- For each table get the corresponding table columns names
 
 
   cursor c_ins_cols(civ_table_name IN all_tab_columns.TABLE_NAME%type) 
   IS
      SELECT 'tab2.'||atc.COLUMN_NAME column_name
        FROM all_tab_columns atc
       WHERE atc.OWNER = piv_owner
         AND atc.TABLE_NAME = civ_table_name
       ORDER BY atc.COLUMN_ID;
 
-- For each table get the columns names excluding the primary key columns
   cursor c_upd_cols(civ_table_name IN all_tab_columns.TABLE_NAME%type) 
   IS
      SELECT 'tab1.'||atc.COLUMN_NAME||'=tab2.'||atc.column_name column_name
        FROM all_tab_columns atc
       WHERE atc.OWNER = piv_owner
         AND atc.TABLE_NAME = civ_table_name
         AND NOT EXISTS (SELECT 1
                           FROM all_cons_columns acl
                               ,all_constraints acn
                          WHERE acl.OWNER = atc.owner
                            AND acl.TABLE_NAME = atc.TABLE_NAME
                            AND acl.column_name = atc.column_name
                            AND acn.OWNER = acl.OWNER
                            AND acn.TABLE_NAME = acl.TABLE_NAME
                            AND acn.constraint_type = 'P')
       ORDER BY atc.COLUMN_ID;
 
-- For each table get the columns names
   cursor c_cols(civ_table_name IN all_tab_columns.COLUMN_NAME%type) 
   IS
      SELECT 
      --'tab1.'||
        atc.COLUMN_NAME column_name
        FROM all_tab_columns atc
       WHERE atc.owner = piv_owner
         AND atc.TABLE_NAME = civ_table_name
       ORDER BY atc.COLUMN_ID;
 
 
   lv_cols     varchar2(4000);
   lv_pk_cols  varchar2(4000);
   lv_ins_cols varchar2(4000);
   lv_upd_cols varchar2(4000);
 
BEGIN
 
   FOR r_tab IN c_tab 
   loop
      lv_cols     := '';
      lv_pk_cols  := '';
      lv_ins_cols := '';
      lv_upd_cols := '';
 
      FOR r_pk IN c_pk(civ_table_name => r_tab.table_name) 
      loop
         lv_pk_cols := lv_pk_cols||'tab1.'||r_pk.column_name||'=tab2.'||r_pk.column_name||' and ';
      end loop r_pk_loop;
 
      lv_pk_cols := substr(str1 => lv_pk_cols
                          ,pos  => 1
                          ,len  => length(ch => lv_pk_cols) - 5);
 
      FOR r_ins_cols IN c_ins_cols(civ_table_name => r_tab.table_name) 
      loop
         lv_ins_cols := lv_ins_cols||r_ins_cols.column_name||',';
      end loop r_ins_cols_loop;
 
      lv_ins_cols := substr(str1 => lv_ins_cols
                           ,pos  => 1
                           ,len  => length(ch => lv_ins_cols) - 1);
 
      FOR r_upd_cols IN c_upd_cols(civ_table_name => r_tab.table_name) 
      loop
         lv_upd_cols := lv_upd_cols||r_upd_cols.column_name||',';
      end loop r_upd_cols_loop;
 
      lv_upd_cols := substr(str1 => lv_upd_cols
                           ,pos  => 1
                           ,len  => length(ch => lv_upd_cols) - 1);
 
      FOR r_cols IN c_cols(civ_table_name => r_tab.table_name) 
      loop
         lv_cols := lv_cols||r_cols.column_name||',';
      end loop r_cols_loop;
 
      lv_cols := substr(str1 => lv_cols
                       ,pos  => 1
                       ,len  => length(ch => lv_cols) - 1);
 
      lv_stmt := REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(lv_stmt
                                                                        ,'TABLE1'
                                                                        ,r_tab.table_name)
                                                                ,'TABLE2'
                                                                ,'DIST_'||r_tab.table_name)
                                                        ,'PKCOLUMNS'
                                                        ,lv_pk_cols)
                                                ,'UPD_COLUMNS'
                                                ,lv_upd_cols)
                                        ,'INS_COLUMNS'
                                        ,lv_ins_cols)
                                ,'COLUMNS'
                                ,LV_COLS)
                        ,'TABLE3' 
                        ,R_TAB.ERR_TABLE);    
    -- here It highy advisable to store the sql statement that will be submitted
    -- to the SQL engine before executing it dynamically
     INSERT INTO t_sql_statement VALUES (lv_stmt);
     execute immediate lv_stmt;     
 
    end loop ;
 
    commit;
exception
   when others then    
 
 
 
      rollback;
      raise;
end  p_archive_test;

Elle compile avec succès mais quand je fais select sur la table t_sql_statement, aucune ligne n'est retournée.

Pouvez-vous m'aider svp ?

**Scriuiw** · 07/06/2012, 16h08

quand je fait select sur la table t_sql_statement aucune ligne sélectionnées vous pouvez m'aider Svp

Bien sûr, donc tu veux qu'on te fasse un script pour remplir ta table ??

Si aucune ligne n'est sélectionnée c'est que :
- Soit ta table est vide
- Soit aucune ligne ne correspond à ta sélection

En aucun cas il ne s'agit d'une erreur.

**achraf87** · 07/06/2012, 17h19

voila les erreurs

[Exécution du PL/SQL : ALTER SESSION SET PLSQL_DEBUG=TRUE
Exécution du PL/SQL : CALL DBMS_DEBUG_JDWP.CONNECT_TCP( '192.168.1.4', '2157' )
Le débogueur a accepté une connexion provenant de la base de données sur le port 2157.
Point d'arrêt d'exception survenu à la ligne 156 de P_ARCHIVE_TEST.pls.
$Oracle.EXCEPTION_ORA_6502 :
ORA-06502: PL/SQL : erreur numérique ou erreur sur une valeur: tampon de chaîne de caractères trop petit
ORA-06512: à "COURIER.P_ARCHIVE_TEST", ligne 155
ORA-06512: à ligne 6
Exécution du PL/SQL : CALL DBMS_DEBUG_JDWP.DISCONNECT()
Processus fermé.
Déconnexion de la base de données courrier.
Débogueur déconnecté de la base de données.

**Scriuiw** · 07/06/2012, 17h29

L'erreur si situe là

Code :

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

1
2
ORA-06502: PL/SQL : erreur numérique ou erreur sur une valeur: tampon de chaîne de caractères trop petit
ORA-06512: à "COURIER.P_ARCHIVE_TEST", ligne 155

Vraisemblablement vous essayez de mettre une valeur qui excède la taille de la zone COURIER.P_ARCHIVE_TEST.

Votre package génère dynamiquement des requêtes donc il faut que vous traciez les différentes opérations qui s'exécutent dedans.

**achraf87** · 07/06/2012, 18h50

j'ai pas bien compris ta réponse

**Scriuiw** · 08/06/2012, 08h57

Trace la requête qui fait une insertion de données dans la table COURIER et tu t'apercevras qu'il y a une erreur.

Par exemple tu aurais :

Code :

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

1
2
3
4
INSERT INTO COURIER 
  (ID,  P_ARCHIVE_TEST)
VALUES
  (100, 1234567890);

Où P_ARCHIVE_TEST est un NUMBER(5) alors que tu essayes d'y insérer un nombre de longueur 10, ce qui n'est pas possible

**achraf87** · 08/06/2012, 09h34

bonjour
mais non courrier c'est le nom de la BD

**Scriuiw** · 08/06/2012, 10h48

Dans ce cas, c'est une zone de ta table P_ARCHIVE_TEST qui pose problème (J'avoue ne pas avoir lu ton code en détail).

La solution ne change pas de toute façon, il faut tracer la requête générée dynamiquement pour voir qu'est-ce qui ne va pas dedans

**McM** · 08/06/2012, 11h27

P_archive_test, c'est le nom de la procedure

, ligne 155 c'est la ligne du raise.

Le problème doit venir d'une des variables lv_ qui sont remplies avec des données par curseur.. du coup ça peut dépasser la taille de la variable.

Pour savoir où ça plante exactement, faut mettre des traces d'erreur.