NUMFILES=`ls -1 *.* | wc -l`
 if (( ${NUMFILES} == 0 )); then
   echo "WARNING: There are no files in process directory.\n" >> $LOG_FILE
   exit 1
 fi
#
 for filename in $(ls -1 *.*)
 do
  echo "Processing $filename \n" >> $LOG_FILE
  #
  echo "Executed PL/SQL package for $filename\n" >> $LOG_FILE
  SQL_LOG_FILE=${LOG_DIR}/${filename}_${LOGDATE}.log
  DB_RSLT=`sqlplus -s <<EOF >> $SQL_LOG_FILE >> $LOG_FILE
   $USERID
   set serveroutput on size 999999;
   declare
    v_result varchar2(200);
   begin
    $sql_error=0                                             <-- i tried to add this and there was an error
    bdms_app.pa_bdms_upload_load_rating.upload('$filename');
   exception
    when others then
     $sql_error=1;                                           <-- i tried to add this and there was an error
   end;
/ 
   set serveroutput off;
   exit 0
  EOF`
  wait
  #
  RC=$?
  #
  echo $DB_RSLT >> $LOG_FILE
  echo "$filename \n" >> $RATING_LOG
  #
  if (( $sql_error == 0 )); then                             <-- if the variable is equal to 0 proceed to moving the file
    echo "Copied $filename to $ARCHIVE_DIR\n" >> $LOG_FILE
    cp $filename $ARCHIVE_DIR
    echo "Delete $filename from $INPUT_DIR\n" >> $LOG_FILE
    rm -f $filename
  fi
  #
 done
 echo "End of Step 2\n" >> $LOG_FILE

******************************
Run date: 12/21/2011 09:57:36
     
Step 1 Check for files to process
files to process        2

End of Step 1

Step 2 Process the files

Processing asd.csv 

Executed PL/SQL package for asd.csv

    =0
    *
ERROR at line 4:
ORA-06550: line 4, column 5:
PLS-00103: Encountered the symbol "=" when expecting one of the following:
begin case declare exit for goto if loop mod null pragma
raise return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe