Mega Code Archive
Procedure with pragma autonomous_transaction
<![CDATA[
SQL>
SQL> create global temporary table temp
2 ( x int )
3 on commit delete rows
4 /
Table created.
SQL>
SQL> create or replace procedure auto_proc1
2 as
3 pragma autonomous_transaction;
4 begin
5 insert into temp values ( 1 );
6 commit;
7 end;
8 /
Procedure created.
SQL> create or replace procedure auto_proc2
2 as
3 pragma autonomous_transaction;
4 begin
5 for x in ( select * from temp )
6 loop
7 null;
8 end loop;
9 commit;
10 end;
11 /
Procedure created.
SQL> insert into temp values ( 2 );
1 row created.
SQL>
SQL> exec auto_proc1;
BEGIN auto_proc1; END;
*
ERROR at line 1:
ORA-14450: attempt to access a transactional temp table already in use
ORA-06512: at "RNTSOFT.AUTO_PROC1", line 5
ORA-06512: at line 1
SQL> exec auto_proc2;
BEGIN auto_proc2; END;
*
ERROR at line 1:
ORA-14450: attempt to access a transactional temp table already in use
ORA-06512: at "RNTSOFT.AUTO_PROC2", line 5
ORA-06512: at line 1
SQL>
SQL> drop table temp;
Table dropped.
]]>