Discussion :

[unix27]

Bonjour,
J'ai un traitement qui ressemble a ça:

...
myreq:='DELETE FROM '|| rec_table.table_name || ' WHERE '|| ref_cl || ' = ' || ref_cl;
EXECUTE IMMEDIATE(mareq);
...

avec le EXECUTE IMMEDIATE(mareq) :il fait la suppression+le commit..., ce que je souhaite c'est une instruction ou autre pour faire la suppression mais sans commit.
Une solution a ça?
Merci

[Pomalaix]

Dans quel outil travaillez-vous ?
C'est sans doute cet outil qui introduit un commit à cause d'un paramètre prévu pour ça.

EXECUTE IMMEDIATE en lui-même n'a pas pour effet de provoquer un COMMIT.

[dariyoosh]

Essayez de passer directement par SQL*Plus pour exécuter votre script PL/SQL.

[unix27]

Dans quel outil travaillez-vous ?
C'est sans doute cet outil qui introduit un commit à cause d'un paramètre prévu pour ça.

EXECUTE IMMEDIATE en lui-même n'a pas pour effet de provoquer un COMMIT.

je suis sur du oracle 11G (oracle développer) et l'histoire du commit
Je pense que le execute immediat fait un commit explicite(déja tester et je confirme cela...)
regarde aussi http://www.developpez.net/forums/d27...ediate-commit/

[Waldar]

Il faudrait songer à lire en entier le sujet que vous indiquez avant de tirer des conclusions hâtives.

[dariyoosh]

Je pense que le execute immediat fait un commit explicite(déja tester et je confirme cela...)
regarde aussi http://www.developpez.net/forums/d27...ediate-commit/

Je ne suis pas sûr si les personnes qui ont participé au discours sur le topic dont vous avez donné le lien ont fait un test pour arriver à cette conclusion qui n'est pas juste. Si je comprends bien en gros l'idée était de dire que les deux instructions suivantes sont équivalentes:

Code :

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

1
2
3
 
DELETE * FROM TABLE; commit;
execute immediate 'delete * from table';

