Blocage procédure stockée

**yoyol** · 18/06/2012, 10h47

bonjour

j'ai créé une procédure stockée qui permet de transférer les données d'une base de données à une autre en utilisant un lien entre les 2 bases de données
j'ai des problèmes de blocage de récupération de données : " ma procédure commence à lire les données de ma 1 ère table dans la base de données mais ça bloque vers la fin de procédure)
SVP si quelqu'un a une idée !!!!!!!
voila le script de ma procédure

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(archv varchar2) 
is
 
lv_stmt  varchar2(8000)  := 'insert /*+ append */ into TABLE1 (select * from TABLE2@db1 tab2 )
                            where not exists (select null
																	  from TABLE1 tab1
																	  where tab1.pk = tab2.pk
                                            )		
                            ';  
--(COLUMNS) INS_COLUMNS  PKCOLUMNS
   cursor c_tab is
      select tab.table_name,'ERR$_'||tab.table_name ERR_TABLE       
        from all_tables tab
		where tab.owner = archv
 --and   tab.table_name = 'action'
      --order by atb.TABLE_ORDER
	  ;
 
--  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 = archv
         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;
 
-- 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 = archv
         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 = archv
         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 = archv
         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;

voila les erreur de débogage :

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
Nom	Valeur	Type
----	-------	-----
- _throw		EXCEPTION_ORA_933
  ARCHV       	'ARCH'	VARCHAR2
  LV_STMT	  'insert /*+ append */ into ACTION (select * from ACTION@db1   
                  tab2 )where not exists (select null from ACTION tab1where   
                  tab1.pk = tab2.pk                    )		
                            '	VARCHAR2(8000)
														 
																	  
                         
  LV_COLS:   
       'ACTION_CLE,ACTION_LIB,ACTION_DTCRE,ACTION_TRI,ACTION_ETAT,ACTION_MAJELT,ACTION_MAJW,ACTION_REP,ACTION_SUP,ACTION_ACTSUI,ACTION_DESTSUI,ACTION_CHRONO,ACTION_AFFSERVBAL,ACTION_AFFSERVUTIL,ACTION_NBJDTLIM,ACTION_TYPEDESTSUIV,ACTION_EXEMPLAIRE,ACTION_TYPE,ACTION_GI,ACTION_CONV_PDF,ACTION_AUTO'	VARCHAR2(4000)
  LV_PK_COLS :	'tab1.ACTION_CLE=tab2.ACTION_CLE'	VARCHAR2(4000)
  LV_INS_COLS :	'tab2.ACTION_CLE,tab2.ACTION_LIB,tab2.ACTION_DTCRE,tab2.ACTION_TRI,tab2.ACTION_ETAT,tab2.ACTION_MAJELT,tab2.ACTION_MAJW,tab2.ACTION_REP,tab2.ACTION_SUP,tab2.ACTION_ACTSUI,tab2.ACTION_DESTSUI,tab2.ACTION_CHRONO,tab2.ACTION_AFFSERVBAL,tab2.ACTION_AFFSERVUTIL,tab2.ACTION_NBJDTLIM,tab2.ACTION_TYPEDESTSUIV,tab2.ACTION_EXEMPLAIRE,tab2.ACTION_TYPE,tab2.ACTION_GI,tab2.ACTION_CONV_PDF,tab2.ACTION_AUTO'	VARCHAR2(4000)
  LV_UPD_COLS	'tab1.ACTION_LIB=tab2.ACTION_LIB,tab1.ACTION_DTCRE=tab2.ACTION_DTCRE,tab1.ACTION_TRI=tab2.ACTION_TRI,tab1.ACTION_ETAT=tab2.ACTION_ETAT,tab1.ACTION_MAJELT=tab2.ACTION_MAJELT,tab1.ACTION_MAJW=tab2.ACTION_MAJW,tab1.ACTION_REP=tab2.ACTION_REP,tab1.ACTION_SUP=tab2.ACTION_SUP,tab1.ACTION_ACTSUI=tab2.ACTION_ACTSUI,tab1.ACTION_DESTSUI=tab2.ACTION_DESTSUI,tab1.ACTION_CHRONO=tab2.ACTION_CHRONO,tab1.ACTION_AFFSERVBAL=tab2.ACTION_AFFSERVBAL,tab1.ACTION_AFFSERVUTIL=tab2.ACTION_AFFSERVUTIL,tab1.ACTION_NBJDTLIM=tab2.ACTION_NBJDTLIM,tab1.ACTION_TYPEDESTSUIV=tab2.ACTION_TYPEDESTSUIV,tab1.ACTION_EXEMPLAIRE=tab2.ACTION_EXEMPLAIRE,tab1.ACTION_TYPE=tab2.ACTION_TYPE,tab1.ACTION_GI=tab2.ACTION_GI,tab1.ACTION_CONV_PDF=tab2.ACTION_CONV_PDF,tab1.ACTION_AUTO=tab2.ACTION_AUTO'	VARCHAR2(4000)

**McM** · 18/06/2012, 17h23

C'est toujours le même problème avec execute immediate .. Pour debugger, il faut tracer la commande qui va être exécutée parce qu'on risque toujours de louper quelque chose, surtout sur du générique comme ça.

Et voici le message d'erreur indiqué par la première ligne du débogueur :

- _throw EXCEPTION_ORA_933

http://ora-933.ora-code.com/

Modifie le code pour insérer dans une table de trace en autonomous transaction la chaine qui va être exécutée.

**ARAGONIS** · 22/06/2012, 09h27

Apparemment, c'est bien le contenu de ta variable lv_stmt qui pose problème. Je suis par contre surpris du rendu que tu affiches dans ton débogage.
Je suppose que tu affiches le contenu de ta table t_sql_statement, mais la restitution est étonnante :
On a en effet collé 'tab1where'.
Ensuite apparait une notion de VARCHAR2(8000). Enfin, le contenu est toujours entre-côte.