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); declare PRAGMA AUTONOMOUS_TRANSACTION; begin insert into a values (2); commit; end; / rollback; 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!