Donc, d'après vous EXECUTE IMMEDIATE fait un COMMIT. On va voir si c'est vrai en créant un script de test (par exemple un fichier nommé myscript.sql qu'on lance à partir de SQL*Plus). Ici on va donner trois versions:

1. DELETE sans COMMIT qui permet de récupérer à nouveau les lignes supprimées en faisant un ROLLBACK vers un SAVING POINT
   
2. DELETE suivi par un COMMIT et à ce moment là notre ROLLBACK vers le point de sauvegarde ne marchera plus
   
3. Et finalement un DELETE exécuté à partir d'un bloc du code PL/SQL en utilisant EXECUTE IMMEDIATE pour voir qu'est-ce que ça donne par rapport aux deux premiers tests

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
 
DROP TABLE testtable;
CREATE TABLE testtable(nval NUMBER);
 
INSERT ALL
  INTO testtable(nval) VALUES(124)
  INTO testtable(nval) VALUES(125)
  INTO testtable(nval) VALUES(126)
  INTO testtable(nval) VALUES(127)
  INTO testtable(nval) VALUES(128)
SELECT * FROM DUAL;
SAVEPOINT AFTER_INSERT;
 
SELECT * FROM testtable;
 
DELETE FROM testtable;
 
ROLLBACK TO SAVEPOINT AFTER_INSERT;
 
SELECT * FROM testtable; -- C'est la fête et on arrive à récupérer les lignes.

et si on lance myscript.sql voici ce qu'on obtient comme résultat:

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
 
SQL> @myscript.sql;
 
 
Table dropped.
 
 
Table created.
 
 
5 rows created.
 
 
Savepoint created.
 
       124
       125
       126
       127
       128
 
 
5 rows deleted.
 
 
no rows selected
 
 
Rollback complete.
 
       124
       125
       126
       127
       128
 
SQL>

Normal, on avait créé un saving point, on n'avait pas fait un COMMIT, en conséquence il est normal de pouvoir récupérer les lignes supprimées en faisant un ROLLBACK vers notre SAVING POINT.


Maintenant on passe à la version 2, c'est à dire un DELETE suivi par un commit.

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
 
DROP TABLE testtable;
CREATE TABLE testtable(nval NUMBER);
 
INSERT ALL
  INTO testtable(nval) VALUES(124)
  INTO testtable(nval) VALUES(125)
  INTO testtable(nval) VALUES(126)
  INTO testtable(nval) VALUES(127)
  INTO testtable(nval) VALUES(128)
SELECT * FROM DUAL;
SAVEPOINT AFTER_INSERT;
 
SELECT nval AS FROM testtable;
 
DELETE FROM testtable;
COMMIT;
 
SELECT nval FROM testtable;
 
ROLLBACK TO SAVEPOINT AFTER_INSERT;
 
SELECT nval FROM testtable;

Et si on lance myscript.sql voici ce qu'on obtient :

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
 
SQL> @myscript.sql;
 
Table dropped.
 
 
Table created.
 
 
5 rows created.
 
 
Savepoint created.
 
       124
       125
       126
       127
       128
 
 
5 rows deleted.
 
 
Commit complete.
 
 
no rows selected
 
ROLLBACK TO SAVEPOINT AFTER_INSERT
*
ERROR at line 1:
ORA-01086: savepoint 'AFTER_INSERT' never established in this session or is invalid
 
 
 
no rows selected
 
SQL>

Et bien, cette fois ci notre ROLLBACK ne marche plus (ORA-01086) car on a fait un COMMIT juste après le DELETE. C'est normal.


Finalement, faisons un troisième test cette fois-ci avec EXECUTE IMMEDIATE

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
 
DROP TABLE testtable;
CREATE TABLE testtable(nval NUMBER);
 
INSERT ALL
  INTO testtable(nval) VALUES(124)
  INTO testtable(nval) VALUES(125)
  INTO testtable(nval) VALUES(126)
  INTO testtable(nval) VALUES(127)
  INTO testtable(nval) VALUES(128)
SELECT * FROM DUAL;
SAVEPOINT AFTER_INSERT;
 
 
BEGIN
  DBMS_OUTPUT.PUT_LINE('test avec EXECUTE IMMEDIATE : ');
  EXECUTE IMMEDIATE 'DELETE FROM testtable';
END;
/
 
ROLLBACK TO SAVEPOINT AFTER_INSERT;
SELECT nval FROM testtable;

Si on lance le script voici ce qu'on obtient:

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
 
SQL> @myscript.sql;
 
Table dropped.
 
 
Table created.
 
 
5 rows created.
 
 
Savepoint created.
 
test avec EXECUTE IMMEDIATE :
 
PL/SQL procedure successfully completed.
 
 
Rollback complete.
 
       124
       125
       126
       127
       128
 
SQL>

Et bien ceci prouve tout simplement que EXECUTE IMMEDIATE ne fait pas de commit.

Je pense que le execute immediat fait un commit explicite(déja tester et je confirme cela...)

Je ne sais pas qu'est-ce que vous avez fait comme test, mais attention certains outils (GUI je veux dire) font par défaut (c'est à dire on peut les paramétrer ou tout simplement c'est leur paramétrage par défaut) font un COMMIT après l'exécution du code. Si par hadard vous avez utilisé un de ces outils, il est donc possible que vous ayiez exécuté l'expression de EXECUTE IMMEDIATE puis c'est l'outil qui a fait le COMMIT juste après et cela vous a donné l'impression que EXECUTE IMMEDIATE fait par défaut un COMMIT.

Voilà donc les exemples ci-dessus montre que ce n'est pas le cas et EXECUTE IMMEDIATE ne fait pas un COMMIT par défaut. D'ailleurs sauf erreur de ma part, sur le site de la documentation en ligne d'oracle ce n'est pas écrit non plus et c'est bien la documentation oracle qui doit être prise comme une source crédible afin d'enlever les doutes sur les problèmes de ce genre.


Cordialement,

[mnitu]

Dans Oracle Developper vous pouvez paramétrer la « Validation automatique » depuis Outils/Préférences/Base des données/Avancé.

L’instruction execute immediate ne fait pas de commit par défaut ! Par contre si l’instruction exécutée par l’execute immediate fait partie du DDL, cette instruction provoque un commit. Ce comportement est souvent une source d’incompréhension pour les débutants.

[dariyoosh]

... Par contre si l’instruction exécutée par l’execute immediate fait partie du DDL, cette instruction provoque un commit. ....

Belle remarque

Effectivement, mon exemple ne prenait en compte que les DML. Pour les DDL c'est différent.

Et voici d'ailleurs un exemple

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
 
SQL> DROP TABLE testtable;
 
Table dropped.
 
SQL> 
SQL> 
SQL> SAVEPOINT AFTER_CREATE_TABLE;
 
Savepoint created.
 
SQL> 
SQL> BEGIN EXECUTE IMMEDIATE 'CREATE TABLE testtable(n NUMBER)'; END;
  2  /
 
PL/SQL procedure successfully completed.
 
SQL> SELECT * FROM testtable;
 
no rows selected
 
SQL> ROLLBACK TO AFTER_CREATE_TABLE;
ROLLBACK TO AFTER_CREATE_TABLE
*
ERROR at line 1:
ORA-01086: savepoint 'AFTER_CREATE_TABLE' never established in this session or
is invalid
 
 
SQL>

Le fait que ROLLBACK ne marche pas (ORA-01086) indique qu'ici EXECUTE IMMEDIATE a fait un commit.