Oracle might have some reasons for the implicit COMMIT, but I have mine to avoid them in my transactions.
Here a small example, how I do it (don't just copy it, it's really small, just an example)!
Let's prepare a package and two tables
CREATE OR REPLACE PACKAGE my_ddl_package AS
FUNCTION do_ddl(p_ddl varchar2) return varchar2;
END my_ddl_package;
/
CREATE OR REPLACE PACKAGE BODY my_ddl_package AS
function do_ddl(p_ddl varchar2) return varchar2
as
PRAGMA AUTONOMOUS_TRANSACTION;
begin
execute immediate p_ddl;
return NULL;
end;
END my_ddl_package;
/
create table t1 as select * from dual;
create table t2 as select * from dual;
And now the testcase
I try to update t1, drop t2 and afterwards rollback the update on t1.
Let's give it a try.
SQL> update t1 set dummy ='U';
1 row updated.
SQL> select my_ddl_package.do_ddl('drop table t2') from dual;
MY_DDL_PACKAGE.DO_DDL('DROPTABLET2')
--------------------------------------------------------------------------------
SQL> select * from t1;
D
-
U
SQL> rollback;
Rollback complete.
SQL> select * from t1;
D
-
X
SQL> select * from t2;
select * from t2
*
ERROR at line 1:
ORA-00942: table or view does not exist
Everything works as expected: update - DDL - rollback
Did I miss something?
If you did not want to read (or think) too much, just the shortcut:
What saved my day? It's PRAGMA AUTONOMOUS_TRANSACTION!
