Не хватает буфера для вывода результата запроса

Question

Есть такой блок:

declare
    type restype is table of varchar2 (64);
    res restype; 
    tabnam varchar2(200):= '&table_l' ;
    colnum int;
    colnam varchar2 (32) := &column_l;
begin
    select column_name into colnam
    from all_tab_columns
    where owner||'.'||table_name = upper (tabnam)
    and column_id = colnam;
    EXECUTE IMMEDIATE 'select '||colnam||' from '||tabnam bulk collect into res;
    for i in 1..res.count loop
        dbms_output.put_line (res(i)); 
    end loop;
end;

Данный запрос выводит все строки столбца, который задается параметрами (таблица + номер столбца). Проблема заключается в том, что строк слишком много и превышается лимит буфера.

Можно ли каким-нибудь образом обойти данную проблему?
Возможно создание функции на основе данного запроса поможет?

Answer 1

В этом случае создайте пакетную конвейерную функцию. Её можно вызвать SQL запросом и сет результата будет забирать небольшими блоками клиент, который запрос посылает.

Допустим, есть таблица с 1М записей (для переполнения буфера надо больше, но только для примера; на db<>fiddle с 100К записей из-за ограничений сайта):

create table t1 (col1, col2, col3) as 
    select rownum, 'value '||rownum, date '2020-01-01'+rownum
    from dual connect by level<=1e6
/

Пример реализации пакета:

create or replace package pack as 
    type restype is table of varchar2 (64);
    function getColumnValues (tabnam varchar2, colid int) return restype pipelined;
end;    
/
create or replace package body pack as 
    function getColumnValues (tabnam varchar2, colid int) return restype pipelined is
        rc sys_refcursor; 
        colnam varchar2 (32);
        val varchar2 (4000);
    begin
        select column_name into colnam
        from user_tab_columns
        where table_name = upper (tabnam)
        and column_id = colid
        ;
        open rc for 'select '||colnam||' from '||tabnam;
        loop fetch rc into val;
            exit when rc%notfound;
            pipe row (val); 
        end loop;
        close rc;
        return;
    end;
end;
/

Запрос и его результат:

select * from pack.getColumnValues ('t1', 2)
/

Result Sequence
----------------------------------------------------------------
value 1
value 2
value 3
[... все 1.000.000 записей]

Answer 2

В дополненение к ответу выше сделал отдельную piplined функцию, которая в принципе выводит то, что я и хотел:

create or replace type TBL as table of VARCHAR2(200);

CREATE OR REPLACE FUNCTION GetColumn(colnum  NUMBER,tabnam  varchar2) RETURN TBL pipelined IS
    type restype is table of varchar2 (64);
    res restype; 
    colnam varchar2 (32);
BEGIN
    select column_name into colnam
    from all_tab_columns
    where owner||'.'||table_name = upper (tabnam)
    and column_id = colnum;
    EXECUTE IMMEDIATE 'select '||colnam||' from '||tabnam bulk collect into res;
    for i in 1..res.count loop
        pipe row(res(i));
    END LOOP;
END;

Проверить можно на db<>fiddle.

БЛОГ НА HUSL

Не хватает буфера для вывода результата запроса

Ответы (2 шт):