Oracle SQL Script - Why spool creates file twice

Question

I run the following SQL-script using Oracle SQL Developer. I don't know why it creates two spool file and it also displays the entire operations in the Oracle SQL Developer console which it should not. Maybe it is due to the SET FEEDBACK ON and SET FEEDBACK OFF for each delete statement.

It creates two files and displays everything in script-output only when there are some records for deletion.

The script's call

@"C:\RM.sql"

The script

SET PAGES 0
SET LINESIZE 10000
SET TRIMS ON
SET ECHO OFF
SET HEADING ON
SET VERIFY OFF
SET FEEDBACK OFF
SET TERMOUT OFF
SET SERVEROUTPUT on size 1000000
SET TIMING OFF
SET COLSEP '|'

alter session set NLS_DATE_FORMAT = 'dd.mm.yyyy hh24:mi:ss';

whenever oserror exit -1
whenever sqlerror exit -2

-- The current timestamps into 'times' variable
column times new_value times noprint
select to_char(sysdate, 'YYYYMMDD_HH24MISS') times from DUAL; 

-- variables definition
define output_file="C:\temp\filename_&times..log"

-- echo some text to the standard output
SET TERMOUT ON
PROMPT
PROMPT "The script counts and reports the records to be deleted..."
PROMPT
SET TERMOUT OFF

-- write the results to the 'output_file' file
spool &output_file

PROMPT
PROMPT "BEGIN------------------------------"
PROMPT "delete from the_table1"
select 'Total records BEFORE delete: ' || count (*) as count_records from the_table1;

SET FEEDBACK ON
PROMPT "Actual deletion of records..."
delete from the_table1;
commit;
SET FEEDBACK OFF
PROMPT "END------------------------------"

PROMPT
PROMPT "BEGIN------------------------------"
PROMPT "delete from the_table2"
select 'Total records BEFORE delete: ' || count (*) as count_records from the_table2;

SET FEEDBACK ON
PROMPT "Actual deletion of records..."
delete from the_table2;
commit;
SET FEEDBACK OFF
PROMPT "END------------------------------"

PROMPT
PROMPT "The script completed..."

SPOOL OFF

SET TERMOUT ON
PROMPT
PROMPT "&output_file has been successfully ended."
PROMPT
PROMPT

EXIT
;

I just ran this scenario using SQL Developer version 20.2, and only one log file was SPOOLed/created — thatjeffsmith, Aug 10 '20 at 14:42
same here, only one file. By the way, if you are deleting the whole table, shouldn't you just truncate it ? — Roberto Hernandez, Aug 10 '20 at 19:48
@thatjeffsmith thanks for your feedback. I don't know why I cannot reproduce it now. — Sherzad, Aug 12 '20 at 08:42
@RobertoHernandez thanks for your feedback. I don't know why I cannot reproduce it more. — Sherzad, Aug 12 '20 at 08:42
@Sherzad can you provide in your question an output of your script execution that shows that actually it's creating two spool files ? — Roberto Hernandez, Aug 12 '20 at 09:26

score 1 · Answer 1 · answered Aug 10 '20 at 20:30

I seem to recall TERMOUT and ECHO options depend on how sqlplus is being run and can give different results, similar to what you are seeing.

I'm using sqlplus as a example of how interactions with the run environment can affect output. This isn't really an answer, and I cannot reproduce the scenarios I used to see often, but I hope the following perhaps will offer some pointers as to what might be encountered.

Start with a simple test script test_off.sql:

set echo off
set termout off

prompt text1

spool f1.txt
prompt text2
spool off
prompt text 3
quit

Now let's run it a number of different ways:

1 Non-interactively Parameterised

$ sqlplus -S un/pw@idb @test_off.sql


$ cat f1.txt
text2

No output to screen, spool file created, with only the spooled contents.

2. Interactively

$ sqlplus -S un/pw/@db
@test_off.sql
$ cat f1.txt
text2

No output to screen, spool file created, with only the spooled contents.

3. Run as piped feed

$ cat test_off.sql | sqlplus -S un/pw@db
text1
text2
text 3

Here we see all the PROMPT command results on screen.

Similar effects may be seen if run within a HEREDOC block.

I've definitely seen (in older versions) some strange inconsistencies and perhaps your SQL Developer is similarly afflicted.

Thanks for your info. Unfortunately, I am not able reproduce more the issue I addressed . — Sherzad, Aug 12 '20 at 08:44

Oracle SQL Script - Why spool creates file twice

1 Answers1