Shell script wrapper

Here is an easy shell script wrapper to easily run an SQL script.

#!/bin/ksh

# Commands
typeset -u Command
for Name in mkdir
do
       Command=$Name

       case $Name in
               grep) PathNames=/usr/xpg4/bin:/usr/bin:/bin ;;
               *)    PathNames=/usr/bin:/bin ;;
       esac

       while [[ -n $PathNames ]]
       do
               PathName=${PathNames%%:*}
               PathNames=${PathNames#*:}

               if [[ $PathName = $PathNames ]]
               then
                       PathNames=""
               fi

               if [[ -x $PathName/$Name ]]
               then
                       eval "$Command=$PathName/$Name"
                       break
               fi
       done

       if [[ -z $(eval print `print \\$${Command}`) ]]
       then
               print -u2 "Unable to find a $Name executable"
               exit 1
       fi
done

# Directories

BaseDir=/home/oracle
SQLDir=${BaseDir}/sql
LogDir=${BaseDir}/logs
ENVFileDir=/usr/local/bin

# Misc
ProgName=${0##*/}

# Files

SQLFile=${SQLDir}/${ProgName%%.sh}.sql

# Parameters

ORACLE_SID=$1

if [[ -z $ORACLE_SID ]]
then
        print -u2 "ERROR - Oracle SID must be set or passed in"
        exit 1
fi

# Get correct environment

. ${ENVFileDir}/${ORACLE_SID}_env

# Set the output directory

OutFile=$LogDir/${ProgName%%.sh}_$ORACLE_SID.out

# Run the SQL

if [[ ! -d $BaseDir ]]
then
        print -u2 "ERROR - Unable to find directory $BaseDir"
        exit 1
fi

if [[ ! -d $LogDir ]]
then
        $MKDIR $LogDir
        if [[ $? -ne 0 ]]
        then
                print -u2 "ERROR - Unable to create directory $LogDir"
                exit 1
        fi
fi

if [[ ! -f $SQLFile ]]
then
        print -u2 "ERROR - Unable to find file $SQLFile"
        exit 1
fi

cd $LogDir
if [[ $PWD != $LogDir ]]
then
        print  -u2 "ERROR - Unable to change directory to $LogDir"
        exit 1
fi

$ORACLE_HOME/bin/sqlplus / as sysdba @$SQLFile $ORACLE_SID > $OutFile 2>&1
if [[ $? -ne 0 ]]
then
        print -u2 "ERROR - SQLPLus errored - please investigate"
        exit 1
fi

exit 0

You name the script with the same name as the SQL script you wish to run.

For example if you have a script called say

purge_stats.sql

then name the script

purge_stats.sh

Here's an example of a simple SQL script

set trims on
whenever sqlerror exit failure rollback

spool purge_stats_&1..log

set timing on
exec dbms_stats.purge_stats(sysdate-14)

spool off

exit 

Some usage notes

1. Environment file for the database must be of the format

<oracle_sid>_env

Bind Variable Capture

When capturing bind variable it is easy to get the character and number data but other types are obscured in the anydata type.

Here's a script to help to unmask those values.

col data_value for a50

accept SQLID prompt 'Enter SQL ID : '

select position
     , case datatype
            when 180 then to_char(anydata.accesstimestamp(value_anydata),'DD-MON-YYYY HH24:MI:SS')
            when  12 then to_char(anydata.accessdate(value_anydata),'DD-MON-YYYY HH24:MI:SS')
            else value_string
       end data_value
from  v$sql_bind_capture
where sql_id = '&SQLID';