Re: Run command on sqlplus repetitively without reconnection.

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

--
http://www.freelists.org/webpage/oracle-l