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_TR ANSACTION
2.EXCEPTION_INI T
3.RESTRICT_REFE RENCES
4.SERIALLY_REUS ABLE

The AUTONOMOUS_TRAN SACTION 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
[code=oracle]
CREATE OR REPLACE PROCEDURE DEPTINS
(
DNO DEPT.DEPTNO%TYP E,
DN DEPT.DNAME%TYPE ,
LC DEPT.LOC%TYPE
)
AUTHID CURRENT_USER
IS
PRAGMA AUTONOMOUS_TRAN SACTION;
BEGIN
INSERT INTO DEPT VALUES(DNO,DN,L C);
DBMS_OUTPUT.PUT _LINE('ONE ROW INSERTED......! ');
COMMIT;
EXCEPTION
WHEN DUP_VAL_ON_INDE X THEN
DBMS_OUTPUT.PUT _LINE('DUPLICAT E VALUE......!');
END;
[/code]

The AUTONOMOUS_TRAN SACTION pragma instructs the plsql compiler to mark a routine as autonomous (independent).A n 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,comm it 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.
[code=oracle]
create or replace trigger mytrig
before insert on emp
declare
pragma autonomous_tran saction;
begin
if to_char(sysdate ,'d') in(1,7) then
insert into trace values(user,sys timestamp);
commit;
Raise_applicati on_error(-20004,'Cannot do manipulation today');
end if;
end;
[/code]

Also these can execute DDL statments,using native dynamic SQL.
[code=oracle]
create or replace trigger scotttrig
after logon on scott.schema
declare
pragma autonomous_tran saction;
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,sys date)';
commit;
Raise_applicati on_error(-20004,'Cannot do LOGIN today');
end if;
end;
[/code]

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(whi ch 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.

[code=oracle]
CREATE OR REPLACE PROCEDURE
update_salary (dept_in IN NUMBER)
IS
PRAGMA AUTONOMOUS_TRAN SACTION;

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;
[/code]
[code=oracle]
CREATE TRIGGER parts_trig
BEFORE INSERT ON parts FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRAN SACTION;
BEGIN
INSERT INTO parts_log VALUES(:new.pnu m, :new.pname);
COMMIT;
END;
[/code]


Also check Using PRAGMA - II