execute immediate + DROP TABLE

Version imprimable

Bonjour,

Dans une procédure stockée, je crée un table temporaire avec execute immediate. Ca fonctionne :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 select trunc(dbms_random.value(1,10000000)) num into tblRef from dual; request := 'CREATE GLOBAL TEMPORARY TABLE NC_TEMP_DIAG_CODE_' || tblRef ||' ('; request := request || 'ncDiagCode VARCHAR2(20), '; request := request || 'ncMotif VARCHAR2(60), '; request := request || 'ncSousMotif VARCHAR2(60), '; request := request || 'ncActionType VARCHAR2(30), '; request := request || 'swDateCreated VARCHAR2(10), '; request := request || 'ncDiagCodeId NUMBER(15,5) '; request := request || ') ON COMMIT PRESERVE ROWS'; execute immediate request;
Ensuite j'effectue des insert et des select, ca fonctionne toujours.

Enfin, je la supprime:
Code:

1 2 3 request := 'DROP TABLE NC_TEMP_DIAG_CODE_' || tblRef ; execute immediate request;
Et là, ca ne fonctionne pas. La procédure stockée se plante.

J'ai même essayé de rajouter CASCADE CONSTRAINTS, ca ne marche toujours pas.

Je ne vois pas d'où viens le problème...je sèche...

Par avance merci de votre aide !

Greg

Citation:

Envoyé par greg75

Et là, ca ne fonctionne pas. La procédure stockée se plante.

Et le message d'erreur ???

Ce test simple fonctionne sans problème :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SQL> create global temporary table ttemp as select * from dual;
 
Table crÚÚe.
 
SQL> declare request varchar2(2000);
  2  begin
  3  request:='drop table ttemp';
  4  execute immediate request;
  5  end;
  6  /
 
ProcÚdure PL/SQL terminÚe avec succÞs.
 
SQL> select * from ttemp;
select * from ttemp
              *
ERREUR Ó la ligne 1 :
ORA-00942: Table ou vue inexistante

17/09/2007, 17h33
McM

Il y a un truc avec les tables temporaire pour le drop..
Je crois que les sessions doivent s'arrêter ou alors un rollback avant le drop.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE GLOBAL TEMPORARY TABLE ttemp 
ON COMMIT PRESERVE ROWS AS SELECT * FROM dual ;
 
 
DECLARE 
	request VARCHAR2(2000);
	v NUMBER;
 BEGIN
	SELECT COUNT(*) INTO v FROM ttemp;
-- COMMIT; ROLLBACK;
  	request:='drop table ttemp';
  	EXECUTE IMMEDIATE request;
END;
 
ORA-14452: attempt TO CREATE, ALTER OR DROP an INDEX ON TEMPORARY TABLE already IN USE
ORA-06512: AT line 7

Avec ou sans le Commit ou le rollback : C'est pareil.

17/09/2007, 17h38
McM

l'aide de TOAD :

Citation:

Action: All the sessions using the session-specific temporary table have to truncate table and all the transactions using transaction specific temporary table have to end their transactions.

Ca marche.. Faut truncater ;-)

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE GLOBAL TEMPORARY TABLE ttemp ON COMMIT PRESERVE ROWS AS SELECT * FROM dual ;
 
DECLARE 
	request VARCHAR2(2000);
	v NUMBER;
 BEGIN
	SELECT COUNT(*) INTO v FROM ttemp;
	request:='truncate table ttemp';
  	EXECUTE IMMEDIATE request;
  	request:='drop table ttemp';
  	EXECUTE IMMEDIATE request;
END;
 
PL/SQL procedure successfully completed

salut

Voici la démonstration de PlaineR pour supprimer une table temporaire

Code:

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
 
SQL> create global temporary table t1 (n number) on commit preserve rows;
Table created.
 
SQL> insert into t1 values (1);
1 row created.
 
SQL> drop table t1;
drop table t1
           *
ERROR at line 1:
ORA-14452: attempt to create, alter or drop an index on temporary table 
already in use
 
SQL> commit;
Commit complete.
 
SQL> drop table t1;
drop table t1
           *
ERROR at line 1:
ORA-14452: attempt to create, alter or drop an index on temporary table 
already in use
 
SQL> delete t1;
1 row deleted.
 
SQL> commit;
Commit complete.
 
SQL> drop table t1;
drop table t1
           *
ERROR at line 1:
ORA-14452: attempt to create, alter or drop an index on temporary table 
already in use
 
SQL> truncate table t1;
Table truncated.
 
SQL> drop table t1;
Table dropped.
 
 
NB : quel est l'intérêt de créer une table temporaire dans un traitement, puis 
de la supprimer ensuite ? Ne peux-tu pas la créer une fois pour toutes ?

18/09/2007, 11h47
greg75

Merci à tous. Ca marche !!!! :yaisse2:
18/09/2007, 16h29
LBO72

J'avais le même besoin il y a fort bien longtemps et j'étais obligé de créer
une table permanente !!!
Merci pour le truncate :P

LBO72.