Mittwoch, 13. Juli 2011

avoid implicit COMMIT at DDL

Some times, I'd like to have a transaction open, even there is a DDL somewhere in between.
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!
Kommentar veröffentlichen