| Adrian Billington 2005-03-29, 7:50 am |
| I posted this in C.U.Shell about 6 days ago and had no responses, so
at risk of being accused of cross-posting, I'm trying here instead :o)
Hi,
I am writing a ksh script that is going to run through a directory
full of SQL scripts to be applied to an Oracle database. I'd really
like to do this in a single Oracle session using a coprocess rather
than have each SQL script run in a "HERE-document" which would imply a
new Oracle connection per SQL file.
Below is a really simple template of what I want to do, minus the SQL
file management, but the principle is the same. Here's the script and
I'll explain what it is I'm actually looking for afterwards.
****************************************
***********
#!/bin/ksh
function open_conn
{
sqlplus -s |&
print -p user/pass@db
}
function run_sql
{
print -p "${1}"
print -p "prompt END_OF_OUTPUT_STREAM"
while read -p outstream
do
if [[ ! ${outstream} == "END_OF_OUTPUT_STREAM" ]]
then
echo "${outstream}"
else
break
fi
done
}
function close_conn
{
run_sql "exit"
}
open_conn
run_sql "@create_table.sql"
run_sql "@create_another_table.sql"
run_sql "@create_index.sql"
close_conn
****************************************
***********
This works OK as a framework. However, I want to build in error
checking but am having difficulty getting the return status of the
coprocess. Capturing $! and using it in the "wait ${bpid}" command
does not work for me because it leaves me hanging. And of course, $?
after the print -p commands tell me the print -p commands were OK.
I need to catch the return code of everything I send to Oracle. The
output stream terminator only works as long as I can get to the end of
a script or statement being sent down the pipe. So I'm not sure if
there's an answer to this other than use a HERE document...
TIA
Adrian
|