Executing Oracle statements or Stored Procedures from KSH

1. To execute an Oracle stored procedure,
   
   

   #!/bin/ksh
   USER=scott
   PASS=tiger
   ORACLE_SID=example_sid
   JOB_LOG_FILE=example.log
   sqlplus -s $USER/$PASS"@"$ORACLE_SID >> $JOB_LOG_FILE << EOFMARK
   set serveroutput on
   set timing on
   exec sp_example('ABC');
   exit
   EOFMARK
   return 0
   

   The above script uses SQL Plus as client to connect to Oracle database, and execute the stored procedure "sp_example". All dbms_out.put_line() outputs will be logged into the "example.log" file.

2. To Run a query on an Oracle database, and output the results to a text file "test.txt",
   
   #!/bin/ksh

USER=scott
PASS=tiger
ORACLE_SID=example_sid
JOB_LOG_FILE=example.log
ORA_TMP_FILE=test.txt

sqlplus -s $USER/$PASS"@"$ORACLE_SID >> $JOB_LOG_FILE << EOFMARK
set serveroutput on
set timing on
set heading off
spool $ORA_TMP_FILE
select TO_CHAR(SYSDATE, 'dd-Mon-yyyy'), COUNT(*) from dual;
spool off
EOFMARK

return 0
   
   To check whether there is data selected,
   
   if [[ -e $ORA_TMP_FILE ]];then
  PARAM_INFO=`grep "no rows selected" $ORA_TMP_FILE | wc -l`
fi
if [[ $PARAM_INFO -eq 1 ]];then
  echo "No data is selected."
fi

   
   To get the data from the text file,
   
   if [[ -e $ORA_TMP_FILE ]];then
  PARAM_LIST=`cat $ORA_TMP_FILE`
fi
if [[ $PARAM_LIST != "" ]];then
  set -A array $PARAM_LIST
  CURRENT_DATE=${array[0]}
  RECORD_COUNT=${array[1]}
  echo "Current date is ${CURRENT_DATE}."
  echo "Record count is ${RECORD_COUNT}."
fi
   
   The output is:
   
   Current date is 17-Jan-2013.
Record count is 1.