|
SQL>
SQL> create table t ( msg varchar2(4000) );
Table created.
SQL>
SQL> create or replace procedure auto_proc
2 as
3 pragma autonomous_transaction;
4 begin
5 insert into t values ( 'A row for you' );
6 commit;
7 end;
8 /
Procedure created.
SQL>
SQL> create or replace
2 procedure proc( read_committed in boolean )
3 as
4 begin
5 if ( read_committed ) then
6 set transaction isolation level read committed;
7 else
8 set transaction isolation level serializable;
9 end if;
10
11 auto_proc;
12
13 for x in ( select * from t ) loop
14 dbms_output.put_line( x.msg );
15 end loop;
16 commit;
17 end;
18 /
Procedure created.
SQL>
SQL> exec proc( TRUE )
A row for you
PL/SQL procedure successfully completed.
SQL>
SQL> delete from t;
1 row deleted.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> exec proc( FALSE )
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table t;
Table dropped.
SQL>
|