Oracle创建假脱机文件,oracle – SQLPlus – 从PL / SQL块假脱机到多个文件

得到了解决方案,不知道为什么我没想到这个…

基本思想是主sqplplus脚本生成一个中间脚本,将输出拆分为多个文件.执行中间脚本将执行对rownum施加不同范围的多个查询,并为每个查询假脱机到不同的文件.

set termout off

set serveroutput on

set echo off

set feedback off

variable v_rowCount number;

spool intermediate_file.sql

declare

i number := 0;

v_fileNum number := 1;

v_range_start number := 1;

v_range_end number := 1;

k_max_rows constant number := 65536;

begin

dbms_output.enable(10000);

select count(*)

into :v_err_count

from ...

/* You don't need to see the details of the query... */

while i <= :v_err_count loop

v_range_start := i+1;

if v_range_start <= :v_err_count then

i := i+k_max_rows;

v_range_end := i;

dbms_output.put_line('set colsep ,

set pagesize 0

set trimspool on

set headsep off

set feedback off

set echo off

set termout off

set linesize 4000

spool large_data_file_'||v_fileNum||'.csv

select data_string

from (select rownum rn, data_object

from

/* Details of query omitted */

)

where rn >= '||v_range_start||' and rn <= '||v_range_end||';

spool off');

v_fileNum := v_fileNum +1;

end if;

end loop;

end;

/

spool off

prompt executing intermediate file

@intermediate_file.sql;

set serveroutput off