存储过程无法返回长字符串响应的解决方案咨询

存储过程PR_GETECOUNT在返回较长JSON字符串时响应为空，短JSON则正常返回，推测是字符类型长度限制导致。

原存储过程代码 #

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;

现象 #

• 预期返回的长JSON：

[{"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]}]

• 实际返回为空；但短JSON（如下）能正常返回：

[{"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]}]

问题分析 #

原代码中l_e_count_upadted定义为NVARCHAR2(4000)，这是Oracle中NVARCHAR2的最大字符长度限制。当拼接后的长JSON字符串长度超过4000字符时，会触发静默截断或赋值失败，最终导致输出参数P_RECORDSET为空。

解决办法 #

方法1：改用CLOB类型存储长字符串 #

CLOB支持最大4GB的存储容量，完全满足长JSON的需求。修改存储过程的参数和中间变量类型：

create or replace PROCEDURE PR_GETECOUNT  
( P_YEAR IN NUMBER
,P_RECORDSET OUT  CLOB
) AS 
l_r_count CLOB;
l_v_count CLOB;
l_e_count CLOB;
l_p_count CLOB;
l_s_count CLOB;
l_n_count CLOB;
l_e_count_upadted  CLOB;
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);

-- 测试用赋值（实际保留原函数调用即可）
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]}';

-- 拼接CLOB字符串
l_e_count_upadted := '[' || l_r_count || ',' || l_v_count || ',' || l_e_count || ',' || l_p_count || ',' || l_s_count || ',' || l_n_count || ']';

P_RECORDSET := l_e_count_upadted;

END PR_GETECOUNT;

注意：如果FN_GETECOUNT返回的是VARCHAR2，可以直接赋值给CLOB变量，Oracle会自动转换。

方法2：使用Oracle原生JSON函数构建JSON #

Oracle 12c及以上版本支持JSON_ARRAY、JSON_OBJECT等原生函数，能更规范地构建JSON，同时避免手动拼接的长度问题：

create or replace PROCEDURE PR_GETECOUNT  
( P_YEAR IN NUMBER
,P_RECORDSET OUT  CLOB
) AS 
l_r_json CLOB;
l_v_json CLOB;
l_e_json CLOB;
l_p_json CLOB;
l_s_json CLOB;
l_n_json CLOB;
BEGIN

-- 假设FN_GETECOUNT返回的是数组字符串（如'[375,127,...]'），直接用JSON_OBJECT构建
l_r_json := JSON_OBJECT('R' VALUE FN_GETECOUNT('R', P_YEAR) FORMAT JSON);
l_v_json := JSON_OBJECT('V' VALUE FN_GETECOUNT('V', P_YEAR) FORMAT JSON);
l_e_json := JSON_OBJECT('E' VALUE FN_GETECOUNT('E', P_YEAR) FORMAT JSON);
l_p_json := JSON_OBJECT('P' VALUE FN_GETECOUNT('P', P_YEAR) FORMAT JSON);
l_s_json := JSON_OBJECT('S' VALUE FN_GETECOUNT('S', P_YEAR) FORMAT JSON);
l_n_json := JSON_OBJECT('N' VALUE FN_GETECOUNT('N', P_YEAR) FORMAT JSON);

-- 用JSON_ARRAY组合成最终JSON数组
SELECT JSON_ARRAY(l_r_json, l_v_json, l_e_json, l_p_json, l_s_json, l_n_json FORMAT JSON)
INTO P_RECORDSET
FROM dual;

END PR_GETECOUNT;

这种方式不仅避免了长度限制，还能保证JSON格式的正确性，减少手动拼接的错误。

内容的提问来源于stack exchange，提问作者sanjeevRm