Procedure can't return large string response(~293 characters)

Question

Below is my stored procedure

create or replace PROCEDURE PR_GETECOUNT  
( P_YEAR IN NUMBER
,P_RECORDSET OUT  NVARCHAR2
) AS 
l_r_count varchar2(400);
l_v_count varchar2(400);
l_e_count varchar2(400);
l_p_count varchar2(400);
l_s_count varchar2(400);
l_n_count varchar2(400);
l_e_count_upadted  NVARCHAR2(4000);
BEGIN

l_r_count:=FN_GETECOUNT( 'R',P_YEAR);
l_v_count:=FN_GETECOUNT( 'V',P_YEAR);
l_e_count:=FN_GETECOUNT( 'E',P_YEAR);
l_p_count:=FN_GETECOUNT( 'P',P_YEAR);
l_s_count:=FN_GETECOUNT( 'S',P_YEAR);
l_n_count:=FN_GETECOUNT( 'N',P_YEAR);

--the values returned by FN_GETECOUNT are as below
l_r_count:='{"R":[375,127,136,650,130,169,009,015,094,027]}';
l_v_count:='{"V":[375,127,136,650,130,169,009,015,094,027]}';
l_e_count:='{"E":[375,127,136,650,130,169,009,015,094,027]}';
l_p_count:='{"P":[375,127,136,650,130,169,009,015,094,027]}';
l_s_count:='{"S":[375,127,136,650,130,169,009,015,094,027]}';
l_n_count:='{"N":[375,127,136,650,130,169,009,015,094,027]}';

--preparing response
l_e_count_upadted:='['||l_r_count||','||l_v_count||','||l_e_count||','||l_p_count||','||l_s_count||','||l_n_count||']' ;

  select l_e_count_upadted INTO P_RECORDSET FROM dual;
  

END PR_GETECOUNT;

expected response

  [{"R":[375,127,136,650,130,169,009,015,094,027]},{"V":[375,127,136,650,130,169,009,015,094,027]},{"E":[375,127,136,650,130,169,009,015,094,027]},{"P":[375,127,136,650,130,169,009,015,094,027]},{"S":[375,127,136,650,130,169,009,015,094,027]},{"N":[375,127,136,650,130,169,009,015,094,027]}]

actual response is empty

However if the length of response values is small then recordset returns correct response e.g

[{"R":[0,0,0,0,1,0,0,0,0,0]},{"V":[0,0,0,0,0,0,0,0,0,1]},{"E":[0,0,0,0,0,0,0,0,0,0]},{"P":[0,0,0,0,0,0,0,0,0,0]},{"S":[4,7,2,4,12,2,3,3,9,0]},{"N":[17,3,23,4,44,55,5,2,1,0]}]

This seems to be limitation of max varchar2 size. please suggest how can it be solved.

Answer 1

Your procedure can be simplified to:

CREATE PROCEDURE PR_GETECOUNT(
  P_YEAR      IN  NUMBER
, P_RECORDSET OUT NVARCHAR2
) AS 
BEGIN
  P_RECORDSET := '['
              || FN_GETECOUNT( 'R',P_YEAR) || ','
              || FN_GETECOUNT( 'V',P_YEAR) || ','
              || FN_GETECOUNT( 'E',P_YEAR) || ','
              || FN_GETECOUNT( 'P',P_YEAR) || ','
              || FN_GETECOUNT( 'S',P_YEAR) || ','
              || FN_GETECOUNT( 'N',P_YEAR) || ']';
END PR_GETECOUNT;
/

Then assuming your other function is:

CREATE FUNCTION FN_GETECOUNT(
  p_VALUE IN VARCHAR2,
  p_year  IN NUMBER
) RETURN VARCHAR2
IS
BEGIN
  RETURN '{"' || p_value || '":[375,127,136,650,130,169,009,015,094,027]}';
END;
/

Then:

DECLARE
  p_recordset NVARCHAR2(400);
BEGIN
  PR_GETECOUNT(42, p_recordset);
  DBMS_OUTPUT.PUT_LINE(p_recordset);
END;
/

Outputs:

[{"R":[375,127,136,650,130,169,009,015,094,027]},{"V":[375,127,136,650,130,169,009,015,094,027]},{"E":[375,127,136,650,130,169,009,015,094,027]},{"P":[375,127,136,650,130,169,009,015,094,027]},{"S":[375,127,136,650,130,169,009,015,094,027]},{"N":[375,127,136,650,130,169,009,015,094,027]}]

However, it is unclear why you use NVARCHAR2 and not VARCHAR2 like all the other variables.

It also seems wrong to be manually generating JSON when Oracle 12 and later supports generating JSON.

fiddle