ORACLE BASED SHELL SCRIPT

Windows

To run a SQL script using SQL*Plus, place the SQL along with any SQL*Plus commands in a file and save it on your operating system. For example, save the following script in a file called "C:\emp.sql".

CONNECT scott/tiger
SPOOL C:\emp.lst
SET LINESIZE 100
SET PAGESIZE 50
SELECT *
FROM emp;
SPOOL OFF
EXIT;

Next, create a batch file called "C:\get_emp.bat" containing the following command.

sqlplus /nolog @C:\emp.sql

The resulting batch file can be run manually, by double-clicking on it, or scheduled using the Scheduled Tasks Wizard (Start > Programs > Accessories > System Tools > Scheduled Tasks) or the AT scheduler.
The method is very similar when using Recovery Manager (RMAN). As an example, place the following RMAN commands in a file called "C:\cmdfile.txt".

RUN {
  BACKUP DATABASE PLUS ARCHIVELOG;
}
EXIT;

Next create a batch file called "C:\backup.bat" containing the following command.

rman target=/ @cmdfile.txt

This command can include a catalog= entry if a recovery catalog is used. Once again, resulting batch file can be run manually or scheduled.

Powershell

Powershell allows file redirection similar to UNIX/Linux shell scripting, so we can do something like the following for SQL*Plus.

@"
  CONNECT scott/tiger
  SPOOL /u01/emp.lst
  SET LINESIZE 100
  SET PAGESIZE 50
  SELECT *
  FROM emp;
  SPOOL OFF
  EXIT;
"@ | sqlplus.exe /nolog

The following example works for RMAN.

@"
RUN {
  BACKUP DATABASE PLUS ARCHIVELOG;
}
EXIT;
"@ | rman target=/

UNIX and Linux (Method 1)

The previous methods works equally well in UNIX and Linux environments. For example, save the following script in a file called "/u01/emp.sql".

CONNECT scott/tiger
SPOOL /u01/emp.lst
SET LINESIZE 100
SET PAGESIZE 50
SELECT *
FROM emp;
SPOOL OFF
EXIT;

Next, create a shell script called "/u01/get_emp.ksh" containing the following lines.

#!/bin/ksh
sqlplus /nolog @/u01/emp.sql

The following command makes the file executable for the file owner.

chmod u+x /u01/get_emp.ksh

The resulting shell script can be run manually from the command line, or scheduled using CRON.
For RMAN, place the following RMAN commands in a file called "/u01/cmdfile.txt".

RUN {
  BACKUP DATABASE PLUS ARCHIVELOG;
}
EXIT;

Next create a batch file called "/u01/backup.ksh" containing the following lines.

#!/bin/ksh
rman target=/ @/u01/cmdfile.txt

This command can include a catalog= entry if a recovery catalog is used. Once again, resulting shell script must be made executable using the following command.

chmod u+x /u01/backup.ksh

The shell script is now ready to run.

UNIX and Linux (Method 2)

UNIX and Linux environments also allow the SQL*Plus and RMAN commands to be piped directly from the command line. For example, save the following commands in a file called "/u01/get_emp.ksh".

#!/bin/ksh
sqlplus /nolog << EOF
CONNECT scott/tiger
SPOOL /u01/emp.lst
SET LINESIZE 100
SET PAGESIZE 50
SELECT *
FROM emp;
SPOOL OFF
EXIT;
EOF

Notice the "<< EOF" and "EOF" tags, indicating the start and end of the command being piped into the SQL*Plus executable. The shell script is made executable using the following command.

chmod u+x /u01/get_emp.ksh

The shell script is ready to be run manually from the command line or scheduled using CRON.
The following example shows how RMAN can use the same method. Create a file called "/u01/backup.ksh" with the following contents.

#!/bin/ksh
rman target=/ << EOF
RUN {
  BACKUP DATABASE PLUS ARCHIVELOG;
}
EXIT;
EOF

Once again, the script can be made executable using the following command.

chmod u+x /u01/backup.ksh

The shell script is now ready to run.

UNIX and Linux (Returning values from SQL)

The following code show a script to pull the output of a query into a shell script variable.

#!/bin/bash
RETVAL=`sqlplus -silent scott/tiger <<EOF
SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF
SELECT * FROM emp;
EXIT;
EOF`
if [ -z "$RETVAL" ]; then
  echo "No rows returned from database"
  exit 0
else
  echo $RETVAL
fi

If you are returning a single value, this method works well. If you are returning multiple rows of multiple columns it gets a bit messy and forces you to parse the return value.

Checking the Database is Up

It is sometimes necessary to check the database is up before performing a task. In the following example, we check the database is up before starting a Tomcat application server. If the database is down, the script sleeps for 5 minutes, then checks again.
The script requires an Oracle Client to make connections to the database. This could be a full client installation, or an Oracle Instant Client installation.
Create a script called "check_db.sh" will the following contents. This is the script that will check if the database is up or not.

# Environment variables necessary for Oracle Instant Client
export LD_LIBRARY_PATH=/home/tomcat/scripts/instantclient_11_2
export PATH=$PATH:$LD_LIBRARY_PATH

function check_db {
  CONNECTION=$1
  RETVAL=`sqlplus -silent $CONNECTION <<EOF
SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF
SELECT 'Alive' FROM dual;
EXIT;
EOF`

  if [ "$RETVAL" = "Alive" ]; then
    DB_OK=0
  else
    DB_OK=1
  fi
}

Next, create a script called "tomcat_start_dev.sh" with the following contents.

scriptPath=${0%/*}
source $scriptPath/check_db.sh

CONNECTION="up_check_user/password@//hostname:1523/service"

echo "Wait until DB is up"
check_db $CONNECTION
while [ $DB_OK = 1 ]
do
  echo "DB not up yet. Sleeping for 5 mins (CTRL+C to exit)"
  sleep 300
  check_db $CONNECTION
done

echo "Starting"
echo "DEV: /u01/dev"
/u01/dev/bin/tomcat start

This sources the "check_db.sh" script, so it is included as if it were part of this script. It could be combined, but this allows the "check_db.sh" script to be shared by multiple scripts. The "tomcat_start_dev.sh" script loops round, checking to see if the DB is up. Once it is up, it falls through to the Tomcat start command.