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)


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 &
while [ $i -lt 100 ]
cat >>testpipe.sql <<EOF
prompt =============
select to_char(sysdate, 'hh24:mi:ss') ttime from dual
sleep 10
i=`expr i + 1`
# Now we're done, we can quit SQL*Plus
cat >>testpipe.sql <<EOF
spool off
# 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 

In the end, you pay your money and take your choice.


Regards Nigel

Other related posts: