Dear Gurus,

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

On Jun 7, 10:44*am, Sanjeev <sanjeev.atvan... (AT) gmail (DOT) com> wrote:
Answered in another newsgroup. PLEASE do not cross=post as the same
group of people monitors all of the comp.databases.oracle newsgroups.


David Fitzjarrell