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 
