Thursday 29 October 2009

Unexpectedly learning something new...

Today I was exploring the new SQL Developer in regard to managing Application Express.

There is an option for example to change the alias for an application. As usual there is the facility to see the SQL so I thought why not have a look what was going on.

I was presented with a useful looking anonymous block I never really thought about before.

I thought I'd try it out:
create table a ( a number);

insert into a values (1);

  insert into a values (2);



select * from a;
Not long after I found another potential use, I was doing some work with triggers and I was trying to decide on the method to create a new record on the same table that fired a trigger, but avoid the mutating table issue - perhaps this could do the trick without the need for a procedure defined with the pragma? Alas:
PLS-00710: PRAGMA AUTONOMOUS_TRANSACTION cannot be declared here

How about that? I like learning new things, and I happened to find something about PL/SQL while researching SQL Developer managing Application Express!

1 comment:

Connor McDonald said...

Using autonomous txns to avoid mutating table errors has got disaster written all over it :-)