Oracle et les ordres DDL : empêcher le COMMIT implicite du DDL avec un trigger - Prevent the Implicit COMMIT of DDL with trigger
Introduction
Sous Oracle tout ordre DDL a un COMMIT implicite; c'est une règle d'or qu'on vous a enseigné dès qu'on commence à travailler avec ce SGBD. Néanmoins ce COMMIT implicite peut-être évité même après que l'ordre DDL ait réussi; nous allons tester cela avec l'exemple du CREATE TABLE.
Points d'attention
N/A.
Base de tests
N'importe quelle base Oracle.
Exemples
============================================================================================
Le COMMIT implicite d'un ordre DDL : modes de fonctionnement
============================================================================================
Nous allons montrer ce que nous entendons par "COMMIT implicite" d'un ordre DDL (Data Definition Language).
Dans l'excellent livre de Thomas KYTE "Expert Oracle Database Architecture", Tom précise que ce que nous appelons COMMIT implicite est en réalité un véritable COMMIT et pas un type de COMMIT différent de celui exécuté sous SQL*Plus avec la commande COMMIT. En clair, la commande COMMIT est appelée dans le code de toute commande DDL ou juste après celle-ci (et c'est là que c'est subtil).
Nous utilisons une table ZZ01 qui est vide. Nous insérons dedans une valeur puis, sans COMMIT ni ROLLBACK, nous créons une nouvelle table avec l'ordre SQL CREATE TABLE, qui est un ordre DDL. Une fois la table créée, nous faisons un ROLLBACK : l'INSERT dans ZZ01 n'est pas annulé car le CREATE TABLE a généré un COMMIT implicite quand la table a été créée.
SQL> desc ZZ01;
Name Null? Type
-------- ----- -------
ID NUMBER
SQL> select * from zz01;
no rows selected
SQL> insert into ZZ01 values (1);
1 row created.
SQL> select * from zz01;
ID
----
1
SQL> CREATE TABLE ZZ02 AS SELECT * FROM CAT WHERE 1=2;
Table created.
SQL> rollback;
Rollback complete.
SQL> select * from zz01;
ID
----
1
En revanche, si l'ordre DDL échoue, alors le ROLLBACK que je déclenche fait après celui-ci fonctionne, ce qui prouve que le COMMIT implicite n'a lieu qu'après le CREATE TABLE ou tout à la fin de cette opération.
SQL> insert into ZZ01 values (3);
1 row created.
SQL> select * from zz01;
ID
----------
1
3
SQL> CREATE TABLE $ZZ02 (ID NUMBER); -- Erreur syntaxique
CREATE TABLE $ZZ02 (ID NUMBER)
*
ERROR at line 1:
ORA-00911: invalid character
SQL> rollback;
Rollback complete.
SQL> select * from zz01;
ID
----------
1
ATTENTION : il y a une exception, c'est si l'erreur de l'ordre DDL est une erreur sémantique et pas une erreur syntaxique. Voir ce post pour une explication de ce phénomène particulièrement vicieux : Un ordre DDL est entouré par deux commit implicites; le premier est conditionnel
SQL> insert into ZZ01 values (2);
1 row created.
SQL> select * from zz01;
ID
----------
1
2
SQL> CREATE TABLE ZZ02 AS SELECT * FROM CAT999 WHERE 1=2; -- Erreur sémantique donc le premier COMMIT est exécuté
CREATE TABLE ZZ02 AS SELECT * FROM CAT999 WHERE 1=2
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> rollback;
Rollback complete.
SQL> select * from zz01; -- Le ROLLBACK n'a pas annulé l'INSERT car le premier COMMIT du DDL a été exécuté puisque l'erreur est sémantique et pas syntaxique
ID
----------
1
2
============================================================================================
Annuler le COMMIT implicite d'un ordre DDL avec un trigger
============================================================================================
L'objectif de cet article est de prouver que le COMMIT a lieu APRES l'ordre DLL et pas dans le code de celui-ci, et donc qu'on peut le court-circuiter avec un trigger. Je vais pour cela tester l'ordre CREATE TABLE. Mon test est simple : si la table que j'ai créée existe dans DBA_OBJECTS et que son statut est VALID, alors l'ordre DDL a été exécuté en entier. Et ensuite je provoque une erreur pour déclencher cette fois un ROLLBACK implicite sur l'ordre DDL via le trigger qui lui est attaché. Si la table qui existait n'existe plus après cette erreur dans DBA_OBJECTS, alors ça signifie que le COMMIT implicite de l'ordre DDL a été court-circuité!
On crée un trigger sur l'évènement système CREATE, avec une portée sur toute la base, donc ON DATABASE. Ce trigger est AFTER, pas BEFORE sinon la table n'existera pas dans DBA_OBJECTS au début de mon trigger. Pour provoquer une erreur, on insère une valeur impossible dans DUAL (sauf si un petit plaisantin a modifié le champ DUMMY de DUAL mais ceci est une autre histoire). A noter que tout CREATE va échouer avec ce trigger, pas que un CREATE TABLE ou un CREATE SEQUENCE... il est possible avec une variable d'environnement de tester le type d'objet créé mais c'est une autre histoire.
Le code du trigger avec en dur le nom de la table de test; je ne fais pas de gestion d'erreur car je veux que l'erreur sur l'INSERT ne soit pas interceptée.
CREATE OR REPLACE TRIGGER trig_after_create_database AFTER CREATE ON DATABASE
DECLARE
V_V_STATUS SYS.DBA_OBJECTS.STATUS%TYPE;
BEGIN
SELECT STATUS INTO V_V_STATUS FROM SYS.DBA_OBJECTS WHERE OBJECT_NAME = 'ZZ01' AND OWNER = 'HR' AND OBJECT_TYPE = 'TABLE';
DBMS_OUTPUT.PUT_LINE('Statut de la table ZZ01 : ' || V_V_STATUS);
INSERT INTO SYS.DUAL VALUES ('AA');
END;
/
Compilation du trigger OK (le code est dans le fichier TRIG.sql).
SQL> @TRIG
Trigger created.
On droppe la table de test.
SQL> drop table zz01 purge;
Table dropped.
Test de création d'une table.
SQL> set serveroutput on;
SQL> create table ZZ01 (id number);
Statut de la table ZZ01 : VALID -- La table a bien été créée car elle existe dans DBA_OBJECTS avec le statut VALID
create table ZZ01 (id number)
*
ERROR at line 1:
ORA-04088: error during execution of trigger 'SYS.TRIG_AFTER_CREATE_DATABASE'
ORA-00604: error occurred at recursive SQL level 1
ORA-12899: value too large for column "SYS"."DUAL"."DUMMY" (actual: 2, maximum:
1)
ORA-06512: at line 10
On vérifie maintenant que la table ZZ01 n'existe plus après l'INSERT dans DUAL qui a échoué.
SQL> SELECT STATUS FROM SYS.DBA_OBJECTS WHERE OBJECT_NAME = 'ZZ01' AND OWNER = 'HR' AND OBJECT_TYPE = 'TABLE';
no rows selected
Bingo, la table a disparu, ce qui prouve qu'on a empêché le COMMIT du DDL de valider définitivement le CREATE TABLE. L'explication est que le trigger sur l'ordre CREATE TABLE ayant échoué, Oracle va généré un ROLLBACK implicite qui va englober les opérations du trigger mais aussi le CREATE TABLE car le trigger est associé à cet ordre DDL et donc c'est l'ensemble DDL/trigger qui est annulé par Oracle.
On pourra m'objecter que le COMMIT implicite de l'ordre DDL se trouve à la fin de celui-ci dans son code et non pas après et que l'appel au trigger AFTER se fait tout à la fin de l'ordre DDL, juste avant le COMMIT. Oui, peut-être, mais dans ce cas je pense que le SELECT dans DBA_OBJECTS n'aurait rien retourné. Je pense que la séquence des programmes est la suivante : trigger BEFORE puis ordre DDL puis trigger AFTER puis COMMIT avec quatre programmes indépendants plutôt que un ordre DDL qui renferme l'appel au trigger BEFORE puis son propre code puis appel au trigger AFTER puis COMMIT à la fin de l'ordre DDL.
Pour les applications pratiques de ce fonctionnement d'Oracle, je vous laisse cogiter :-)
/https%3A%2F%2Fassets.over-blog.com%2Ft%2Fcedistic%2Fcamera.png)
/https%3A%2F%2Fprofilepics.canalblog.com%2Fprofilepics%2F1%2F5%2F1555182.jpg)