I complete my last post : here, I show that we can cancel the implicit COMMIT of a DDL order; so maybe it is possible to cancel the ROLLBACK of a DML operation.
The objective of this article was to prove that the COMMIT takes place AFTER the DLL order and not in the code thereof, and therefore that it can be bypassed with a trigger. For this, I will test the CREATE TABLE order. My test is simple: if the table I created exists in DBA_OBJECTS and its status is VALID, then the DDL order has been executed in full. And then I cause an error to trigger this time an implicit ROLLBACK on the DDL order via the trigger attached to it. If the existing table no longer exists after this error in DBA_OBJECTS, then it means that the implicit COMMIT of the DDL command has been bypassed!
We create a trigger on the CREATE system event, with a scope on the whole database, so ON DATABASE. This trigger is AFTER, not BEFORE otherwise the table will not exist in DBA_OBJECTS at the start of my trigger. To cause an error, we insert an impossible value in DUAL (unless a little joker has modified the DUMMY field of DUAL but that is another story). Note that any CREATE will fail with this trigger, not just a CREATE TABLE or a CREATE SEQUENCE ... it is possible with an environment variable to test the type of object created but that's another story.
The trigger code with the hard name of the test table; I don't do error handling because I want the error on the INSERT not to be caught.
CREATE OR REPLACE TRIGGER trig_after_create_database AFTER CREATE ON DATABASE
DECLARED
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 ('Status of table ZZ01:' || V_V_STATUS);
INSERT INTO SYS.DUAL VALUES ('AA');
END;
/
Compile the OK trigger (the code is in the TRIG.sql file).
SQL> @TRIG
Trigger created.
We drop the test table.
SQL> drop table zz01 purge;
Table dropped.
Test creation of a table.
SQL> set serveroutput on;
SQL> create table ZZ01 (id number);
Status of table ZZ01: VALID - The table has been successfully created because it exists in DBA_OBJECTS with the status 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
We now check that the table ZZ01 no longer exists after the INSERT in DUAL which failed.
SQL> SELECT STATUS FROM SYS.DBA_OBJECTS WHERE OBJECT_NAME = 'ZZ01' AND OWNER = 'HR' AND OBJECT_TYPE = 'TABLE';
no rows selected
Bingo, the table has disappeared, which proves that the DDL COMMIT was prevented from definitively validating the CREATE TABLE. The explanation is that the trigger on the CREATE TABLE order having failed, Oracle will generate an implicit ROLLBACK which will include the operations of the trigger but also the CREATE TABLE because the trigger is associated with this DDL order and therefore it is the DDL / trigger set that is canceled by Oracle.
One could object to me that the implicit COMMIT of the DDL order is at the end of it in its code and not after and that the call to the AFTER trigger is made at the very end of the DDL order, just before the COMMIT. Yes, maybe, but in this case I think the SELECT in DBA_OBJECTS wouldn't have returned anything. I think the program sequence is as follows: trigger BEFORE then DDL order then trigger AFTER then COMMIT with four independent programs rather than a DDL order which contains the call to the BEFORE trigger then its own code then call to the trigger AFTER then COMMIT to the end of the DDL order.
October 14, 2020 - 4:00 am UTC
We don't "cancel" the final commit, we just never get there.
SQL> create or replace
2 trigger sys.kill_all_ddl
3 after create on database
4 begin
5 raise_application_error(-20000,'No create for you!');
6 end;
7 /
Trigger created.
then in another session
SQL> create table t ( x int );
create table t ( x int )
*
ERROR at line 1:
ORA-04088: error during execution of trigger 'SYS.KILL_ALL_DDL'
ORA-00604: error occurred at recursive SQL level 1
ORA-20000: No create for you!
ORA-06512: at line 2
and I traced that session
Before the 'create table' you'll see
XCTEND rlbk=0
which is "transaction end without rollback" ie, a commit
and then after the trigger fires you'll see
XCTEND rlbk=1
which is a rollback.