Shell script to call sql file


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Shell script to call sql file
# 1  
Old 03-15-2017
Shell script to call sql file

hi ,

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..

Code:
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
~

# 2  
Old 03-15-2017
Without digging deeper into this - some ideas (untested; no guarantee they will work):

- source a respective sql text file
- have a case statement with the different sql queries
- assign the sql queries to several variables and have those expanded in the here document

Give any of these a try and come back with the results.
# 3  
Old 03-15-2017
hi,

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.

it accepts 2 parameters as filename and emailid

sh test.ksh test.sql sk@xyz.com


test.ksh
------------
Code:
#!/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);

# 4  
Old 03-15-2017
You can't dump sql code in shell and expect it to go into your database. The shell is trying to process it as shell code and not understanding it.

To feed it into sqlplus, tell the shell to feed it into sqlplus, like so:

Code:
sqlplus sqlparameters <<EOF
text
text
text
EOF

The ending EOF cannot be indented, at all.
# 5  
Old 03-15-2017
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..


Code:
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);

This User Gave Thanks to itzkashi For This Post:
# 6  
Old 03-15-2017
replace 'text' with your query.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

How to call SQL Loader in shell script?

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)
Discussion started by: sam1234
1 Replies

2. Shell Programming and Scripting

Call sql script from UNIX shell script

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)
Discussion started by: FName_LName
3 Replies

3. Shell Programming and Scripting

Need Help: Shell script to call sql session with variables stored in .txt file

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)
Discussion started by: Khan28
4 Replies

4. Shell Programming and Scripting

Need to write shell script for my .sql file call

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)
Discussion started by: lakshmanraok117
7 Replies

5. UNIX for Advanced & Expert Users

call sql through shell script

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)
Discussion started by: tushar_spatil
1 Replies

6. UNIX for Advanced & Expert Users

Call parallel sql scripts from shell and return status when both sql are done

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)
Discussion started by: huasheng8
3 Replies

7. Shell Programming and Scripting

call shell script from pl/sql block

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)
Discussion started by: Opamps123
3 Replies

8. Shell Programming and Scripting

how to call shell script from pl/sql loop

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)
Discussion started by: rosalinda
1 Replies

9. Shell Programming and Scripting

how can i call a shell script from pl/sql

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)
Discussion started by: rajesh.P
3 Replies

10. UNIX for Dummies Questions & Answers

how can a call shell script from pl/sql

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)
Discussion started by: rajesh.P
0 Replies
Login or Register to Ask a Question