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

From: Steve Howard <stevedhoward_at_gmail.com>
Date: Mon, 7 Jun 2010 19:02:46 -0700 (PDT)
Message-ID: <7a2b8081-ebac-4b02-9ff3-6083a75c7018_at_y11g2000yqm.googlegroups.com>

On Jun 7, 10:43 am, Sanjeev <sanjeev.atvan..._at_gmail.com> wrote:
> 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

I know this isn't an answer to your question, but this looks like a really questionable design.
You want to insert into a single column a concatenated list of values from another column? Received on Mon Jun 07 2010 - 21:02:46 CDT