Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Can "pragma autonomous transaction" called from exception block?

685758Aug 29 2011 — edited Aug 29 2011
CREATE OR REPLACE TRIGGER TRIG_EMP
AFTER UPDATE
ON EMP
FOR EACH ROW

DECLARE

CNT NUMBER := 0 ;

BEGIN

UPDATE EMP_bkp
SET COMM=999
WHERE ENAME ='SMITH';

COMMIT; -- it will generate error as commit cnt b use inside trigger so code wil go to exception blk.

exception
when others then
declare
PRAGMA AUTONOMOUS_TRANSACTION;
begin
commit;
end;
END;


why code is giving error?cant i put PRAGMA AUTONOMOUS_TRANSACTION in exception block although i have putted it into declare.. begin.. end block.
It might be unnecesary question but i m in delema why we cnt use pragma in exception block when any error ocur in code.as we can call any proc,func via exception block then why not we can call pragma?
This post has been answered by Alfonso Vicente on Aug 29 2011
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 26 2011
Added on Aug 29 2011
1 comment
139 views