Oracle Database Discussions

Hi, I'm quite new in shell scripting. I created the below simple script to do a check on the database, it's a count so it works fine... if the database is there. Now I want to be able to catch if the database is or is not there, what I tried so far didn't work, can anybody please give me a hand with this?

The shell script file "start_check.sh":
-----------------------------------------------------------------------------------------------------------------------------
#!/bin/bash
# Set environmental variables
. /home/oracle/env/usrdwh1.env

TEMP_FILE=rwcnt

runCheckQuery()
{
# Run query and dump result into the TEMP_FILE
sqlplus -s "/as sysdba" > /tmp/${TEMP_FILE} << EOF
@/u02/reports/sql/start_check.sql
EOF
if [ $? -eq 0 ]
then err_num=0
else err_num=1
fi
}

################ MAIN ####################

runCheckQuery
row_count=`cat /tmp/${TEMP_FILE}`

if [ $err_num -eq 0 ]
then
# If no rows were found then send an email alert
if [ $row_count -eq 0 ]; then
echo 'No process found - Please investigate' | mailx -s "Daily check ALERT" dba@somedomain.com
fi
else
# There was an error when trying to connect to the db. Need to report it
echo 'Database connection error - Please investigate - Error Message: (' $row_count ')' | mailx -s "Daily check ALERT (Database connection error)" dba@somedomain.com
fi

# Remove the tmp file
rm /tmp/${TEMP_FILE}
-----------------------------------------------------------------------------------------------------------------------------


The sql script file "start_check.sql":
-----------------------------------------------------------------------------------------------------------------------------
SET SERVEROUTPUT ON
SET FEEDBACK OFF
WHENEVER SQLERROR EXIT;
DECLARE
row_count NUMBER;
BEGIN
SELECT COUNT(*)
INTO row_count
FROM dw_ml_ba ml_ba
WHERE sys_id = 'CCX'
AND ml_ex_start_datetime > TRUNC(sysdate);
DBMS_OUTPUT.PUT_LINE(row_count);
END;
/
EXIT
-----------------------------------------------------------------------------------------------------------------------------

Edited by: leocoppens on Jan 4, 2013 4:05 PM