Q

Perl script calling SQL scripts with SQL*Plus

I have a Perl script that calls several SQL scripts by using SQL*Plus. Whenever there is an error in the SQL script, it returns a value to the Perl that stops the job from running. 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. I need to know how to handle connection problems on the SQL*Plus command.

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!

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

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close