Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

FUNCTION returning more than 4000 characters

613347Apr 22 2011 — edited Apr 22 2011
I 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;
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 20 2011
Added on Apr 22 2011
5 comments
2,081 views