Hi we are using version 11.2.0.4 of oracle. I am trying to use pragma automonous_transactions in my code. My question is if pragma autonomous transaction has to be a separate procedure? or it can be achieved in a plsql block too?
In below block i am expecting the lock taken should not be released by the DDL which happen inside the pragma autonomous block. It should persists even after the DDL. But its not working that way. When i put the same pragma autonomous transaction code in a separate procedure and give a call to that procedure then only it works. Why so? Is it that the pragma autonomous cant happen in same block?
create table user1.test1(c1 number, c2 number);
DECLARE
v_lckcnt NUMBER(15,0);
BEGIN
execute immediate 'LOCK TABLE user1.test1 In Exclusive Mode NOWAIT';
select count(*) into v_lckcnt
from v$locked_object where object_id in (select object_id from all_objects where object_name='TEST1');
dbms_output.put_line('lock count 1 :-'||v_lckcnt);
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
execute immediate 'create table USER2.test(c1 number, c2 number)';
commit;
END;
--USER2.p1();
select count(*) into v_lckcnt
from v$locked_object where object_id in (select object_id from all_objects where object_name='TEST1');
dbms_output.put_line('lock count 2 :-'||v_lckcnt);
END;
/
Procedure:-
create or replace procedure USER2.p1 authid current_user
is
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
execute immediate 'create table USER2.test(c1 number, c2 number)';
commit;
END;
/