May I suggest you consider using Advanced Queuing.=20 Nick -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Sami Seerangan Sent: 16 February 2005 01:47 To: oracle-l@xxxxxxxxxxxxx Subject: How to handle the exception when remote db is down Hi All, I am trying to do DML activity on both local and remore DB. If the remote db is down, I need to insert the values into temporary table on the local db so that later when the remore db becomes available I can push the records. This is what I did but don't know how to handle the exception when remote db is down. SQL> desc db_lnk_test Name Null? Type ----------------------------------------- -------- -----------------------= ----- C1 NUMBER C2 VARCHAR2(100) C3 DATE SQL> desc db_lnk_test_Q Name Null? Type ----------------------------------------- -------- -----------------------= ----- C1 NUMBER C2 VARCHAR2(100) C3 DATE=20 =20 =20 SQL> create database link testa connect to MY_USER identified by temp_123 using 'testa'; =20 Database link created. SQL> get p1 =20 1 create or replace procedure db_lnk_test_proc(i_p1 number) 2 as 3 begin 4 insert into db_lnk_test values(i_p1,'From a1 db',sysdate); 5 begin 6 insert into db_lnk_test@testa values(i_p1,'From a1 db',sysdate); 7 exception when others then 8 insert into db_lnk_test_Q values(i_p1,'From a1 db',sysdate); -- If the remote db is down insert into QUEUE table 9 end; 10 commit; 11* end; SQL>=20 SQL> exec db_lnk_test_proc(1); =20 PL/SQL procedure successfully completed. SQL> exec db_lnk_test_proc(5); BEGIN db_lnk_test_proc(5); END; =20 * ERROR at line 1: ORA-02067: transaction or savepoint rollback required ORA-06512: at "MY_USER.DB_LNK_TEST_PROC", line 8 ORA-02055: distributed update operation failed; rollback required ORA-02068: following severe error from TESTA ORA-03113: end-of-file on communication channel ORA-06512: at line 1 =20 Thanks Sami -- //www.freelists.org/webpage/oracle-l =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D This message is intended solely for the use of the individual or organisati= on to whom it is addressed. It may contain privileged or confidential info= rmation. If you have received this message in error, please notify the ori= ginator immediately. If you are not the intended recipient, you should not= use, copy, alter, or disclose the contents of this message. All informati= on or opinions expressed in this message and/or any attachments are those o= f the author and are not necessarily those of VarTecTelecom Europe Ltd or i= ts affiliates. VarTec Telecom Europe Ltd accepts no responsibility for loss= or damage arising from its use, including damage from virus.=20 =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D -- //www.freelists.org/webpage/oracle-l