ORDS, SODA & JSON in the Database

executing the following restful api gives me an error Status: 400 Bad Request Content-type: application/json; charset=UTF-8 {"status":400,"message":"ORA-06502: PL/SQL: numeric or value error: character string buffer too small"}
The plsql code is

DECLARE
l_cursor SYS_REFCURSOR;
l_date DATE;
fcnt_breakfast NUMBER := 0;
fcnt_lunch NUMBER := 0;
fcnt_dinner NUMBER := 0;
fcnt_extra NUMBER := 0;

-- make these roomy to avoid buffer issues
last_payroll VARCHAR2(4000) := '';
current_payroll VARCHAR2(4000) := '';
count_rec NUMBER := 0;

TYPE emp_detail_type IS RECORD (
payroll Daily_Trans.G_payroll%TYPE,
emp_type Daily_Trans.type%TYPE,
-- name Daily_Master.Name%TYPE,
name varchar2(4000),
meal_kind VARCHAR2(1),
meal_type VARCHAR2(3)
);
emp_detail_rec emp_detail_type;

TYPE emp_obj IS RECORD (
cpayroll Daily_Trans.G_payroll%TYPE,
cemp_type Daily_Trans.type%TYPE,
-- cname Daily_Master.Name%TYPE,
cname varchar2(4000),
cnt_breakfast NUMBER,
cnt_lunch NUMBER,
cnt_dinner NUMBER,
cnt_extra NUMBER
);
prev_record emp_obj;
BEGIN
l_date := TO_DATE(:tdate,'yyyy-MM-dd');

-- open cursor
IF UPPER(NVL(:mealtype,'ALL')) <> 'ALL' THEN
OPEN l_cursor FOR
SELECT m.g_payroll, m.type, m.name, t.meal_kind, t.meal_type
FROM daily_master m
JOIN daily_trans t
ON t.g_payroll = m.g_payroll
AND t.type = m.type
AND t.meal_date = m.meal_date
WHERE m.type = NVL(:emptype,'G')
AND TO_CHAR(m.meal_date,'yyyy-MM-dd') = NVL(:tdate,'2025-04-27')
AND t.meal_type = NVL(:mealtype,'HOT')
ORDER BY m.g_payroll, m.type;
ELSE
OPEN l_cursor FOR
SELECT cast(m.g_payroll as varchar2(100) ) g_payroll , m.type, cast(m.name as varchar2(4000) ) name , t.meal_kind, t.meal_type
FROM daily_master m
JOIN daily_trans t
ON t.g_payroll = m.g_payroll
AND t.type = m.type
AND t.meal_date = m.meal_date
WHERE m.type = NVL(:emptype,'G')
AND TO_CHAR(m.meal_date,'yyyy-MM-dd') = NVL(:tdate,'2025-04-27')
ORDER BY m.g_payroll, m.type;
END IF;

-- STREAMING: initialize output but do NOT auto-emit headers
APEX_JSON.initialize_output(p_http_header => FALSE);

-- Emit headers once (status + mime)
OWA_UTIL.status_line(200, 'OK', TRUE);
OWA_UTIL.mime_header('application/json; charset=UTF-8', FALSE);
OWA_UTIL.http_header_close;

-- Start JSON stream
APEX_JSON.open_object;
APEX_JSON.open_array('items');

LOOP
FETCH l_cursor INTO emp_detail_rec;
EXIT WHEN l_cursor%NOTFOUND;

current_payroll := emp_detail_rec.emp_type || emp_detail_rec.payroll;

fcnt_breakfast := CASE WHEN emp_detail_rec.meal_kind = 'B' THEN 1 ELSE 0 END;
fcnt_lunch := CASE WHEN emp_detail_rec.meal_kind = 'L' THEN 1 ELSE 0 END;
fcnt_dinner := CASE WHEN emp_detail_rec.meal_kind = 'D' THEN 1 ELSE 0 END;
fcnt_extra := CASE WHEN emp_detail_rec.meal_kind = 'T' THEN 1 ELSE 0 END;

IF count_rec = 0 THEN
-- seed accumulator
prev_record.cpayroll := emp_detail_rec.payroll;
prev_record.cemp_type := emp_detail_rec.emp_type;
prev_record.cname := emp_detail_rec.name;
prev_record.cnt_breakfast := fcnt_breakfast;
prev_record.cnt_lunch := fcnt_lunch;
prev_record.cnt_dinner := fcnt_dinner;
prev_record.cnt_extra := fcnt_extra;
last_payroll := current_payroll;
ELSE
IF current_payroll <> last_payroll THEN
-- flush previous object directly to response
APEX_JSON.open_object;
APEX_JSON.write('payroll', prev_record.cpayroll);
APEX_JSON.write('name', prev_record.cname); -- safe: varchar2
APEX_JSON.write('cnt_breakfast', prev_record.cnt_breakfast);
APEX_JSON.write('cnt_lunch', prev_record.cnt_lunch);
APEX_JSON.write('cnt_dinner', prev_record.cnt_dinner);
APEX_JSON.write('cnt_extra', prev_record.cnt_extra);
APEX_JSON.close_object;

   -- start new accumulator  
   prev\_record.cpayroll      := emp\_detail\_rec.payroll;  
   prev\_record.cemp\_type     := emp\_detail\_rec.emp\_type;  
   prev\_record.cname         := emp\_detail\_rec.name;  
   prev\_record.cnt\_breakfast := fcnt\_breakfast;  
   prev\_record.cnt\_lunch     := fcnt\_lunch;  
   prev\_record.cnt\_dinner    := fcnt\_dinner;  
   prev\_record.cnt\_extra     := fcnt\_extra;  
   last\_payroll              := current\_payroll;  
 ELSE  
   -- accumulate  
   prev\_record.cnt\_breakfast := prev\_record.cnt\_breakfast + fcnt\_breakfast;  
   prev\_record.cnt\_lunch     := prev\_record.cnt\_lunch     + fcnt\_lunch;  
   prev\_record.cnt\_dinner    := prev\_record.cnt\_dinner    + fcnt\_dinner;  
   prev\_record.cnt\_extra     := prev\_record.cnt\_extra     + fcnt\_extra;  
 END IF;  

END IF;

count_rec := count_rec + 1;
END LOOP;

CLOSE l_cursor;

IF count_rec > 0 THEN
-- flush last accumulated object
APEX_JSON.open_object;
APEX_JSON.write('payroll', prev_record.cpayroll);
APEX_JSON.write('name', prev_record.cname);
APEX_JSON.write('cnt_breakfast', prev_record.cnt_breakfast);
APEX_JSON.write('cnt_lunch', prev_record.cnt_lunch);
APEX_JSON.write('cnt_dinner', prev_record.cnt_dinner);
APEX_JSON.write('cnt_extra', prev_record.cnt_extra);
APEX_JSON.close_object;
END IF;

APEX_JSON.close_array; -- items
APEX_JSON.close_object; -- root

-- finish streaming and free internal buffers
APEX_JSON.free_output;

:status_code := 200;
EXCEPTION
WHEN OTHERS THEN
-- If an error occurs, send a minimal JSON error (headers might already have been sent)
-- Try to set status code and return a simple error body.

 OWA\_UTIL.status\_line(400, 'Bad Request', TRUE);  
 OWA\_UTIL.mime\_header('application/json; charset=UTF-8', FALSE);  
 OWA\_UTIL.http\_header\_close;  

HTP.p('{"status":400,"message":"' || REPLACE(SQLERRM, '"', '\"') || '"}');
:status_code := 400;
END;