VARCHAR2(32767)无法处理存储过程中的字符串

Question

我正在使用游标连接字符串(以形成稍后执行的查询(。在这里，将形成的查询将比VARCHAR2(32767(所能处理的查询大得多。因此，我在进程执行中遇到错误-ORA-06502:PL/SQL:数字或值错误：字符串缓冲区太小。

我也使用了CLOB数据类型，但得到了错误ORA-06502:PL/SQL:numeric或value错误。

我的代码在下面：

CREATE OR REPLACE PROCEDURE sp_Market
IS
Names VARCHAR2(32767);    
BEGIN
DECLARE CURSOR cur IS  ('Select ID, Order_of, field_name  
FROM   pld_medicare_config');
BEGIN       
FOR i IN cur
LOOP
Names := Names ||  i.sqql;
END LOOP;    
dbms_output.put_line(Names);
END;   
END sp_Market;

我如何处理我的字符串查询，有什么数据类型可以完成任务？

Answer 1

CLOB正常(据我所知(；我怀疑您存储的查询是否有大的。

从过程中删除dbms_output.put_line调用；我怀疑是它引起了这个错误。

Answer 2

我不确定您是如何得到任何运行时错误的，因为您的过程不会编译。

有效的PL/SQL版本如下所示：

create or replace procedure sp_market is
names varchar2(32767);
begin
for r in (
select id, order_of, field_name
from   pld_medicare_config
)
loop
names := names || ' ' || r.field_name;
end loop;
names := ltrim(names);
dbms_output.put_line(names);
end sp_market;

如果names需要更长，请将数据类型更改为clob。

Answer 3

使用CLOB数据类型并使用dbms_lob.writeappend过程附加数据。这是参考(Oracle 18c(。

错误可能源于dbms_output.put_line调用。该过程仅为varchar2参数定义，这意味着在调用过程中会发生隐式转换。对于长度超过32767个字符/字节的clob内容，它将失败。

或者，您可以在varchar2(4000)上声明集合，并按顺序填充集合元素：

CREATE OR REPLACE PROCEDURE sp_Market
IS
TYPE tLongString IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
cNames  tLongString;    
BEGIN
DECLARE CURSOR cur IS Select ID, Order_of, field_name, sqql FROM pld_medicare_config;
BEGIN       
FOR i IN cur
LOOP
cNames(cNames.COUNT+1) := i.sqql;
END LOOP;    
END;   
END sp_Market;

注意

已更正代码，现在将编译。