Oracle 获取ORA-06502: PL/SQL: 数值或值错误: 字符串缓冲区太小

嗨，新年快乐...

我有一个执行大的大查询的过程... 我将分享我的过程的行为

CREATE OR REPLACE PROCEDURE DUMMY_PROC(DATASET OUT SYS_REFCURSOR,FSID NUMBER) as
STR VARCHAR2(32000);
BEGIN
STR:=' SELECT (SOME COLUMNS FROM SOME MULTIPLE TABLES) WHERE D_ID= '||FSID||' '  

DBMS_OUTPUT.ENABLE(1000000) ;
DBMS_OUTPUT.PUT_LINE ( STR ) ;
OPEN DATASET FOR STR ;
END DUMMY_PROC;

给定的过程具有与我的原始proc相同的功能 (它总共有1245行)
但是当我执行该过程时，出现以下错误

ORA-06502: PL/SQL: 数值或值错误: 字符串缓冲区太小

如果是正确的varchar2数据类型的最大大小是32767。但是我的动态查询超过了这个限制，有什么方法可以做到这一点？

您可以只使用clob，例如

SQL> create table t ( x int );

Table created.

--
-- 500 concatenations less than 32k, so its ok
--
SQL> declare
  2    c varchar2(32700);
  3    rc sys_refcursor;
  4  begin
  5    c := 'select * from t where 1=1';
  6    for i in 1 .. 500
  7    loop
  8      c := c || 'union all select * from t where 1=1';
  9    end loop;
 10    open rc for c;
 11  end;
 12  /

PL/SQL procedure successfully completed.

--
-- 1000 concatenations takes us over the limit
--

SQL> declare
  2    c varchar2(32700);
  3    rc sys_refcursor;
  4  begin
  5    c := 'select * from t where 1=1';
  6    for i in 1 .. 1000
  7    loop
  8      c := c || 'union all select * from t where 1=1';
  9    end loop;
 10    open rc for c;
 11  end;
 12  /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 8

--
-- but a clob is fine
--

SQL> declare
  2    c  clob;
  3    rc sys_refcursor;
  4  begin
  5    c := 'select * from t where 1=1';
  6    for i in 1 .. 1000
  7    loop
  8      c := c || 'union all select * from t where 1=1';
  9    end loop;
 10    open rc for c;
 11  end;
 12  /

PL/SQL procedure successfully completed.

但是我强调 .... 您已经解决了该问题，但是您真的想在数据库中抛出长度超过32k的SQL语句吗？那和一份10页的文件是一样的...