The problem is that Oracle is still holding the update to the remote database open. Try the following:
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); Commit; <========== This makes sure your first insert works 5 begin 6 insert into db_lnk_test@(protected) values(i_p1,'From a1 db',sysdate); 7 exception when others then Rollback; <===== Rollback the remote transactions 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;
You could also look at using savepoints. Nick had a good idea also about looking at Advanced Queueing.
Tom
-- --Original Message-- -- From: Sami Seerangan [mailto:dba.orcl@(protected)] Sent: Tuesday, February 15, 2005 8:47 PM 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
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) 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>
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;
