CREATE OR REPLACE FUNCTION getChildDeparId(departId IN VARCHAR2) RETURN VARCHAR2
AS
type row_sql is ref cursor;
row row_sql;
tab T_DEPART_INFO%rowtype;
departIds varchar2(32767);
BEGIN
open row for 'select * from T_DEPART_INFO where CHARGE_ID=:1'
using departId;
loop
fetch row into tab;
exit when row%notfound;
departIds:=departIds||tab.depart_id||','||getChildDeparId(tab.depart_id);
end loop;
close row;
dbms_output.put_line(length(departIds));
return departIds;
END getChildDeparId;返回字符串大于4000 就报错字符缓冲区太小,怎么解决?
AS
type row_sql is ref cursor;
row row_sql;
tab T_DEPART_INFO%rowtype;
departIds varchar2(32767);
BEGIN
open row for 'select * from T_DEPART_INFO where CHARGE_ID=:1'
using departId;
loop
fetch row into tab;
exit when row%notfound;
departIds:=departIds||tab.depart_id||','||getChildDeparId(tab.depart_id);
end loop;
close row;
dbms_output.put_line(length(departIds));
return departIds;
END getChildDeparId;返回字符串大于4000 就报错字符缓冲区太小,怎么解决?
但不推荐最好使用LOB
把你现在函数返回得值写入到这个共公那个变量中。CREATE OR REPLACE PACKAGE DOER.DRDR
IS
TMPVAR VARCHAR2(30000);
PROCEDURE A;
PROCEDURE B;
END;CREATE OR REPLACE PACKAGE BODY DOER.DRDR
IS
PROCEDURE A IS
I NUMBER;
BEGIN
I := 500;
TMPVAR := '1234567890';
LOOP
TMPVAR := TMPVAR || '1234567890';
EXIT WHEN I = 0;
I := I -1;
END LOOP;
END;
--接收--
PROCEDURE B IS
I NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE(LENGTH(TMPVAR));
END;
END;