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


SQL> create database link testa connect to MY_USER identified by
temp_123 using 'testa';

Database link created.


SQL> get p1
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));
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>

SQL> exec db_lnk_test_proc(1);

PL/SQL procedure successfully completed.


SQL> exec db_lnk_test_proc(5);
BEGIN db_lnk_test_proc(5); END;

*
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

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