Running SQLPlus and PL/SQL Commands From A Shell Script

1 08 2008

I got this from Tim Archer..

http://timarcher.com/?q=node/48

The need often arises to run Oracle SQL scripts or PL/SQL procedures from a shell script. For instance, in my environment we run a lot of jobs through cron. Part of the job may be to connect to the database and run a PL/SQL function or procedure. Below I will describe how to do this, and some of the various “extras” that I include in my shell scripts. All of the examples below have been tested on Oracle 10gR2 and RedHat AS 4.

The Basic Syntax

In its simplest format, you can call SQLPlus from a shell script. The basic format of a shell script doing this is:

#!/bin/sh
sqlplus system/manager@prod_db <<ENDOFSQL
select sysdate from dual;
exit;
ENDOFSQL

Try to create that shell script on your server and run it. Be sure to enter in the correct connect string. You’re screen output should show all of the output from the SQL Plus session that executes.

A More Advanced Example

Now I will show you a more advanced script that I run on my servers. It builds off the simple example above, except it does a few extra things:

  • Checks command line parameters for the appropriate number of parameters.
  • Exports the ORACLE_SID as passed in from the command line parameters.
  • Runs the oraenv script for the specified ORACLE_SID to setup all the Oracle specific environment parameters.
  • Checks the return code from SQL Plus to determine if the SQL script successfully ran and takes an appropriate course of action.

Create a file named test.sh and put the following code in it:

#!/bin/sh

# The name of this script
SCRIPT_NAME=test.sh

###################################################################
#
# Check for Invalid Command Line Arguments
# $1 = ORACLE_SID
#
###################################################################
if [ $# -lt 1 ]
then
  echo "Usage: $SCRIPT_NAME <ORACLE_SID>"
  echo "Example: $SCRIPT_NAME DWHSE PROD"
  exit
fi

###################################################################
#
# Setup Script Variables
#
###################################################################
#This is the oracle_sid of the instance that the oracle environment
#will be setup for.
export ORACLE_SID=$1

#
# Initially we need some oracle path in the PATH for the dbhome command
# to be found. We reset the PATH variable to the proper oracle_home/bin
# below
#
export ORACLE_HOME=/usr/local/banner/oracle/product/current
export PATH=$ORACLE_HOME/bin:/usr/local/bin:$PATH

export ORAENV_ASK=NO

. /usr/local/bin/oraenv

export ORAENV_ASK=YES

echo "Starting Create PIN for ORACLE_SID: $ORACLE_SID"

###################################################################
#
# Run our SQL Plus Commands
#
###################################################################
sqlplus system/manager <<ENDOFSQL
whenever sqlerror exit sql.sqlcode;
select sysdate from dual;
exit;
ENDOFSQL

ERRORCODE=$?

#Check the return code from SQL Plus
if [ $ERRORCODE != 0 ]
then
  echo "********************"
  echo "ERROR: The SQL Plus Command Failed. ErrorCode: $ERRORCODE"
else
  echo "********************"
  echo "SQL Plus Successfully Ran. ErrorCode: $ERRORCODE"
fi

The format to run this script will then be:

./test.sh ORACLE_SID

You’ll need to modify a few sections of the script to work in your environment as described below:

  • In the section labeled Check for Invalid Command Line Arguments you will want to change the script to take an ORACLE_SID appropriate for your environment. The SID’s that are valid in my environment are PROD and DWHSE.
  • Change the connect string from system/manager to be what is required for your environment. There is no need to specify the Oracle SID in this part of the connect string since the beginning of the script exports the appropriate SID.
  • Plug in your own SQL to run within SQL Plus. In the example I gave you it will always succeed since selecting sysdate from dual will always work.

Running PL/SQL Procedures And Passing In Unix Environment Variables

Within the section specifying the SQL to run you can even call PL/SQL functions and procedures. If you want to call a PL/SQL function or procedure, make sure you prefix the procedure call with the keyword exec. Furthermore, you can pass your Unix environment variables into your SQL Plus session. The following example creates the environment variable named TEST_VAR and then runs SQL Plus calling DBMS_OUTPUT to print out the value in the variable.

export TEST_VAR="HELLO WORLD"
sqlplus usfbanner/webster <<ENDOFSQL
whenever sqlerror exit sql.sqlcode;
set serveroutput on;
exec dbms_output.put_line('${TEST_VAR}');
exit;
ENDOFSQL

Running The Script Through Cron

Once you are able to get a script created that performs all of the appropriate actions that you need to do, you can automate running it through cron. This is the simple part.

  • As the user who will run the script (probably the oracle user), edit the crontab.
    crontab –e
  • Add a line to run your script at the time you desire. For example, I have the following entry in the crontab for my oracle user that will run a script to send emails to people who new user accounts have been generated for.
    30 13 * * * /usr/local/banner/scripts/send_new_account_email.shl PROD

    The script will run at 1:30pm every day connecting to the PROD database instance. Lastly, the output from running this script will be emailed to the oracle user for my review. The emailing of any output is standard cron functionality.

About these ads

Actions

Information

3 responses

5 08 2008
SQLPlus, SQL, Starting & Controlling Oracle Using Shell Scripts « James Rossiter

[...] control, Oracle, Solaris, sql, sqlplus, starting, startup I found a post by dbamac that describes how to run SQLPlus commands and SQL commands themselves from a shell script. I now [...]

5 08 2008
James Rossiter

Thanks a lot, this is really really handy, I hope you don’t mind that I’ve linked to you from my personal blog. It looks like you are further down the Oracle path than me but I look forward to catching up!

6 08 2008
macdba

Thanks for your comments James. Of course you may link my page to your blogs as you wish. Stay cool!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Follow

Get every new post delivered to your Inbox.

%d bloggers like this: