TNSPING from PL/SQL?

From: J.A. Cooper <julius.cooper_at_gmail.com>
Date: 21 Dec 2005 09:32:24 -0800
Message-ID: <1135186344.044349.183130@g49g2000cwa.googlegroups.com>

Hello all,

I have a procedure that reads through the user_db_links table, and writes to a temp table a connect string, along with some scripts I want to run against the database:

**********************BEGIN PL/SQL CODE**********************
create table temptbl (
command              varchar2(1000));

declare
newline char(1) := chr(10); /* Newline command */ string varchar2(1000);
cursor each_c is

               select 'conn hr/hr@' || db_link
                  from user_db_links;

begin
open each_c;
loop

          fetch each_c into string;
          exit when each_c%NOTFOUND;
          string := string || newline || ' @c:\script1.sql;' ||
                                   newline || ' @c:\script2.sql;' ||
                                   newline || ' @c:\script3.sql;' ||
          insert into temptbl (command) values (string);

end loop;
end;

set pagesize 0; /* avoid the report header */ spool runtime.sql
select *
  from temptbl;
spool off;

@runtime

PAUSE Review the above and press <ENTER> to continue only if you received no errors.

***********************END PL/SQL CODE***********************

This will do exactly what I intend: connect to each site in my user_db_links table, and run the scripts. However, if a site is not available (due to downtime, etc)., then I get a "Could not connect" message, and the scripts return "Not connected" messages.

No harm, no foul, right?

Well, since we're distributing this script to several end users, and want to avoid getting a ton of calls saying "It told me not to push <ENTER> if there were errors--and I got LOTS of errors!", I thought it prudent to try to find a way to do a connection check BEFORE I write to temptbl, so that the script only tries to connect to the sites that are up AT THIS MOMENT.

My initial thought was to attempt to run the initial value of string ('conn hr_at_site') before I was reminded that you cannot initiate a new connection via PL/SQL. Then, I figured I'd try to do 'host tnsping site', but it seems that you cannot use HOST inside of PL/SQL, either.

So...my question is: how can I determine, quickly and cheaply (and hopefully, this afternoon), whether my remote databases are available--without resort to java/PRO*C/etc?

Regards,

Julius Cooper Received on Wed Dec 21 2005 - 11:32:24 CST