/@DWH1 @/app/prd/dwh/sql/fmtx/dwh_vinv_fmtx_srvc.sqlWhenever there is an error in the SQL script, it returns a value to the Perl that stops the job from running. The problem is, when the database is down, SQL*Plus does not return any execution error (because the script didn't even run), so the Perl script finishes running without any problem (which is wrong). I need to know how to handle connection problems on the SQL*Plus command. Thanks!
In your Perl script, you'll want to code a section that verifies that the database is up and running. When you run your SQL script in SQL*Plus, you are telling Perl to make a host call to the OS and run a command line utility, i.e., SQL*Plus. To check that the database is up and running, you can use a few methods.
The first method I would consider on Unix/Linux is to have Perl perform "ps -ef|grep ora_smon_sid|grep -v grep" where sid is your database SID. If a line is returned, then your database is running. If no line is returned, then the database is down. This method assumes that the Perl script is run on your database server.
Another method, which can be run on any database server, is to use Perl's DBI (database interface) to make a connection to the Oracle database and then run a simple query as follows:
SELECT sysdate FROM dual;
The Perl DBI will throw an error which you can catch if the database is not up and running. Your error handling can be to report the problem and abort the rest of the script.
This was first published in May 2006