oracle 过程无法返回大字符串响应(~293个字符)

2exbekwf 于 2023-10-16 发布在 Oracle

关注(0)|答案(1)|浏览(100)

下面是我的存储过程

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;

预期响应

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

实际响应为空

但是，如果响应值的长度很小，则recovery返回正确的响应例如

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

这似乎是最大varchar2大小的限制。请提出如何解决。

oracle

来源：https://stackoverflow.com/questions/77290124/procedure-cant-return-large-string-response293-characters

1条答案

按热度按时间

ljo96ir51#

您的程序可以简化为：

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;
/

假设你的另一个函数是：

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;
/

然后又道：

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

输出：

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

但是，不清楚为什么使用NVARCHAR2而不是VARCHAR2，就像所有其他变量一样。*
当Oracle 12及更高版本支持生成JSON时，手动生成JSON似乎也是错误的。*

fiddle

赞(0）回复(0）举报 2023-10-16

我来回答

oracle 过程无法返回大字符串响应(~293个字符)

1条答案

相关问题

热门标签

最新问答