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

Shell Programming and Scripting
1

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.

The data in text file will be like following:

0322600453
CAN
5076

2867000260
CAN
3496

sqlplus -S <<EOF > /dev/null
user/password@universe
WHENEVER OSERROR EXIT 9;
WHENEVER SQLERROR EXIT SQL.SQLCODE;



SET ECHO ON
SET SERVEROUTPUT ON
SPOOL $LOGFILE

-------------------------------------------------------------------------------------
---- Here I need to make a loop to read text file and pass on the first line as 
---- parameter to following .sql script till all the rows are not finished in text 
---- file
-------------------------------------------------------------------------------------
@cancel.sql 

SPOOL OFF
EOF



sql_return_code=$?

if [ $sql_return_code != 0 ]
then
echo "The Cancel.sql script failed. Please refer to the log results.txt for more information"
echo "Error code $sql_return_code"
exit 0;
fi
2

Hello Khan28,

Welcome to forum, you can use following command for same which will take input from a file(with sql queries) and put it's output to a output file.

sqlplus user/password@universe < QUERIES.sql > Output_file

As I am not that much clear on your requirement so just an idea. Then after saving the sql output to a file you can check it's data if any error or according to your need you can perform the operations.

Thanks,
R. Singh

3

Hi R Singh,
Thanks for your prompt reply. The requirement is to read values from txt file and pass those to sqlplus session for each record coming from the txt file. The pseudo code will be like this

While line in $ cancel.txt
do
sqlplus cancel.sql "How to read first parameter of the text file here"
done

4

Hello Khan28,

You can try with following as a startup. Let's say we have cancel.txt as follows.

cat cancel.txt
USER_NAME= Singh
Password= xyz
universe= abscedd

Then script can be as follows.

cat test_script.ksh
user=`awk -F"=" '/USER_NAME/ {print $2}' cancel.txt`
password=`awk -F"=" '/Password/ {print $2}' cancel.txt`
universe=`awk -F"=" '/universe/ {print $2}' cancel.txt`
sqlplus "$user"/"$password"@"$universe" < QUERIES.sql > Output_file
 
you can do testings here with Output file as per your reqiurements.

Thanks,
R. Singh

5

SQL is a descriptive language, not a procedural and it does not support constructs like loops. You'll have to construct the loop in the shell script and call the sql-client within this loop.
Does the sql-commandfile cancel.sql already accept the parameters? The first paramerer (in cancel.sql) is referenced as &1 (unless you changed the default via set define ).
I would put all SQL*Plus commands (WHENEVER, SET... and SPOOL) into cancel.sql and then write your script like this if you are sure the file Cancel_ID.txt is always in the correct format (maybe validate that as start of the script):

#!/bin/bash
# read first line holding the parameter until there is nothing more to read
while read CANCEL_ID; do   
   sqlplus -s user/password@universe @cancel.sql $CANCEL_ID
   sql_return_code=$?
   if [ $sql_return_code != 0 ]
   then
      echo "The Cancel.sql script failed. Please refer to the log results.txt for more information"
      echo "Error code $sql_return_code"
      exit 0;
   fi
   # read second line and ignore it
   read DUMMY                     
   # read 3rd line and ignore
   read DUMMY
   # read empty line between the paragraphs and ignore
   read DUMMY                  
# feed the file to the loop
done <Cancel_ID.txt