Anonymous PL/SQL blocks

Some of you may be familiar with the ability to administer Apex applications via SQL Developer, for instance I could modify the alias of my application thusly:

It opens a popup which allows me to write my new alias.
As with the other facilities of SQL Developer, you can opt to view the relevant SQL for this change.

All Oracle is really doing here is calling the relevant API, but what I also noticed the first time I used it was the autonomous transaction pragma it applied to the anonymous block.

declare
  PRAGMA AUTONOMOUS_TRANSACTION;
begin
  wwv_flow_api.set_security_group_id(p_security_group_id=>100001);
  wwv_flow_api.set_application_alias (p_flow_id=>101,p_alias=>'TIM_0101_b');
  commit;
end;
/

In the past I've quite happily applied this pragma for a procedure defined within a package, but this demonstrates we can also define independent transactions within these anonymous PL/SQL blocks, in addition to certain triggers.

Something I added to the memory bank when I first stumbled on it.

A final note - you can also kinda "name" your anonymous block to assist your documentation process, ie - there is no reason why you can't finish with:
END my_anonymous_block;

ScottWe

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);

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!