Re: TNSPING from PL/SQL?

"J.A. Cooper" <julius.cooper_at_gmail.com> wrote in message news:1135186344.044349.183130_at_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
>

pl/sql Exception handler!

It doesnt matter how many times you test, eventually one of your users will be the first to see a down database. A database being down is more useful information than a live database!

You are lucky if your users use a system you give them then you can control it, collect that information and use it in either a email,sms or direct commuication method to your whole dba team.

As for your users, handle it - i.e. tell them the database is currently down. Received on Wed Dec 21 2005 - 13:35:37 CST