Spooler not writing dbms_output to file

Question

I need to spool the output of a loop to a file. My code for that is below:

set termout off       
set echo off       
set colsep ''
set linesize 5000  
set heading off    
set feedback off
set preformat off
set trimspool on   
set serverout on

spool 'C:\folder\script.sql' 
begin
  for rec in (select unique prgm_id from tmp_table_output) loop
    for rec2 in (select unique cmpg_id from tmp_table_output where prgm_id = rec.prgm_id) loop
      dbms_output.put_line('spool ''C:\folder\PRG''||lpad('||rec.prgm_id||', 4, 0)||''_CMPG''||lpad('||rec2.cmpg_id||', 4, 0)||''.txt''  CREATE');
      dbms_output.put_line('SELECT field FROM tmp_table_output where prgm_id = '||rec.prgm_id||' and cmpg_id = '||rec2.cmpg_id);
      dbms_output.put_line('spool off');
    end loop;
  end loop;
end;
/
spool off;

However, when I check the script.sql file, instead of actually iterating through the loop, the output is:

begin
  for rec in (select unique prgm_id from tmp_table_output) loop
    for rec2 in (select unique cmpg_id from tmp_table_output where prgm_id = rec.prgm_id) loop
       dbms_output.put_line('spool ''C:\RemoteOnboarding\PRG''||lpad('||rec.prgm_id||', 4, 0)||''_CMPG''||lpad('||rec2.cmpg_id||', 4, 0)||''.txt''  CREATE');
       dbms_output.put_line('SELECT field FROM tmp_unica_output where prgm_id = '||rec.prgm_id||' and cmpg_id = '||rec2.cmpg_id);
       dbms_output.put_line('spool off');
    end loop;
  end loop;
end;
/
spool off;

How do I get it to write the loop output to the file?

I would check that the `/` is on the first column of the line (i.e. no spaces before it); also, put a `dbms_output.put_line('prompt starting...');` immediately after the `begin` to check that your block is actually being executed - perhaps one of the queries are returning no records, which would explain why the output isn't being generated. — Jeffrey Kemp, Feb 26 '14 at 00:14
Rewrite it as a single query and spool the result. There is absolutely no need for looping through result set imitating join. Moreover, `preformat` is an option of the `markup` command. You do not use it alone and definitely will receive "unknown set option " error when you do. — Nick Krasnov, Feb 26 '14 at 07:14

score 2 · Answer 1 · answered Feb 25 '14 at 22:53

2

SPOOL is a SQL*Plus command. If you want to write to a file from PL/SQL, use the UTL_FILE package.

answered Feb 25 '14 at 22:53

Mark J. Bobak

13,720
6
39
67

So there is no way to get this code functioning? I'd have to use the UTL_FILE package? – Danger_Fox Feb 25 '14 at 22:58
If you are using SPOOL, what you get, is a transcript of everything that happened in SQL*Plus. So, you should see the result of the DBMS_OUTPUT mixed in with the code itself. If you want PL/SQL to write a clean file, that only contains what you are writing from PL/SQL, you should ude UTL_FILE. – Mark J. Bobak Feb 25 '14 at 23:04
Actually, I'm not sure this is entirely correct (I may be wrong so I'm not downvoting yet). `UTL_FILE` is only useful if the output is to go to a file on the server - SPOOL is useful to get a file on the client. The OP does have `SPOOL 'C:\folder\script.sql'`, he has `set serverout on`, so I'd expect the output file to have both the PL/SQL block, and then the `/` should execute the block. I've written this style of SQL*Plus script before and it can work - basically it is being used to generate a series of SQL*Plus scripts. – Jeffrey Kemp Feb 26 '14 at 00:12
Jeffrey, you're right that UTL_FILE is only useful for writing to the server, whereas spool will write to the client, where ever SQL*Plus happens to be running, and I should have made that distinction. I guess it wasn't 100% clear to me, exactly what the objective is. Also, the comment the OP made, regarding the fact that he's not seeing the output of the code loop, only the code he's typing into SQL*Plus, seems to indicate a problem, which I see you already noted in your comment to the question. Let's see what the OP has to say, and I'll either edit my answer, or delete it. – Mark J. Bobak Feb 26 '14 at 00:34
@JeffreyKemp You're correct in what I want to do. Sorry if that wasn't clear. Based on your comment, I'm still not able to see what my issue is. – Danger_Fox Feb 26 '14 at 15:46

score 0 · Answer 2 · answered Feb 26 '14 at 06:27

If you can change the code between your BEGIN and END block into a single SQL, it may help you.

Here one example how you can do it, the code is not tested as I do not have all your setup at my end, there could be some syntax errors as well, try and let me know if you are getting any error

set termout off       
set echo off       
set colsep ''
set linesize 5000  
set heading off    
set feedback off
set preformat off
set trimspool on   
set serverout on

spool 'C:\folder\script.sql' 
select text from (
    select unique 1 as rn, rec.prgm_id, rec2.cmpg_id, 'spool ''C:\folder\PRG''||lpad('||rec.prgm_id||', 4, 0)||''_CMPG''||lpad('||rec2.cmpg_id||', 4, 0)||''.txt''  CREATE' as text
      from tmp_table_output rec, tmp_table_output rec2
     where a.prgm_id = b.prgm_id
    union all
    select 2, rec.prgm_id, rec2.cmpg_id, 'SELECT field FROM tmp_table_output where prgm_id = '||rec.prgm_id||' and cmpg_id = '||rec2.cmpg_id
      from tmp_table_output rec, tmp_table_output rec2
     where a.prgm_id = b.prgm_id
    union all
     select unique 3, rec.prgm_id, rec2.cmpg_id, 'spool off'
      from tmp_table_output rec, tmp_table_output rec2
     where a.prgm_id = b.prgm_id
 )
 order by prgm_id, cmpg_id, rn;
spool off;

Trying your code gives the same results as mine. It just outputs the pl/sql — Danger_Fox, Feb 26 '14 at 15:57
Are you sure? I used SQL only, no PL/SQL. Can you run the select statement only in SQL*PLUS and check if the output is proper or not? — San, Feb 26 '14 at 17:40

Spooler not writing dbms_output to file

2 Answers2