SQL & PL/SQL

Hi All,

Database : Oracle 10.1.0.2.0.

searched lot , But not able to fix, Kindly Share Ur Idea,

i have a procedure p1 in a client Database (*DB1*) which makes insert,update in DB1, inside my procedure im calling client employee procedure p2, which makes insert,update in DB2.

i think in DB2 a trigger fails under some condition. In p2 he uses the pragma autonomus transaction commit when exp occurs.

my procedure also have rollback when exp occurs. then why im getting this error when the trigger fails.

ORA-02067: transaction or savepoint rollback required, and database is not connecting through remote connections.
so that no one can login in to the application site.

so, im cornered now for this issue.

my procedure p1 :

create or replace procedure p1 (inp1) as

begin

savepoint mysavept;

insert on t1;

p2(inp2);

insert on t2;

commit;

exception when others

ROLLBACK TO mysavept;

P_ERROR:=SQLCODE||'-'||SQLERRM||'--->ERROR_BACKTRACE: ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;

update error_logtbl set ermsg=ERROR;

commit;

end p1;
/