Geek asks and answers

Spooler does not write dbms_output to file

I need to generate the loop output to a file. My code for this 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 looping through the output:

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 loop output to a file?

+3
source share
2 answers

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

+2
source

If you can change the code between the BEGIN block and END into one SQL, this can help you.

, , , , , , -

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;
0

More articles:


All Articles