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!

ORA-06519 while doing transactions

731360Oct 31 2009 — edited Oct 31 2009
Hi,
I have created a trigger on my transaction table. When a service is booked it is being inserted into two seperate tables. It is working fine, but at some times I am getting following error while recording the transactions.

ORA-06519 Active autonomous transactions detected and rolled back.

Following is the trigger code:

create or replace trigger glstrip_audit
after INSERT or update ON jeevadb.ipservice
for each row
declare
pragma autonomous_transaction;
nurstcode pbswardmast.nurstcode%type;
roomno pbsipregist.currroomno%type;
BEGIN

select b.nurstcode,a.currroomno into nurstcode,roomno from pbsipregist a,pbswardmast b where a.currroomno=b.wardno and a.regno=:new.ipno;

if :new.servcode='006013' then

if inserting then
insert into phindenthd(indentno,indentdate,indenttime,regno,storecd,nurstcode,doctcode,userid,edituser,editdate,status)
values (:new.servslno,:new.servdate,:new.servtime,:new.ipno,'94',nurstcode,:new.orderedby,:new.createdby,:new.editedby,:new.editedon,'P');
insert into phindentdt(indentno,itecode,indentqty)
values (:new.servslno,'OPTGS01',:new.servqty);

elsif updating then

update phindenthd set indentno=:old.servslno,indentdate=:old.servdate,indenttime=:old.servtime,regno=:old.ipno,
storecd='94',nurstcode=nurstcode,doctcode=:old.orderedby,userid=:old.createdby,edituser=:new.editedby,editdate=:new.editedon,status='P'
where regno=:old.ipno and indentno=:old.servslno;
update phindentdt set indentno=:old.servslno,itecode='OPTGS01',indentqty=:new.servqty where indentno=:old.servslno;

end if;
end if;
end;

can you help me in finding the problem?

thanks
satya
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 28 2009
Added on Oct 31 2009
4 comments
3,387 views