By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,652 Members | 1,727 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

Using PRAGMA - I

debasisdas
Expert 5K+
P: 8,127
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
Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE PROCEDURE DEPTINS
  2. (
  3. DNO DEPT.DEPTNO%TYPE,
  4. DN DEPT.DNAME%TYPE,
  5. LC DEPT.LOC%TYPE
  6. )
  7. AUTHID CURRENT_USER
  8. IS
  9. PRAGMA AUTONOMOUS_TRANSACTION;
  10. BEGIN
  11. INSERT INTO DEPT VALUES(DNO,DN,LC);
  12. DBMS_OUTPUT.PUT_LINE('ONE ROW INSERTED......!');
  13. COMMIT;
  14. EXCEPTION
  15. WHEN DUP_VAL_ON_INDEX THEN
  16. DBMS_OUTPUT.PUT_LINE('DUPLICATE VALUE......!');
  17. END;
  18.  
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.
Expand|Select|Wrap|Line Numbers
  1. create or replace trigger mytrig
  2. before insert on emp
  3. declare
  4. pragma autonomous_transaction;
  5. begin
  6. if to_char(sysdate,'d') in(1,7) then
  7. insert into  trace values(user,systimestamp);
  8. commit;
  9. Raise_application_error(-20004,'Cannot do manipulation today');
  10. end if;
  11. end;
  12.  
Also these can execute DDL statments,using native dynamic SQL.
Expand|Select|Wrap|Line Numbers
  1. create or replace trigger scotttrig
  2. after logon on scott.schema
  3. declare
  4. pragma autonomous_transaction;
  5. begin
  6. if to_char(sysdate,'dy') in('sun','sat') then
  7. --this is not supported in normal triggers without using PRAGMA.
  8. execute immediate 'create table logtable(id varchar2(10),dt date)';
  9. execute immediate 'insert into logtable values(user,sysdate)';
  10. commit;
  11. Raise_application_error(-20004,'Cannot do LOGIN today');
  12. end if;
  13. end;
  14.  
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.

Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE PROCEDURE 
  2.    update_salary (dept_in IN NUMBER)
  3. IS
  4.    PRAGMA AUTONOMOUS_TRANSACTION;
  5.  
  6.    CURSOR myemps IS
  7.       SELECT empno FROM emp
  8.        WHERE deptno = dept_in
  9.          FOR UPDATE NOWAIT;
  10. BEGIN
  11.    FOR rec IN myemps
  12.    LOOP
  13.       UPDATE emp SET sal = sal * 2 
  14.        WHERE empno = rec.empno;
  15.    END LOOP;
  16.    COMMIT;
  17. END;
  18. ------------
  19. BEGIN
  20.    UPDATE emp SET sal = sal * 2;
  21.    update_salary (10);
  22. END;
  23.  
Expand|Select|Wrap|Line Numbers
  1. CREATE TRIGGER parts_trig
  2. BEFORE INSERT ON parts FOR EACH ROW
  3. DECLARE
  4. PRAGMA AUTONOMOUS_TRANSACTION;
  5. BEGIN
  6. INSERT INTO parts_log VALUES(:new.pnum, :new.pname);
  7. COMMIT;
  8. END;
  9.  

Also check Using PRAGMA - II
Feb 25 '08 #1
Share this Article
Share on Google+