This discussion is archived
2 Replies Latest reply: Jan 7, 2013 6:11 AM by 889367 RSS

spool output with pause and prompt

889367 Newbie
Currently Being Moderated
10.2.0.5 & 11.2.0.2 on linux & windows

From time to time I have to run scripts that include large data sets an inserting a large number of rows. I have to spool the results of these scripts I get to go back and make sure there were no errors. I put pause statements in between various steps of the scripts in an attempt to verify that the last portion succeeded before proceeding with the execution.
The problem I'm running into is that when I spool the output, if there's a large amount of data to sift through, it isn't all flushed to the spool file until after I've hit enter to proceed past the pause statement.

Is there a way to make sure that all data is flushed to the spool file real time and not held in the buffer? I'm assuming it's being held in a buffer.

For an example you can run the following and monitor the spool file at each pause in the script. I'm not seeing the number of rows returned for each select until after the next select processes or even after the whole script is complete.

main_test.sql:
set sqlprompt "_USER'@'_CONNECT_IDENTIFIER> "
set echo on
set termout on
set time on
set timing on
set trimspool on
set pages 0
column sname new_value spool_name
select to_char(sysdate, 'yyyy_mm_dd_hh24_mi_ss')
|| '.' || sys_context('USERENV', 'SERVER_HOST')
|| '.' || sys_context('USERENV', 'DB_NAME') as sname
from dual;
spool &&spool_name..log

--###############################
--###DEPLOYMENT STARTS HERE######
--###############################
@@test.sql
prompt end of test
pause
exit;


test.sql:
select 1,2 from dba_objects;
prompt end of first select
pause
select 1,2 from dba_objects;
prompt end of 2nd select
pause
select 1,2 from dba_objects;
prompt end of 3rd select
pause

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points