Linux script call SQL

callSQL.sh

#!/usr/bin/ksh
################################################################
# Modification History:
#
# Ver.            Date            By              Activity
# ----            -------         ------          --------
# 1.0             2012/06/18      TEST          Creation
#################################################################
SN=$(basename $0)

function logMsg {
        echo "$(date +'%Y/%m/%d %H:%M:%S') - $2"
        echo "$(date +'%Y/%m/%d %H:%M:%S') - $2" >> $1
}

if [ $# -lt 2 ]; then
  echo "syntax: $SN <connection> <sql file>"
  exit 9
fi

which sqlplus > /dev/null 2>&1
if [ $? -ne 0 ]; then
  echo "sqlplus not found"
  exit 1
fi

sqlFile=$2
logFile=$sqlFile.log

>$logFile
logMsg $logFile "$SN begins......"

sqlplus $1>>$logFile 2>&1 << EOF
        set echo on;
        set timing on;
        set serveroutput on;
        @$sqlFile;
EOF

#grep -v $SN $logFile

ret=$(grep -c "^ERROR" $logFile)

if [ $ret -ne 0 ]; then
        logMsg $logFile "$SN Failed, Please check [$logFile]"
else
        logMsg $logFile "$SN Finished, Please check [$logFile]"
fi
--------------------------------------------------------------------------------------------------------

 

test.sql

--------------------------------------------------------------------------------------------------------

 

-- Created on 6/18/2012 by

declare

-- Local variables here

 

i integer;

 

begin

 

-- Test statements here

 

update BATCH_TEST set name = 'JASON' where name = 'jason2';

commit;

 

end;

/

--------------------------------------------------------------------------------------------------------

 

callSQL.sh username/password@SID test.sql