sql - 假脱机程序未将 dbms_output 写入文件

Question

我需要将循环的输出假脱机到文件中。我的代码如下:

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;

但是，当我检查 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:\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;

如何让它将循环输出写入文件？

Answer 1

SPOOL 是一个 SQL*Plus 命令。如果您想从 PL/SQL 写入文件，请使用 UTL_FILE 包。