• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 7486
  • Last Modified:

Check Remote DbLink

i want to update data on remote server. So, i do follwing

- create database link
- create trigger on a table
    insert, update and delete data

it is working fine when i insert, update and delete data on server a it changes same on server b.

now i want to check connection in trigger, if connection established then insert,update and delete on remote server otherwise insert in a log table.


help me how to check connection weather is established or not
0
pak_slm
Asked:
pak_slm
  • 5
  • 5
  • 4
  • +2
1 Solution
 
MohanKNairCommented:
Add the following code to the trigger. If the remote DB connection is available then value of db_connect will be 1 else 0.

db_connect := 1;
BEGIN
select 1 from dual@<dblink>;
EXCEPTION WHEN OTHERS THEN db_connect:=0;
END;

if db_connect=1
THEN
-------
process DATA
END IF;
0
 
MohanKNairCommented:
Some changes to the above code. The value from SQL has to be fetched into a variable


declare
n1 number(1);
db_connect number(1);
BEGIN
db_connect := 1;
BEGIN
select 1 INTO n1 from dual@<dblink>;
EXCEPTION WHEN OTHERS THEN db_connect:=0;
END;

......................

END;
/
0
 
pak_slmAuthor Commented:
i try with change but not work
check the following code

CREATE or REPLACE TRIGGER Agent_Ins_Upd_DEl_Trig
AFTER insert or update or delete ON TblAgent
FOR EACH ROW
declare
n1 number(1);
db_connect number(1);
BEGIN
db_connect := 1;
select 1 into n1 from dual@abclink;
EXCEPTION
WHEN OTHERS THEN db_connect := 0;

if db_connect = 1 then

      if UPDATING then
            update AgentTable set AgentName=:new.AgentName, DealerAbrv =:new.DealerAbrv where AgentCode=:old.AgentCode;
      end if;
      if INSERTING then
            insert into AgentTable values (:new.AgentCode,:new.AgentName,:new.DealerAbrv);
      end if;
      if DELETING then
            delete from AgentTable where AgentCode=:old.AgentCode;
      end if;
else
      insert into AgentTemp values (:new.AgentCode,:new.AgentName,:new.DealerAbrv);
end if;
END;
/
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
MohanKNairCommented:
The exception code has to be placed with a block

CREATE or REPLACE TRIGGER Agent_Ins_Upd_DEl_Trig
AFTER insert or update or delete ON TblAgent
FOR EACH ROW
declare
n1 number(1);
db_connect number(1);
BEGIN
db_connect := 1;

BEGIN
select 1 into n1 from dual@abclink;
EXCEPTION
WHEN OTHERS THEN db_connect := 0;
END;

if db_connect = 1
THEN
     if UPDATING then
          update AgentTable set AgentName=:new.AgentName, DealerAbrv =:new.DealerAbrv where AgentCode=:old.AgentCode;
     elsif INSERTING then
          insert into AgentTable values (:new.AgentCode,:new.AgentName,:new.DealerAbrv);
     elsif DELETING then
          delete from AgentTable where AgentCode=:old.AgentCode;
     end if;
ELSE
     insert into AgentTemp values (:new.AgentCode,:new.AgentName,:new.DealerAbrv);
END IF;
END;
/
0
 
pak_slmAuthor Commented:
it is ok when connection established. i have un pluge lan cable to test when no connection.
i have run insert query to test but it goes to long wait and not execute query nor else part.
0
 
pak_slmAuthor Commented:
it work but after long time wait, it should be quick

in upper code AgentTable is a view  created as
create or replace view AgentTable  as select * from tblagent@abclink
0
 
MohanKNairCommented:
Test whether the db link is working from sql*plus

SQL> insert into AgentTable values (11, null,null);

SQL> rollback;

0
 
pak_slmAuthor Commented:
i have check through a software and sql prompt also but same result;
0
 
MohanKNairCommented:
Also test the response when inserted from remote server

insert into tblagent values (11, null,null);
0
 
geotigerCommented:

You should check the v$dblink view to see if the db link can be established. If there is no record in the view after your initial select .. from dual@db, then you can not connect to the remote db.
0
 
ram_0218Commented:
I'm not sure if  v$dblink will help in this situation.

The direct answer would be:- There are no ways to find out if the db link is valid or not. If someone can say that theres a way then we got to clearly define what is the validity of the link?

What if the remote host is not responding or down momentarily. Theres no way of catching up it without trying to do some DMLs with the remote db unless it cant be.

So ultimately for such scenerio, one has to try:-

begin
 insert into table@remote_link(a,b,c) values (a,b,c);
exception when others then
 insert into log_table(a,b,c) values (a,b,c);
end;

0
 
actonwangCommented:
>>... but it goes to long wait and not execute query nor else part.
     for dblink, it will have a timeout period to try to connect to remote server. you can not avoid it.
0
 
actonwangCommented:
my suggestion is that instead of test dblink in each trigger, you test it upfront in the beginning of each session and register the dblink status in a temp table or a package. That way, you only need to check value in temp table or variable in the package to see if the dblink is valid currently.

that should give you a good solution.

Acton
0
 
ram_0218Commented:
Hhmmmm!! looks better than trying to check it everytime.

May be a static/persist variable in a package.
0
 
pak_slmAuthor Commented:
if user use an application and continue work 5 to 6 hours then if in begining connection ok and after some time it disconnect then in temp table it show connected.

i feel it is not suitable.

guide alternate solution.

0
 
ram_0218Commented:
then my approach should minimize the impact. give it a thought.
0
 
actonwangCommented:
you could periodically check to see if the db link is ok by registering the last time you checked the remote link into a temp table:

select time_column into last_time from temptable;

if sysdate > last_time + 30/1440 then
    -- do check again
end if;
0
 
ram_0218Commented:
Question to actonwang,

Now how do you decide the time that you use? The time is a very critical matter and you can never predict the time the connection would survive!
0
 
actonwangCommented:
good point.

You need to combine this with :

EXCEPTION
WHEN OTHERS THEN <flag db link is broken> and <register the time>

ram, you should be able to get my point ... :)
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 5
  • 5
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now