Wednesday, 14 October 2015

PL/SQL: Sample code for using PRAGMA AUTONOMOUS_TRANSACTION

In this post I just want to post a sample code (which I use) to write debug in PL/SQL using PRAGMA AUTONOMOUS_TRANSACTION.

The AUTONOMOUS_TRANSACTION pragma changes the way a subprogram works within a transaction. A subprogram marked with this pragma can do SQL operations and commit or roll back those operations, without committing or rolling back the data in the main transaction. Read more
 --create table  
 create table xxaj_debug (id number, msg varchar2(4000), date_stamp date);  
 --create sequence  
 create sequence xxaj_seq start with 1 increment by 1;  
 --create procedure  
 create or replace procedure xxaj_proc ( msg varchar2)  
 is  
      PRAGMA AUTONOMOUS_TRANSACTION;  
 begin  
      insert into xxaj_debug values (xxaj_seq.nextval, msg, sysdate);  
      commit;  
 end;  


Feel free to point out if anything is missing/wrong in this blog.

No comments:

Post a Comment