oracle function中如何处理长度超过varchar2存储长度的字符串

比如有如下函数，拼接字符串：

CREATE OR REPLACE Function translate_order_right_user_fun (
activity_instance_id_ in varchar2 default ''
) RETURN varchar2 IS
return_value varchar2(4000):='';


str_length number;
workitem_tmp wfs_workitem%ROWTYPE;
CURSOR mycur is select * from wfs_workitem w where w.activity_instance_id=activity_instance_id_ order by w.assit_order;
BEGIN

open mycur;
LOOP
FETCH mycur INTO workitem_tmp;
EXIT WHEN mycur%NOTFOUND;
return_value:=(return_value||('{WORKITEM_ID:"'||workitem_tmp.WORKITEM_ID|| '",USER_ID:"'||workitem_tmp.user_id ||'",FINISH_FLAG:"'||workitem_tmp.finish_flag|| '",ASSIT_ORDER:"'||workitem_tmp.assit_order||'"},'));
END LOOP;
CLOSE mycur;
SELECT length(return_value) into str_length FROM DUAL;
select substr(return_value,1,str_length-1) into return_value from dual;
RETURN return_value;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error   ---------------' || sqlcode ||   '     :       ' || sqlerrm);
END;

 在数据量小的时候没有问题，但是如果拼接出来的字符串超过了varchar2存储的长度之后，函数就会报错，怎么办，其实在这我们可以用clob类型，直接使用！

CREATE OR REPLACE Function translate_order_right_user_fun (
activity_instance_id_ in varchar2 default ''
) RETURN clob IS
return_value clob:='';


str_length number;
workitem_tmp wfs_workitem%ROWTYPE;
CURSOR mycur is select * from wfs_workitem w where w.activity_instance_id=activity_instance_id_ order by w.assit_order;
BEGIN

open mycur;
LOOP
FETCH mycur INTO workitem_tmp;
EXIT WHEN mycur%NOTFOUND;
return_value:=(return_value||('{WORKITEM_ID:"'||workitem_tmp.WORKITEM_ID|| '",USER_ID:"'||workitem_tmp.user_id ||'",FINISH_FLAG:"'||workitem_tmp.finish_flag|| '",ASSIT_ORDER:"'||workitem_tmp.assit_order||'"},'));
END LOOP;
CLOSE mycur;
SELECT length(return_value) into str_length FROM DUAL;
select substr(return_value,1,str_length-1) into return_value from dual;
RETURN return_value;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error   ---------------' || sqlcode ||   '     :       ' || sqlerrm);
END;

 执行发现没有任何问题了。