Shell Programming and Scripting

Hi guys.


I m creating scripts which input multiple value , inside sqlplus script when it prompt/accept
do anybody know how to provide multiple value inside sqlplus script when it prompt.

like,

enter value for first:
enter value for second:
enter value "save file as " :

I m try to provide these values when sqlplus script ask. but provided parameters will be generated from shell script.

Can somebody help me .

If there's a shell script involved why not just have it ask?

Code:

printf "Enter value: "
read VALUE
echo "Value is ${VALUE}"

I have sql script which called internally from shell ...

I cant change code of sql script which is provided by oracle itself.

Exactly my point.

its something like this

#!/bin/ksh

for i in x do
y=$x+1

sqlplus -s @/mydir/xyz.sql
(inside this xyz.sql script it ask few values. at that time i want to provide $x $y to it)

since i cant change sql script i m finding solution if it can b possible to provide it.

Oh I see, the SQL script does ask. I thought you wanted to modify it to ask.

You could try

Code:

sqlplus -s @/mydir/xyz.sql <<EOF
response1
${variable2}
response3
EOF
# The above EOF must be at the BEGINNING of the line!  No spaces in front!

If you need to run this non-interactively, you might look into using 'expect'. It can match incoming lines by regular expressions and provide a response. I'm not well-versed in it, but I've seen it do some cool things including completely automated responses based on timeouts and the data it receives.

If this is an Oracle-supplied script, someone else may have solved this already - a quick web search for the script might turn something up.


Also note, if you want some formatting, some shells allow you to add a "-" before the marker to ignore leading tab characters in the here-doc. This could be written as

Code:

sqlplus -s @/mydir/xyz.sql <<-EOF
	response1
	${variable2}
	response3
	EOF # Note that these the beginning of each line are tabs, not space characters.