PRGMA Autonomous Transaction

Autonomous transactions allow you to leave the context of the calling transaction,
perform an independent transaction, and return to the calling transaction without affecting it's state.
The autonomous transaction has no link to the calling transaction,
so only committed data can be shared by both transactions.

The following types of PL/SQL blocks can be defined as autonomous transactions:

    * Stored procedures and functions.
    * Local procedures and functions defined in a PL/SQL declaration block.
    * Packaged procedures and functions.
    * Type methods.
    * Top-level anonymous blocks.

CREATE TABLE Emp(empno number(4),ename varchar2(25),sal number(25,4),comm number(25,4),deptno number);

insert into emp values(102,'rika',5000,'',20);
insert into emp values(103,'kan',4000,'',30);

select * from emp;

update emp set comm=150 where deptno=20;

Note:We are not commiting here.....

declare
pragma autonomous_transaction;
begin
insert into emp values(104,'hari',5000,'',40);
insert into emp values(105,'kiran',4000,'',40);
commit;
end;

select * from emp;

rollback

select * from emp;


Example 2::
-----------

CREATE TABLE error_logs (
  id             NUMBER(10)     NOT NULL,
  log_timestamp  TIMESTAMP      NOT NULL,
  error_message  VARCHAR2(4000),
  CONSTRAINT error_logs_pk PRIMARY KEY (id)
);

CREATE SEQUENCE error_logs_seq;


CREATE OR REPLACE PROCEDURE log_errors (p_error_message  IN  VARCHAR2) AS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO error_logs (id, log_timestamp, error_message)
  VALUES (error_logs_seq.NEXTVAL, SYSTIMESTAMP, p_error_message);
  COMMIT;
END;
/

BEGIN
  INSERT INTO at_test (id, description)
  VALUES (998, 'Description for 998');

  -- Force invalid insert.
  INSERT INTO at_test (id, description)
  VALUES (999, NULL);
EXCEPTION
  WHEN OTHERS THEN
    log_errors (p_error_message => SQLERRM);
    ROLLBACK;
END;
/

SELECT * FROM at_test WHERE id >= 998;

SELECT * FROM error_logs;

Ur's
AmarAlam