Running SQL Scripts from Shell script - Need insight!

Shell Programming and Scripting
1

I've a script that fetches various values from the database as below:

#! /bin/ksh

$conn="user/pwd@service_name"
`sqlplus -s << $conn EOF1
@xyz.sql
@pqr.sql
@abc.sql
EOF1`

The output of the script should generate txt files containing the results from queries which are further manipulated for display / reporting.
However, when the scripts executes, Oracle throws an exception
SP-0734 : not found .. as against running a direct query within the HERE docs (<< EOF1 EOF1). The same exception doesn't arise when the entire block is assigned to a return variable.

Can someone focus on why this shows up?

TIA
Sirisha

2

Try this syntax:


conn="user/pwd@service_name"
sqlplus -s $conn <<EOF1
@xyz.sql
@pqr.sql
@abc.sql
EOF1
3

Oops.. sorry for that mistake. That's a typo here.. and what i did was what you suggested.. thanx for the correction!

Rather i even tried
`sqlplus -s "user/pwd@service_name" <<EOF1
@xyz.sql
@pqr.sql
@abc.sql
EOF1`

with the same error.. can you pls think of why the error pops up if the whole thing is not assigned to return variable?
Any other suggestions are welcome.

Thnx again!

4

Why are you using the back ticks (`)?

5

To consider the sequence of sql statements as single block of execution.. anything wrong with them.

6

That is what you are doing with here document enclosed by <<EOF1 and EOF1. The back ticks return the output of the sqlplus command and then, in your case, attempts to execute it.

If you want to capture the output of sqlplus in a variable or an array, then use the back ticks.

set -A RESULTS_ARRAY `sqlplus -s "user/pwd@service_name" <<EOF1
@xyz.sql
@pqr.sql
@abc.sql
EOF1`

for i in ${RESULTS_ARRAY[@]}
do
    echo $i
done
7

That's pretty clear now! Thank you so much :slight_smile: