How to handle the exception when remote db is down 2005-02-16 - By Nick Tilbury @ Northampton
May I suggest you consider using Advanced Queuing.=20
Nick
-- --Original Message-- --
From: oracle-l-bounce@(protected)
[mailto:oracle-l-bounce@(protected)]On Behalf Of Sami Seerangan
Sent: 16 February 2005 01:47
To: oracle-l@(protected)
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@(protected) 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 (See ORA-02067.ora-code.com): transaction or savepoint rollback required
ORA-06512 (See ORA-06512.ora-code.com): at "MY_USER.DB_LNK_TEST_PROC", line 8
ORA-02055 (See ORA-02055.ora-code.com): distributed update operation failed; rollback required
ORA-02068 (See ORA-02068.ora-code.com): following severe error from TESTA
ORA-03113 (See ORA-03113.ora-code.com): end-of-file on communication channel
ORA-06512 (See ORA-06512.ora-code.com): 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
|
|
