 testing remote connection in Pl/Sql [message #127327] |
Mon, 11 July 2005 05:06  |
cagture
Messages: 4
Registered: July 2005
|
Junior Member |
|
|
Hi,
I am doing remote operations in Pl/Sql, but sometimes when there's is no connection to my another oracle instance I got '..connecion timeout..' I want to test the connection whether it exists or not. I mean something like ping. If at this time there's no connection I want to give a message to user.
|
|
|
|
| Re: testing remote connection in Pl/Sql [message #127432 is a reply to message #127327] |
Mon, 11 July 2005 12:27  |
mf_cx
Messages: 5
Registered: July 2005
|
Junior Member |
|
|
If you want to test if the DB link can created successfully you could try something like this. (This is rough and could be proceduralized easily...
begin
execute immediate 'create database link test connect to uname identified by pwd using ''DBname''';
begin
execute immediate 'select 1 from dual@test';
exception when LOGIN_DENIED then dbms_output.put_line(SQLERRM); -- or set a flag that it doesn't work...
end;
execute immediate 'drop database link test';
end;
Michael
|
|
|
|
| Re: testing remote connection in Pl/Sql [message #127575 is a reply to message #127432] |
Tue, 12 July 2005 05:40 |
cagture
Messages: 4
Registered: July 2005
|
Junior Member |
|
|
Hi Michael,
Thank you very much for your attention.
I have tried your suggestion but still problems.
I got the message
ERROR at line 1:
ORA-12535: TNS:operation timed out
ORA-06512: at "SYS.C_TESTC3", line 9
ORA-06512: at line 1
On the other hand I waits for al long time (indeed as in connection timeout parameter.)
What I want is to detect the network whether it is down or not, just like ping.
Thank You.
|
|
|
|
|
|
| Re: testing remote connection in Pl/Sql [message #127733 is a reply to message #127327] |
Wed, 13 July 2005 01:23 |
kgrimes
Messages: 7
Registered: July 2005
Location: Sydney Aus.
|
Junior Member |
|
|
If you have an oracle client installed, you can run from the command line
tnsping 'oracle address'
this should use your tnsnames.ora to determine the correct port for your oracle instance and try to connect.
if it can't connect you will get something like this;
Used parameter files:
TNS-03505: Failed to resolve name
if it can it should look more like this;
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = xxxx-oracle-node2)(PORT = 1542)) (ADDRESS = (PROTOCOL = TCP)(HOST = xxxx
-oracle-node1)(PORT = 1542))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME
= XXXXXXX)))
OK (660 msec)
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
