FUNCTION returning more than 4000 characters
613347Apr 22 2011 — edited Apr 22 2011I am calling the below function from a view, and function is failing because it returning more than 4000chars.
An I know when the return type is varchar2 it cannot return more than 4000 chars.
It's working fine when I use CLOB for the return type. Is there any other better option to handle this err.
CREATE OR REPLACE FUNCTION FN_CONCATENATE_COMMENTS (
P_CASENO IN PATIENT_DET.CASE_NO%TYPE,
P_POLICY IN PATIENT_DET.POLICYNO%TYPE
)
RETURN VARCHAR2
AS
V_TEMP VARCHAR2(300);
V_COMMENT VARCHAR2(30000);
CURSOR FETCHCOMMENT IS
SELECT TO_CHAR (C.CREATED_TS,'mm/dd/yyyy hh:mi:ss AM - ')|| C.DOC_COMMENT_DESC DOC_COMMENT_DESC
FROM TREATEMENT_DET C
WHERE C.CASE_CD = P_CASENO AND C.POLICY_N0 = P_POLICY
ORDER BY C.CREATED_TS ;
BEGIN
OPEN FETCHCOMMENT;
LOOP
FETCH FETCHCOMMENT INTO V_TEMP;
EXIT WHEN FETCHCOMMENT%NOTFOUND;
V_COMMENT := V_COMMENT ||' '|| TRIM(V_TEMP) ||' ';
END LOOP;
CLOSE FETCHCOMMENT;
RETURN V_COMMENT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE('SYSTEM ERROR PLEASE CONTACT DBA ' || SQLERRM);
END FN_CONCATENATE_COMMENTS;