CODEWITHSUNDEEP
oracle-databaseplsqltoadclob
xstitch
xstitch

Reputation: 13

CLOB - ORA-06502: PL/SQL: numeric or value error: character string buffer too small

I am using Toad For Oracle and I have a procedure in a package where I'm getting the CLOB value for result. I'll be using dummy variables only but I'll be putting the whole process here.

My goal is to a rest web service where my output will be a JSON CLOB.

START OF THE BODY PACKAGE

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;

And this is my sample of the convert_input_json procedures & convert_result_json function

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;

So when I try to run this, I'm getting the ORA-06502: PL/SQL: numeric or value error: character string buffer too small

And I'm not really sure why am I getting this error. Can someone please help me.

Upvotes: 0

Views: 1584

Answers (1)

Paul W
Paul W

Reputation: 11264

There should be an error stack trace showing the line number that raised the exception. Looking at your code, there are two obvious places where this can happen:

  1. The JSON_TABLE COLUMNS mapping - ensure that emp_fname/emp_lname never overflows the allotted 50 characters.

  2. In the convert_result_json function, you are using || to append new addresses to an existing CLOB value. || will convert the operands to varchar2(32767). If the size of the CLOB + the new string being appended > 32KB, it will fail due to the varchar2 datatype limit. If this is your issue, use dbms_lob to append to your LOBs, not ||.

Upvotes: 0

Related Questions