Ask the Expert

Perl script calling SQL scripts with SQL*Plus

I have a Perl script that calls several SQL scripts by using SQL*Plus:
/@DWH1 @/app/prd/dwh/sql/fmtx/dwh_vinv_fmtx_srvc.sql
Whenever 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!

    Requires Free Membership to View

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

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: