主页 / 问题 / 1283710
Accepted
Alex Konkin
Asked:2022-05-19 19:37:55 +0000 UTC2022-05-19 19:37:55 +0000 UTC

没有足够的缓冲区来显示查询结果

  • 772

有这个块：

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;

此查询返回由参数（表 + 列号）指定的列的所有行。问题是行数过多，超出了缓冲区限制。

有没有办法解决这个问题？
也许根据这个请求创建一个函数会有所帮助？

sql

2 个回答

  1. Best Answer

    在这种情况下，创建一个批处理流水线函数。它可以通过 SQL 查询调用，结果集将由发送查询的客户端以小块的形式获取。

    假设有一个包含 1M 记录的表（缓冲区溢出需要更多，但仅作为示例；由于站点限制，在db<>fiddle上有 100K 记录）：

    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 записей]
    • 5

  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。

    • 2

相关问题