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!

Kommentare:

Randolf hat gesagt…

Hi Martin,

regarding autonomous transactions one should be aware of the potential "can of worms" it opens up.

Among other things your session might deadlock itself, and some your DDL might fail with "ORA-0054 resource busy" for example if you have an open transaction on the object being subject to DDL which would behave differently when not using an autonomous transaction.

Randolf

Martin Berger hat gesagt…

Randolf,
you are totally right. Without a proper architecture and testing, it can end in unwanted situations.
I'm still not sure if I prefer a implicit COMMIT or an ORA-00054.
Martin

Mohammed Taj hat gesagt…

Nice post