ORA-06502: PL/SQL: numeric or value error: character string buffer too small

I have written below function

FUNCTION Get_Loc_Dtl
(
No IN VARCHAR2,
Date_Cond IN VARCHAR2
)

RETURN VARCHAR2
is
l_Start_Date DATE;
l_End_Date DATE;

l_Location_Detail VARCHAR2(32767) := NULL; BEGIN l_Start_Date := TO_DATE(Date_Cond||' '||'00:00:00','DD-MON-YYYY HH24:MI:SS');
l_End_Date := TO_DATE(Date_Cond||' '||'23:59:59','DD-MON-YYYY HH24:MI:SS');

FOR CUR_REC IN (
SELECT TO_CHAR(STARTTIME,'HH24:MI:SS')||' '||No||' '||LOCATION as LOCATIONDETAIL
FROM Loc
WHERE SUB_No = No
AND STARTTIME BETWEEN l_Start_Date AND l_End_Date ORDER BY STARTTIME
)

LOOP
l_Location_Detail := l_Location_Detail || '##' || CUR_REC.LOCATIONDETAIL;
END LOOP;
DBMS_OUTPUT.PUT_LINE('length : '||l_Location_Detail); RETURN LTRIM(l_Location_Detail, '##');
END Get_Loc_Dtl;

My Actual need is to insert location value into a table i.e. command as below

Table LocationDetail having below columns

LocId Number(10),
LocDetail VARCHAR2(4000),

insert into LocationDetail
Select 1 as LocId,
Get_Loc_Dtl('9833225665','05-Jan-2010') as LocDetail from dual;

So, Is there any SQL datatype which will allow more than 4000 character so as to change data type of column 'LocDetail' in LocationDetail table.

but when length of LOCATIONDETAIL goes beyond 4000 it is giving below error
ORA-06502: PL/SQL: numeric or value error: character string buffer too small

Could any one suggest me a solution for above same?

Thanking in Advance
Sanjeev Received on Mon Jun 07 2010 - 09:43:39 CDT