SQLPlus - буферизация в несколько файлов от МН блоков / блоков SQL
У меня есть запрос, который возвращает много данных в файл CSV. Так, на самом деле, что Excel не может открыть его - существует слишком много строк. Есть ли способ управлять spool для буферизации в новый файл каждый раз, 65 000 строк были обработаны? Идеально, я хотел бы иметь свой вывод в файлах, названных в последовательности, такой как large_data_1.csv, large_data_2.csv, large_data_3.csv, и т.д...
Я мог использовать dbms_output в МН блоке / блоке SQL для управления, сколько строк производится, но затем как я переключил бы файлы, как spool кажется, не доступен от МН блоков / блоков SQL?
(Oracle 10 г)
ОБНОВЛЕНИЕ:
У меня нет доступа к серверу, таким образом пишущий, что файлы к серверу, вероятно, не работали бы.
ОБНОВЛЕНИЕ 2:
Некоторые поля содержат текст свободной формы, включая разрывы строки, таким образом считая разрывы строки ПОСЛЕ ТОГО, КАК файл записан, не так легко как рассчитывающие записи, В ТО ВРЕМЯ КАК данные возвращаются...
5 ответов
Есть решение, не знаю, почему я не подумал об этом раньше ...
Основная идея заключается в том, что главный сценарий 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
Вы смотрели на настройку подключения к внешним данным в Excel (предполагая, что файлы CSV создаются только для использования в Excel)? Вы можете определить представление Oracle, которое ограничивает возвращаемые строки, а также добавить некоторые параметры в запрос, чтобы пользователь мог дополнительно ограничить набор результатов. (Я никогда не понимал, что кто-то делает с 64К строками в Excel).
Я считаю, что это своего рода взлом, но вы также можете использовать UTL_MAIL и создавать вложения к электронным письмам для ваших пользователей. Размер вложений ограничен 32 КБ, поэтому вам придется отслеживать размер в цикле курсора и начинать новое вложение на этом основании.
utl_file - это пакет, который вы ищете. Вы можете написать курсор и перебирать строки (записывая их), а когда mod(num_rows_written,num_per_file) == 0, пора начинать новый файл. Это прекрасно работает внутри блоков PL/SQL.
Вот ссылка для utl_file:
http://www.adp-gmbh.ch/ora/plsql/utl_file.html
NOTE: Я предполагаю, что можно записывать файлы на сервер.
Попробуйте это для чистого решения SQL * Plus ...
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