#!/bin/csh -f
setenv SQLPLUS ${ORACLE_HOME}/bin

setenv ORACLE_SID oracle_sid
set exit_loop = 0

while ( $exit_loop == 0 )
   $SQLPLUS/sqlplus -s / << EOF
   variable exit_code number
   DECLARE
     ...
   BEGIN
      ...
   END;
/
exit :exit_code
EOF
set sql_return_code = $status
      # RETURN CODES
      # 0 End SUCCESS.
      # 1 Return Value 1
      # 2 Return Value 2
			
	if ($sql_return_code == 0) then		# 0	Success:	End success
		date
	  	echo 0: Return Code 0 message
	  	set exit_code = 0
	  	set exit_loop = 1
	endif
	if ($sql_return_code == 1) then 	
		date
	  	echo 1: Return code 1 message
	  	set exit_code = 1
	  	set exit_loop = 1
	endif
	if ($sql_return_code == 2) then 	
  		date
  		echo 2: Return code 2 message
	  	set exit_loop = 0
  		sleep 300
	endif
end #loop

if $exit_code == 1 then
   mv ${filename}/$argv[1]* ${directory}/subdirectory/.
$SQLPLUS/sqlplus -s / << EOF
   variable exit_code number
   DECLARE
      ...
   BEGIN
      ...
      :exit_code := 0;
      EXCEPTION
         WHEN OTHERS THEN
            :exit_code := 1; 
   END;
/
exit :exit_code
EOF
   mv ${DATA}/Subdirectory/$argv[1] ${DATA}/Subdirectory/archive/$argv[1]
   mv ${DATA}/Subdirectory/logfile_$argv[1]  ${DATA}/Subdirectory/archive/logfile_$argv[1]
   set exit_code = 0
endif
exit $exit_code
