Test Oracle connection

darrengan has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks,

i am running a perl script which query from a Oracle db. In event the db is down or password expired, my script will hang. To prevent this, i would like to have a checking on the db connection before my other scripts execute.

i have tried the following but it will hang:

$CHECK_DB = system ("sqlplus -s username/password\@dbname >> /dev/null
+ 2>&1");

if ( $CHECK_DB eq 0 )
 {
  print LOG "Msg: DB link up\n";
 }
else
 {
  print LOG "Msg: DB link down!\n";
 }
[download]

It doesnt seems to be working. Is there any other method?

Cheers Mate...

Comment on Test Oracle connection Download Code

Replies are listed 'Best First'.
Re: Test Oracle connection by holli (Abbot) on Apr 25, 2006 at 07:29 UTC
Use DBI and the DBD::Oracle driver. holli, /regexed monk/	[reply]
Re: Test Oracle connection by jcc (Sexton) on Apr 25, 2006 at 13:16 UTC
sqlplus is waiting for your input - I'd imagine your command is just hanging. Look at the `tnsping` utility. (You should have it if you have sqlplus). It should do what you want. Also, there's no reason to redirect output here. System will store the return code, that's all you need.	[reply] [d/l]
Re^2: Test Oracle connection by Anonymous Monk on Apr 25, 2006 at 20:01 UTC
tnsping tests only Oracle listener not database. one can create temporary file: set head off connect scott/tiger@mydb select 'mydb is available' from dual; exit run it with sqlplus -s /nolog @test.sql and analyze sqlplus output. but the best solution of course dbi	[reply]
Re: Test Oracle connection by cdarke (Prior) on Apr 25, 2006 at 11:42 UTC
I agree with holli, you would find it easier to use Perl. However, you say that sqlplus 'hangs', and it would be good to find out the reason. When you run sqlplus from the command line under these conditions, what happens? Do you have truss(1) or strace(1) available to see why it has stopped?	[reply]
Re^2: Test Oracle connection by jcc (Sexton) on Apr 25, 2006 at 17:23 UTC
sqlplus is an interactive command shell. It won't exit until something explicitly tells it to. Nothing in this example gives it that command.	[reply]
Re: Test Oracle connection by darrengan (Sexton) on Apr 26, 2006 at 01:52 UTC
Thanks for all the feedbacks. I guess my script hangs while waiting for input in sqlplus. I tried using DBI but unfortunately it is not installed. I can't install is as it is a production server. Cheers!	[reply]