Shell Programming and Scripting

hi everyone, rookie scripter here. Under some time pressure here and hoping you can help. I've written a .csh script that needs to check a row count in an Oracle table, then do an IF-THEN check against that count. Two different select statements actually, one after another. If either of the two counts fail to meet expectation, I want to exit the script. Here's what I have:

#!/bin/csh -f
source ~/.cshrc
#
echo `date` "+--------------> "Command was `basename $0`\.
set USAGE="USAGE=ltz_check_eodevent_process.csh"
set SQLPLUS = ${ORACLE_HOME}/bin/sqlplus
echo `date` " starts sql query for checking the EOD counts"
${SQLPLUS} -s << EOF
$DB_LOGIN
SET VERIFY OFF
SET TERMOUT OFF
SET ECHO OFF
SET FEEDBACK OFF
SET TRIMSPOOL ON
SET LINESIZE 300
SET PAGESIZE 200
SET COLSEP ' '
SET SERVEROUTPUT ON SIZE 999999
WHENEVER SQLError EXIT FAILURE

select count(1) as count_of_rows from put_groups where group_name='PC'
and description='EOD_EVENT' and serial_number=1;

IF count_of_rows < 1 THEN
raise_application_error(-20001,'EOD message for PC not found');
END IF;

select count(1) as count_of_rows from position_intraday where
added_date like to_char(sysdate,'YYYYMMDD')||'%' and pre_eod_flag is null
and added_by='MSG-PC' and to_date(substr(added_date,1,12),'YYYYMMDDHH24MI') <=
(select to_date(substr(element_value_1,1,10)||substr(element_value_1,12,8),'YYYY-MM-DDHH24:MI:SS')
as msg_date
from put_groups where group_name='PC' and description='EOD_EVENT' and serial_number=1);

IF count_of_rows > 0 THEN
raise_application_error(-20001,'EOD message for PC was not processed correctly);
END IF;

EOF
#
success:
echo " "
echo `date` "+++++++++> ltz_check_eodevent_process.csh finished successfully."
echo `date` "---------------> "`basename $0` done\.
echo " "
exit 0

failure:
echo `date` "=========> ltz_check_eodevent_process.csh failed."
echo `date` "---------------> "`basename $0` done\.
exit 1
########################

The problem is, the IF-THEN block is not being recognized. The select statements are both valid and they are working within this script, but it's not exiting. Ideally, I'd like to use "goto failure" if the count doesn't meet what I want. I don't need to create files or send any emails. Just look at the count, proceed to next select/check OR exit via failure.

Maybe I need a BEGIN-END block somewhere? Help? THANKS!!!

It looks to me like you are attempting to use shell if-then logic inside your SQL code. Since you've turned off all the notifications, you are not seeing the error messages that this will surely generate.

If I am reading the code correctly, you will want to either replace those IF-THEN statements (which are not valid in SQL) with the Oracle-specific DECODE structure. The DECODE is not a true IF-THEN-ELSE, but can usually be made to work with some tweaking.

Alternatively, you could run your select statements and redirect the output to an OS file which you can then analyze with a shell script.