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

Question

我正在使用Toad For Oracle，我在一个包中有一个过程，我在那里获得CLOB值的结果。我将只使用虚拟变量，但我将把整个过程放在这里。

我的目标是一个rest web服务，我的输出将是一个JSON CLOB。

正文包的开始

TYPE in_emp_type IS RECORD ( emp_id NUMBER, 
emp_fname VARCHAR2(50), emp_lname VARCHAR2(50), 
city VARCHAR2(40), country(50) )
in_emp_rec in_emp_type ;

TYPE out_emp_type IS RECORD (emp_addr_1 VARCHAR2(100), emp_addr_2 VARCHAR2(100); 
TYPE out_emp_tab_type IS TABLE OF out_emp_type;
query_tab out_emp_tab_type  := out_emp_tab_type ();
PROCEDURE get_employee_details(api_key VARCHAR2, emp_id NUMBER, 
emp_fname VARCHAR2, emp_lname VARCHAR2, out_result_json OUT CLOB ) IS
CURSOR get_all_emp IS
select city, country from emp_table 
where emp_id = emp_id 
and fname = emp_fname 
and lname = emp_lname ; 

CURSOR get_emp_addr (emp_id NUMBER, city VARCHAR2) IS
select addr_1, addr_2 from emp_addr 
where emp_id = emp_id and city = city;
v_city VARCHAR2;
v_country VARCHAR2;
v_json_input CLOB;
v_result_json CLOB;
BEGIN 
open get_all_emp;
fetch get_all_emp  into v_city , v_country ;
close get_all_emp;
v_json_input := '{"EmployeeDetails": 
{"EmployeeID": || emp_id  || ' ",
"EmployeeFirstName": || emp_fname || '",
"EmployeeLastName": || emp_lname || '",
"EmployeeCity": || v_city || '",
"EmployeeCountry": || v_country || '"}'
convert_input_json(v_json_input,in_emp_rec);

open get_emp_addr (emp_id, city);
fetch get_emp_addr bulk collect into query_tab ;

v_result_json := convert_result_json(query_tab);
out_result_json := v_result_json;
END;

这是我的convert_input_json过程的示例&convert_result_json函数

PROCEDURE convert_input_json(input_json IN CLOB, output_json OUT in_emp_type) IS 
CURSOR get_details IS 
select emp_id, emp_fname, emp_lname, city, country
from json_table(input_json, '$' COLUMNS (
emp_id NUMBER PATH '$.EmployeeDetails.EmployeeID',
emp_fname VARCHAR2(50) PATH '$.EmployeeDetails.EmployeeFirstName',
emp_lname VARCHAR2(50) PATH '$.EmployeeDetails.EmployeeLastName', ... includeother fields here)
BEGIN 
output_json := in_emp_type( emp_id  => NULL, emp_fname  => NULL....)
open get_details;
fetch get_details into output_json.emp_id  , output_json.emp_fname ....);
close get_details;
END;

FUNCTION convert_result_json (in_result out_emp_tab_type) RETURN CLOB IS
v_clob CLOB
BEGIN
FOR i in 1...query_tab.count LOOP
v_clob := '{ '||chr(13)||'"customerResults": {'
v_clob := v_clob||chr(13)||chr(9)||chr(9)||'"addr1": "'||query_tab(i).emp_addr_1 ||'",';
v_clob := v_clob||chr(13)||chr(9)||chr(9)||'"addr2": "'||query_tab(i).emp_addr_2 ||'",';
END LOOP;
RETURN (v_clob)
END;

所以当我尝试运行这个，我得到ORA-06502: PL/SQL:数字或值错误:字符串缓冲区太小

我真的不知道为什么我得到这个错误。谁能帮帮我?

Answer 1

应该有一个错误堆栈跟踪，显示引发异常的行号。查看您的代码，有两个明显的地方可能发生这种情况:

1. JSON_TABLE COLUMNS映射-确保emp_fname/emp_lname永远不会溢出分配的50个字符。

2. 在convert_result_json函数中，您使用||将新地址附加到现有CLOB值。||将操作数转换为varchar2(32767)。如果CLOB的大小+被附加的新字符串>32KB，由于varchar2数据类型限制，它将失败。如果这是您的问题，请使用dbms_lob附加到lob，而不是||。