the below script contains sql query and after executed it sends the output of the query (output.txt) to an email body with conditional subject line based on the output of all_counts_match.txt.
i want to make this script generic so that it can accept the sql file as parameter and can be reuse for any other sql query and generates the output in a same way as it does..the problem is there are two sql queries so how to achive this?
thanks in advance..
sqlplus -s abc/yahoo@xe <<EOF
set feedback off trimspool on
set linesize 4000
set newpage 0
set pagesize 0
set wrap on
set echo off
set verify off
SET COLSEP "|"
alter session enable parallel dml;
spool output.txt
SELECT PROCESS_DATE ,table_name ,
(CASE WHEN table_row_count = input_record_cnt THEN 'Yes' ELSE 'No' END) COUNTS_MATCHING
FROM t1
WHERE insert_date = TRUNC(SYSDATE);
spool off
spool all_counts_match.txt
select case when sum(case when table_row_count = input_record_cnt then 1 else 0 end) = count(*)
then 1 else 0
end as x
from t1
where insert_date = trunc(sysdate);
spool off
exit
EOF
if [[ $(cat all_counts_match.txt) -eq 1 ]]; then
<output.txt mail -s "load counts Match for $(date '+%C%y%m%d')" sk@xyz.com
else
<output.txt mail -s "WARNING- load counts do not match for $(date '+%C%y%m%d')" sk@xyz.com
fi
rm -f output.txt
rm -f all_counts_match.txt
~
could any one tell me what is the issue with the below script.
i am gettin syntax error near unexpected token `from'
i have added the small piece of code highlighted in italics -underline below.
i am trying to send out an output of test.sql based on the result of all_counts_match.txt to an email.
#!/usr/bin/ksh
#_____________#
# Validate Parameters #
#________________________#
V_USER=$( whoami | tr -d ' ' )
RC=0
if [ $V_USER == "ACS " ]; then
echo "#_____________#"
echo "# ACS Mode #"
echo "#_____________#"
if [ ${V_SQL_FILE_LOC:-#} == "#" ]; then
echo "ERROR: V_SQL_FILE_LOC is not defined as a parameter"
RC=1
fi
if [ ${V_EMAIL_ADDR:-#} == "#" ]; then
echo "ERROR: V_EMAIL_ADDR is not defined as a parameter"
RC=1
fi
if [ $RC -eq 1 ]; then
exit 1
fi
elif [ $# -lt 2 ]; then
echo "#_______________________________________________________________________#"
echo "# NOTE: Script will run SQL file and email results to email group"
echo "#_______________________________________________________________________#"
exit 1
else
export V_SQL_FILE_LOC=$1
export V_EMAIL_ADDR=$2
fi
#________________________#
# Configuration #
#________________________#
export V_DATETIME=$( date "+%Y%m%d%H%M%S" )
export V_SQL_FILE_BASE=$( basename ${V_SQL_FILE_LOC} )
export V_LOG_FILE_BASE="test_t1_${V_SQL_FILE_BASE}_${V_DATETIME}.log"
export V_LOG_FILE="${xyz_dd}/${V_LOG_FILE_BASE}"
#________________________#
# Validate Parameters #
#________________________#
echo "o Validating Parameters"
if [ -a ${V_SQL_FILE_LOC} ]; then
echo "o SQL File Exists."
echo "[X] PASS"
else
echo "o SQL File Does Not Exist."
echo "o Now Exiting..."
exit 1
fi
#________________________#
# Helper Functions #
#________________________#
#_____________________________________#
# BEGIN #
#_____________________________________#
{
echo " " > ${V_LOG_FILE}; chmod 666 ${V_LOG_FILE}
echo "Start: `date +'%F %T'`"
v_audit_name=$( echo $V_SQL_FILE_BASE | cut -f2 -d'.' | cut -f1 -d'.' )
v_temp_sql=${ABC_LOOKUP}/${v_audit_name}.${V_DATETIME}.sql
v_spool_header="set heading off\n
set newpage 0\n
set pages 0\n
set feedback off\n
set linesize 5000\n
set trimspool on\n
set echo off\n
\n
spool $AI_RETS/audit_output.${v_audit_name}.${V_DATETIME}.dat\n
"
echo -e $v_spool_header > ${v_temp_sql}
cat ${V_SQL_FILE_LOC} >> ${v_temp_sql}
echo "spool off" >> ${v_temp_sql}
export V_SQL_FILE=${v_temp_sql}
sqlplus -s xyz/abx@D1 < $V_SQL_FILE; ORCL_RC=$?
if [ $ORCL_RC -ne 0 ]; then
echo "ERROR: SQL*Plus encountered an error while running a sql command"
echo "Now exiting"
exit 1
fi
spool all_counts_match.txt
select case when sum(case when table_row_count = input_record_cnt then 1 else 0 end) = count(*)
then 1 else 0
end as x
from t1
where insert_date = trunc(sysdate);
spool off
if [[ $(cat all_counts_match.txt) -eq 1 ]]; then
<${AI_RETS}/audit_output.${v_audit_name}.${V_DATETIME}.dat| mail -s "load counts Match for $(date '+%C%y%m%d')" sk@abc.com
else
<${AI_RETS}/audit_output.${v_audit_name}.${V_DATETIME}.dat| mail -s "WARNING- load counts do not match for $(date '+%C%y%m%d')" sk@abc.com
fi
rm -f all_counts_match.txt
rm -f ${v_temp_sql}
echo "End: `date +'%F %T'`"
} 2>&1 | tee -a ${V_LOG_FILE}
test.sql
---------
select tname , source_name ,
(CASE WHEN table_row_count = input_record_cnt THEN 'Yes' ELSE 'No' END) COUNTS_MATCHING
from t1
WHERE insert_date = TRUNC(SYSDATE);
sorry i am not able to get your point..
i want to use this sql query inside the script ...pls let me know how can i do this..
select case when sum(case when table_row_count = input_record_cnt then 1 else 0 end) = count(*)
then 1 else 0
end as x
from t1
where insert_date = trunc(sysdate);