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
---------
Code:
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);
I like to call a shell script from pl/sql proceduere and i have to use the shell script return value in that procedure. i am using oracle 9i and cygwin.
can any one suggest me how can i do this (0 Replies)
I would like to call the shell script from pl/sql and i need to uses the value returned by the shell script in pl/sql procedure.
can any one suggest me how can i do that? (3 Replies)
Hello,
I am doing a shell script which contain a pl/sql loop to search for 3 values, i would like to call another shell script inside this sql loop each time it find the values. so how can i call shell script from pl/sql using its variables, any idea?
Here is idea about the code:
my... (1 Reply)
Hi Experts,
I want to call script_name.ksh as many time as id in customer table and also pass it as a parameter to script.
someting Like below.
for i in select id from customer
do
./script_name.ksh $i &
done
I have figured out how to have ID from customer but now how to call... (3 Replies)
Hi Experts:
I have a shell script that's kicked off by cron. Inside this shell script, I need to kick off two or more oracle sql scripts to process different groups of tables. And when both sql scripts are done, I will continue in the shell script to do other things like checking processing... (3 Replies)
Hi i am not able to connect sqlplus
my script is as follows
$ORACLE_HOME/bin/sqlplus << ! > /tmp/extract/DM.txt
and output is
SQL*Plus: Release 11.1.0.7.0 - Production on Wed Jan 18 02:53:54 2012
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Enter user-name: t175481... (1 Reply)
Hi Guys,
I need to write a simple shell script which will generate a .csv file/report by calling .sql file inside a shell script.
Can somebody help me on this.
Thanks in advance!
Regards,
LK (7 Replies)
Hi,
I need help in writing a shell script which can read data from a text file (Cancel_ID.txt) and then calls sqlplus session (Cancel.sql) with the first line parameter of the text file ("0322600453") till all rows are not completed.
... (4 Replies)
I know this question is out there in many forums, but I tried all the combinations in vain.
I'm basically trying to call a sql script from a shell script.
Below is my sql script (plsql.sql)
DELCARE
v_empno NUMBER := '&empno';
BEGIN
select ename,sal from emp where empno = v_empno;... (3 Replies)
HI Experts,
I am pretty new to scripting and i need to create a perl or shell script which should fetch a file from local directory and insert the data into a table using sql loader. This will be later added to chron job to run daily and fetch all files and load them into
the table.
Also i... (1 Reply)