RE: How to handle the exception when remote db is down

From: Nick Tilbury _at_ Northampton <_at_>
Date: Wed, 16 Feb 2005 08:47:54 -0000
Message-ID: <6FF46F566A6CE14B92CD5F67A1DA8E4082C6F3@nthexch2k3.eu.vte.com>

May I suggest you consider using Advanced Queuing.=20

Nick

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Sami Seerangan Sent: 16 February 2005 01:47
To: oracle-l_at_freelists.org
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_at_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

--
http://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


--
http://www.freelists.org/webpage/oracle-l

Received on Wed Feb 16 2005 - 03:50:47 CST