1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

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

Discussion in 'SQL PL/SQL' started by laxman, May 27, 2010.

  1. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    Hi all,
    i am facing one ora-error while executing the procedure.
    ORA-06512: at "Lax.CLOSE_DUP"
    ORA-06502: PL/SQL: numeric or value error: character string buffer too small

    i am suspecting it could be length issue with some of variables i have declared inside procedure
    l_effectreqcount number;----> This is tracking no of records being fetched.
    l_postdata varchar2(999); ---> This is the variable for which i believe the error has come.
    The reason is that in this variable l_postdata i am concatenating all the data being fetched from cursor.it is something like this

    Code (SQL):
    CREATE OR REPLACE PROCEDURE close_dup IS
    l_effectreqcount NUMBER(5);
    l_postdata varchar2(999) ---- can i increase the size here to 32000 since every day we need to concatenate fetched reqid to this variable and everyday we are getting approx 20000 or more reqid.
    BEGIN
       l_effectreqcount:= 0;
        l_postdata:='';
    BEGIN
    cursor close_dup_rec IS (SELECT reqid FROM a)
    loop
    l_effectreqcount := l_effectreqcount + 1 ;

                        IF(IN_GenReqID) THEN
                            IF(l_effectreqcount = 1) THEN
                                l_postdata:= l_postdata || close_dup_rec.reqid ;
                                                           dbms_output.put_line('l_postdata');
                             ELSE
                                l_postdata:= l_postdata||','|| close_dup_rec.reqid ;
                                                            dbms_output.put_line('l_postdata');
                             END IF;
                                                 
                        END IF;
    END loop;
    END;
     
    Need ur help please.

    Regards
    Laxman
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Within PL/SQL a varchar2 can be as long as 32767; I would declare the maximum length as it's not allocated until it's used.
     
  3. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    Thanks a lot sir for ur responce.

    Regards
    Laxman
     
  4. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    this problem can be resolved using anchored datatype like %type.

    syntax :- tablename.colname%type.

    ex: emp.empno%type.

    then there is no need increasing the size every day
     
  5. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    You cant use %TYpe as the process concatenates all reqid. It will not be useful.

    Also making it VARCHAR2(32767) is alos not worthwhile, as you gets approx 20000 or more reqid /day. It will be exhausted soon.

    Obviuosly , you have to change the related process (that processes l_postdata ) .
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I agree the process needs to be changed. A CLOB won't work, however, as it will cause problems with dbms_output.put_line:

    Code (SQL):
     
    SQL> DECLARE
    2 v_stuff CLOB;
    3 BEGIN
    4 FOR crec IN (SELECT * FROM clobopt) loop
    5 v_stuff := v_stuff || crec.stuff;
    6 END loop;
    7
    8 dbms_output.put_line(v_ztuff);
    9
    10 END;
    11 /
    DECLARE
    *
    ERROR at line 1:
    ORA-06502: PL/SQL: NUMERIC OR VALUE error
    ORA-06512: at line 8
     
    SQL>
     
     
    Collections might be the best way to handle this, using bulk collect and forall.
     
  7. Abhijeet Bhatiya

    Abhijeet Bhatiya Starter

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    Ahmedabad
    You should check variable name in "dbms_output.put_line".
     
  8. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.
    May be helpful : string-aggregation-techniques
    for example:
    Code (SQL):
    WITH q AS
    (
          SELECT 1 grp, 'elem1' name  FROM dual
          UNION ALL
          SELECT 2 , 'elem2'  FROM dual
          UNION ALL
          SELECT 2, 'elem3'  FROM dual
          UNION ALL
          SELECT 1 , 'elem4'  FROM dual
          UNION ALL
          SELECT 1 , 'elem5'  FROM dual
          UNION ALL
          SELECT 2 , 'elem6'  FROM dual
    )
    SELECT   grp,
             rtrim(xmlagg (xmlelement(e, name||'; ') ORDER BY name).EXTRACT('//text()'),';') AS str
    FROM q
    GROUP BY grp;
     
    Last edited: Nov 22, 2016