2 Replies Latest reply: Jan 7, 2013 8:11 AM by 889367 RSS

    spool output with pause and prompt

    889367
      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