Using PRAGMA - I

[debasisdas]

PRAGMA:-Signifies that the statement is a pragma (compiler directive). Pragmas are processed at compile time, not at run time. They pass information to the compiler.
A pragma is an instruction to the Oracle compiler that tells it to do something. In this case you are telling Oracle to associate an error that you choose to a variable that you choose. This pragma must go in the declarative section of your block.
Types of PRAGMA
1.AUTONOMOUS_TRANSACTION
2.EXCEPTION_INIT
3.RESTRICT_REFERENCES
4.SERIALLY_REUSABLE

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.

Just check this sample code

CREATE OR REPLACE PROCEDURE DEPTINS
(
DNO DEPT.DEPTNO%TYPE,
DN DEPT.DNAME%TYPE,
LC DEPT.LOC%TYPE
)
AUTHID CURRENT_USER
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO DEPT VALUES(DNO,DN,LC);
DBMS_OUTPUT.PUT_LINE('ONE ROW INSERTED......!');
COMMIT;
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
DBMS_OUTPUT.PUT_LINE('DUPLICATE VALUE......!');
END;
 

The AUTONOMOUS_TRANSACTION pragma instructs the plsql compiler to mark a routine as autonomous (independent).An a-t is an independent transaction started by another transaction,the main transaction.It lets the user suspend the main transaction and all sql operations,commit OR rollback those operations,then resume the main transaction.

RESTRICTIONS:-
Pragma can't be used to mark all subprograms in a package as autonomous.Only individual routines can be marked autonomous.It can be coded any where in the declaration section of the sub program.
Once started, an autonomous transaction is fully independent.It shares no locks,resources or commit dependencies with the main transaction.

ADVANTAGES:-
Unlike regular triggers autonomous triggers can contain COMMIT and ROLLBACK.

Please go through this sample trigger code.

create or replace trigger mytrig
before insert on emp
declare
pragma autonomous_transaction;
begin
if to_char(sysdate,'d') in(1,7) then
insert into  trace values(user,systimestamp);
commit;
Raise_application_error(-20004,'Cannot do manipulation today');
end if;
end;
 

Also these can execute DDL statments,using native dynamic SQL.

create or replace trigger scotttrig
after logon on scott.schema
declare
pragma autonomous_transaction;
begin
if to_char(sysdate,'dy') in('sun','sat') then
--this is not supported in normal triggers without using PRAGMA.
execute immediate 'create table logtable(id varchar2(10),dt date)';
execute immediate 'insert into logtable values(user,sysdate)';
commit;
Raise_application_error(-20004,'Cannot do LOGIN today');
end if;
end;
 

LIMITATIONS:-
Changes made by a-t become visible to other transaction when the a-t commits.The changes also become visible to the main transaction when it resumes.
If a-t attempts to access a resource held by th main transaction(which can't resume until the a-t routine exits),a deallock can occur.In that case,Oracle raises an exception in the a-t,If the user tries to exit an a-t without COMMIT OR ROLLBACK ,ORACLE RAISES AN EXCEPTION,in both the cases the transaction is rolled back if the exception goes unhandled.

CREATE OR REPLACE PROCEDURE 
   update_salary (dept_in IN NUMBER)
IS
   PRAGMA AUTONOMOUS_TRANSACTION;
 
   CURSOR myemps IS
      SELECT empno FROM emp
       WHERE deptno = dept_in
         FOR UPDATE NOWAIT;
BEGIN
   FOR rec IN myemps
   LOOP
      UPDATE emp SET sal = sal * 2 
       WHERE empno = rec.empno;
   END LOOP;
   COMMIT;
END;
------------
BEGIN
   UPDATE emp SET sal = sal * 2;
   update_salary (10);
END;
 

CREATE TRIGGER parts_trig
BEFORE INSERT ON parts FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO parts_log VALUES(:new.pnum, :new.pname);
COMMIT;
END;
 

Also check Using PRAGMA - II