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

From: ddf <oratune_at_msn.com>
Date: Mon, 7 Jun 2010 15:56:49 -0700 (PDT)
Message-ID: <77e97085-b9a3-4ca0-817c-0587b8716c30_at_a39g2000prb.googlegroups.com>

On Jun 7, 10:44 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

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 Received on Mon Jun 07 2010 - 17:56:49 CDT