Hello,
Can you tell me why the last command(echo) in this script does not execute?
In the command line I have to hit control -d to terminate this tag but I dont know how to do it in a shell script.
sqlplus -s <<EOF
connect / as sysdba
select sysdate from dual;
quit;
EOF
echo "other things to do"
The EOF tag needs to be flush with the left margin. I often use __EOF with some leading underscores to make the indentation look the way I like.
sqlplus -s <<'__EOF'
connect / as sysdba
select sysdate from dual;
quit;
__EOF
echo "other things to do"
Thank you. Do you also know how to redirect this output to a file or pipe it?
Because when I try with "> file" it just contunues to ask for new imput.
I don't understand your question, but maybe a bunch of examples can help.
vnix$ cat <<HERE | wc
two words
make that five
HERE
2 5 25
vnix$ nl <<HERE | sed 's/no//' >/tmp/ovtpvt
foo no line numbers
HERE
vnix$ cat /tmp/ovtpvt
1 foo line numbers
vnix$ cat <<HERE >/tmp/song.txt
I'm a ${work} and I'm $adjective
HERE
vnix cat /tmp/song.txt
I'm a Lumberjack and I'm okay
... the last example of course assuming you have set the environment variables $work and $adjective correctly. (-:
What I'm after is something like this(which does not work of course):
sqlplus -s <<'__EOF'
connect / as sysdba
select sysdate from dual;
quit;
__EOF >> file_to_output.txt # (or | awk something...)
So I need the output of this (not necessary in a file) in order to process it in the script.
try to execute below to direct the output; might this will help you.
db_date=`sqlplus -s / <<EOF
select sysdate from dual;
EOF
`
echo $db_date>> file_to_output.txt
Yes, but the query I'm giving will be more complex and will output many rows.
The "echo $var" will suppress all of them in only one row.
I need a way to output the result as it is displayed on the console.
LE: Actually I may have found a way but cant test it right now as I dont have access to an sqlplus
Maybe something like this will work?
$~ sort > tempfile <<'EOF'
field2
field1
EOF
$~ cat tempfile
field1
field2
To send output to a file...
sqlplus -s <<'__EOF' >> output_file.txt
connect / as sysdba
select sysdate from dual;
quit;
__EOF
To pipe it to a command...
sqlplus -s <<'__EOF' | awk '{print}'
connect / as sysdba
select sysdate from dual;
quit;
__EOF
It works, thank you.
One more question about this tag:
How can I make this variable substitution inside EOF?
$newv and ${newv} are interpreted literally
for i in `cat textfile`
do
newv="ALTER SYSTEM KILL SESSION '$i' IMMEDIATE;"
sqlplus -s <<'EOF'
connect / as sysdba
$newv
quit
EOF
done
output is:
SP2-0042: unknown command "$newv" - rest of line ignored.
As illustrated by the examples I posted, all it takes is removing the single quotes around EOF.
As an aside, the cat in backticks is an anti-pattern.
while read i; do
newv="ALTER SYSTEM KILL SESSION '$i' IMMEDIATE;"
sqlplus -s <<____EOF
connect / as sysdba
$newv
quit
____EOF
done <textfile
Thank you very much era 
Try bracketing the whole lot with parentheses.
(
sqlplus -s <<EOF
connect / as sysdba
select sysdate from dual;
quit;
EOF
) 2>&1 | awk_or_whatetever_pipeline > my_filename
... if you for some weird reason can't bring yourself to use the correct syntax which was posted earlier.
sqlplus -s <<EOF | awk_or_whatever_pipeline >my_filename
dit dit dah
EOF
You can mix and match the various redirect options in shell. For example if you want to run two sqlplus sessions, filter each one and output the final results to a file.
(
sqlplus -s <<EOF | awk_or_whatever_pipeline_one
dit dit dah
EOF
sqlplus -s <<EOF | awk_or_whatever_pipeline_two
dit dit dah
EOF
) 2>&1 >my_filename
Back to the point. In Oracle you may wish to check that v$session is current before issuing the kill. Don't forget to escape v\$session if it's mentioned in a unix shell.