Geek Answers Handbook

SQLPlus - buffering multiple files from PL / SQL blocks

I have a query that returns a lot of data in a CSV file. In fact, that Excel cannot open it, there are too many rows. Is there a way to control spoolto buffer a new file every time 65,000 lines are processed? Ideally I would like to have its output to a file named in sequence, such as large_data_1.csv, large_data_2.csv, large_data_3.csv, etc.

I could use dbms_outputin a PL / SQL block to control the number of rows, but then how would I switch files, since spoolit doesn't seem to be accessible from PL / SQL blocks?

(Oracle 10g)

UPDATE:

I do not have access to the server, so writing files to the server will probably not work.

UPDATE 2:

Some of the fields contain free-form text, including lines, so counting line breaks AFTER the file is written is not as simple as counting WHILE records when data is returned ...

+5
source share
6 answers

Got a solution, I don’t know why I didn’t think about it before ...

The main idea is that the sqplplus script wizard creates an intermediate script that will split the output into several files. Executing an intermediate script will execute multiple requests with different ranges imposed on rownum, and link to another file for each request.

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
+8
source

Try this for a clean SQL * Plus solution ...

set pagesize 0
set trimspool on  
set headsep off 
set feedback off
set echo off 
set verify off
set timing off
set linesize 4000

DEFINE rows_per_file = 50


-- Create an sql file that will create the individual result files
SET DEFINE OFF

SPOOL c:\temp\generate_one.sql

PROMPT COLUMN which_dynamic NEW_VALUE dynamic_filename
PROMPT

PROMPT SELECT 'c:\temp\run_#'||TO_CHAR( &1, 'fm000' )||'_result.txt' which_dynamic FROM dual
PROMPT /

PROMPT SPOOL &dynamic_filename

PROMPT SELECT *
PROMPT   FROM ( SELECT a.*, rownum rnum
PROMPT            FROM ( SELECT object_id FROM all_objects ORDER BY object_id ) a
PROMPT           WHERE rownum <= ( &2 * 50 ) )
PROMPT  WHERE rnum >= ( ( &3 - 1 ) * 50 ) + 1
PROMPT /

PROMPT SPOOL OFF

SPOOL OFF

SET DEFINE &


-- Define variable to hold number of rows
-- returned by the query
COLUMN num_rows NEW_VALUE v_num_rows

-- Find out how many rows there are to be
SELECT COUNT(*) num_rows
  FROM ( SELECT LEVEL num_files FROM dual CONNECT BY LEVEL <= 120 );


-- Create a master file with the correct number of sql files
SPOOL c:\temp\run_all.sql

SELECT '@c:\temp\generate_one.sql '||TO_CHAR( num_files )
                                   ||' '||TO_CHAR( num_files )
                                   ||' '||TO_CHAR( num_files ) file_name
  FROM ( SELECT LEVEL num_files 
           FROM dual 
        CONNECT BY LEVEL <= CEIL( &v_num_rows / &rows_per_file ) )
/

SPOOL OFF

-- Now run them all
@c:\temp\run_all.sql
+3

split .

+1

utl_file - , . ( ), mod(num_rows_written,num_per_file) == 0 - . PL/SQL.

utl_file: http://www.adp-gmbh.ch/ora/plsql/utl_file.html

: , .

0

Excel ( , CSV Excel)? Oracle, , , . ( , - 64K Excel).

, - , UTL_MAIL . 32K , .

0

, , Excel, , - Excel, SQL (PL/SQL?), . , , . - .

, Excel .

, , , , , .

0

More articles:


All Articles