autonomous_transaction konusuna güzel bir örnek
SQL> set serveroutput on;
SQL> drop table ersin_test_trans;
Table dropped
SQL> create table ersin_test_trans (cur_date date, text varchar(20));
Table created
SQL> create or replace procedure ersin_auto_trans is
2 pragma autonomous_transaction;
3 begin
4 insert into ersin_test_trans values(sysdate,’a/1′);
5 –commit;
6 end ersin_auto_trans;
7 /
Procedure created
SQL> create or replace procedure ersin_normal_trans is
2 begin
3 insert into ersin_test_trans values (sysdate,’n/1′);
4 ersin_auto_trans;
5 insert into ersin_test_trans values (sysdate,’123456789012345678901234′);
6 end ersin_normal_trans;
7 /
Procedure created
SQL> execute ersin_normal_trans;
begin ersin_normal_trans; end;
ORA-06519: active autonomous transaction detected and rolled back
ORA-06512: at “SYS.ERSIN_AUTO_TRANS”, line 6
ORA-06512: at “SYS.ERSIN_NORMAL_TRANS”, line 4
ORA-06512: at line 1
SQL> select * from ersin_test_trans;
CUR_DATE TEXT
———– ——————–
SQL> truncate table ersin_test_trans;
Table truncated
SQL> create or replace procedure ersin_auto_trans is
2 pragma autonomous_transaction;
3 begin
4 insert into ersin_test_trans values(sysdate,’a/1′);
5 commit;
6 end ersin_auto_trans;
7 /
Procedure created
SQL> execute ersin_normal_trans;
begin ersin_normal_trans; end;
ORA-12899: value too large for column “SYS”.”ERSIN_TEST_TRANS”.”TEXT” (actual: 24, maximum: 20)
ORA-06512: at “SYS.ERSIN_NORMAL_TRANS”, line 5
ORA-06512: at line 1
SQL> commit;
Commit complete
SQL> select * from ersin_test_trans;
CUR_DATE TEXT
———– ——————–
05/07/2007 a/1
SQL> truncate table ersin_test_trans;
Table truncated
SQL> create or replace procedure ersin_normal_trans is
2 begin
3 insert into ersin_test_trans values (sysdate,’n/1′);
4 ersin_auto_trans;
5 insert into ersin_test_trans values (sysdate,’n/2′);
6 end ersin_normal_trans;
7 /
Procedure created
SQL> execute ersin_normal_trans;
PL/SQL procedure successfully completed
SQL> commit;
Commit complete
SQL> select * from ersin_test_trans;
CUR_DATE TEXT
———– ——————–
05/07/2007 n/1
05/07/2007 a/1
05/07/2007 n/2
SQL>
