Exécuter une requête dynamique sans commit

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

Citation:

---

...
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

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.

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

Citation:

---

Envoyé par 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.

---

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/

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

Citation:

---

Envoyé par unix27

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:

---

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:

---

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:

---

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:

---

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:

---

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:

---

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:

---

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.

Citation:

---

Envoyé par unix27

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,

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.

Citation:

---

Envoyé par mnitu

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

---

Belle remarque :ccool:

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

Et voici d'ailleurs un exemple

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

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.

Citation:

---

Envoyé par dariyoosh

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

---

Vous êtes en train d'embrouiller inutilement un sujet très simple, avec une affirmation trompeuse. EXECUTE IMMEDIATE n'a pas fait de COMMIT, il est resté neutre à ce sujet.

Comme je l'ai dit depuis le début de cette discussion :

Citation:

---

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

---

Si la commande exécutée comporte un COMMIT implicite, EXECUTE IMMEDIATE respecte simplement ce COMMIT implicite. Ni plus, ni moins.

Citation:

---

Envoyé par Pomalaix

... Si la commande exécutée comporte un COMMIT implicite, EXECUTE IMMEDIATE respecte simplement ce COMMIT implicite. Ni plus, ni moins.

---

Ok, merci de m'avoir corrigé.

Idéalement, est-ce que vous auriez un lien vers le chapitre de la documentation offcielle d'oracle qui affirme votre énoncé?

Merci d'avance,

Citation:

---

Envoyé par dariyoosh

...Idéalement, est-ce que vous auriez un lien vers le chapitre de la documentation offcielle d'oracle qui affirme votre énoncé?...

---

Non, pas spécifiquement. Je m'appuyais plutôt sur des tests sur ce coup-là.

A priori, la doc mentionnera un effet particulier s'il y en a. Mais ça sera plus difficile d'y trouver l'absence d'un effet particulier !

@dariyoosh:oui je me suis trompé sur ça ;)... il fallait que je met un savepoint dans mon traitement ...
voila maintenant j'ai une autre chose(j'avoue que je suis débutant sur oracle et que ce que je suis entrain de faire est une petite évolution qui ne rentre pas dans mon périmétre de travail ... mais bon c'est une longue histoire...:D)
j'ai dans mon traitement ça:

Code:

---

1 2 3 4 5 6 7

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

---

et les tables contiennent des contraintes donc je souhaite faire des delete (ordre DML et non DDL)sans prendre en compte les contraintes d'intégrités
je sais bien que ce n'est pas bon... mais je fais le delete sur des tables temporaire donc ce n'est pas très grave(je ne souhaite pas entré dans la complexité des contraintes... car la suppression ce fait avec une requête dynamique)
en fin de compte je cherche quelque chose comme ceci:

Code:

---

DELETE FROM table WHERE ref_cl='1111' ON DELETE CASCADE

---

(supprimer sans se soucier des problème familiaux :lol: parent,fils...)
Est ce que cela est possible?:oops:

et merci a @mnitu et @Pomalaix ;)

Sinon... le sujet est résolu merci pour tout;)