0

In my PL/SQL script, I have requirement to test the connectivity of remote data. Normally, in OS shell, we use command tnsping. Is there any command in SQL or PL/SQL so that I can test connectivity.

Basically, I am getting some data using database link. I want my script to execute immediately. If remote database is available then I want to run query using database link. If remote database is unavailable then I want to skip the getting of data using database link. So, this is the reason why I am looking for.

Kindly guide me for proper way. So, that my script does not hang in case of unavailability of remote database.

I am using Oracle 12.1C.

James Z
  • 12,209
  • 10
  • 24
  • 44
jeer
  • 29
  • 3
  • 2
    But if you can't connect, you can't run any commands. Are you talking about `sqlplus` or something else? And if `tnsping` does it, why don't you use it? – James Z Mar 05 '22 at 17:29
  • Normally, when we use database link in pl/sql code or sql query then it get data from remote database if remote database is available. But, if remote database is not available then sql query hangs ... I want to avoid hang. – jeer Mar 05 '22 at 17:42
  • And what is the method that you're running that SQL? Is it `sqlplus` or something else? – James Z Mar 05 '22 at 18:14
  • Oracle PL/SQL stored procedure ... and sql statement: ........ select column_name into v_variable from my_table@db_link – jeer Mar 05 '22 at 19:18
  • That's what you run **in** the database. But what is the **tool / programming language** you use to **connect to your database** – James Z Mar 05 '22 at 19:29
  • If you don't know even that, include the code (not just the SQL statement), but the full code you're running, and how you're running it. – James Z Mar 05 '22 at 19:30
  • @Jeer There might be a TNS setting for this. For example, find your tnsnames.ora file and add `(CONNECT_TIMEOUT=5)` should make the connection break quickly, instead of hanging forever. But I can't seem to get [CONNECT_TIMEOUT](https://docs.oracle.com/cd/E11882_01/network.112/e10835/tnsnames.htm#NETRF666) to work for me. It might depend on exactly what the connection is waiting for. – Jon Heller Mar 05 '22 at 22:33
  • I need something like dbms_tns.resolve_tnsname, that is available in 12.2C ... While I am using 12.1C – jeer Mar 07 '22 at 08:58
  • Yes, that's clear. Is there a reason why you're not telling what you run? – James Z Mar 07 '22 at 15:18
  • @jeer I don't think even DBMS_TNS will help you here. That package only gives you the connection string; it doesn't actually test the connection. – Jon Heller Mar 07 '22 at 20:36
  • thanks. then what can be the solution? – jeer Mar 22 '22 at 19:44

0 Answers0