Re: Run command on sqlplus repetitively without reconnection.
- From: Nigel Thomas <nigel_cl_thomas@xxxxxxxxx>
- To: Ajay_Thotangare@xxxxxx, Jared Still <jkstill@xxxxxxxxx>
- Date: Wed, 9 May 2007 14:15:46 -0700 (PDT)
Ajay
If you don't want to get into perl for this requirement, you can easily do it
using a shell script.
# script start
rm -f testpipe.sql
# create a pipe for the files
# syntax may vary - this is bash/cygwin
mknod testpipe.sql p
echo spool output.log testpipe.sql
sqlplus -s testuser/testuser <testpipe.sql &
i=0
while [ $i -lt 100 ]
do
cat >>testpipe.sql <<EOF
prompt =============
select to_char(sysdate, 'hh24:mi:ss') ttime from dual
/
EOF
sleep 10
i=`expr i + 1`
done
# Now we're done, we can quit SQL*Plus
cat >>testpipe.sql <<EOF
spool off
exit
EOF
# script end
This uses SQL*Plus to do what it's good at: run the SQL and format the output;
it uses the shell to do what it can do well: organise the looping. Because the
commands are piped into test.sql, SQL*Plus only needs to connect once.
In this example there is no integration of control. If you had to break out
based on the results of the query itself, this mechanism might become rather
cumbersome (possible, but kludgy). The closer integration you need between the
data and the control, the better the Perl route sounds.
In comparison, the PL/SQL route that another poster mentioned can also work
well; but one thing it isn't particularly good at is delivering output back to
the user (there are plenty of ways around that, including sending UTL_FILE
output to a unix pipe, and tailing that, or sending output via DBMS_PIPE to a
monitor).
In the end, you pay your money and take your choice.
Enjoy
Regards Nigel
Other related posts:
