Results 1 to 2 of 2

Thread: SQL Error

  1. #1
    Join Date
    Nov 2011
    Posts
    1

    Unanswered: SQL Error

    Hi,

    ORA-06502: PL/SQL: numeric or value error: character string buffer too small
    ORA-06512: at "FUNCTION", line 32
    06502. 00000 - "PL/SQL: numeric or value error%s"
    *Cause:
    *Action:

    I am getting the above error while trying to run a query which uses a function as a field similar to the below:

    Select <function1>(b.emplid), a.name from table a, table b
    where a.emplid=b.emplid;

    whereas function1 looks like below:

    create or replace
    function function1 (in_emplid in varchar2) return varchar2 as
    cursor cur (in_emplid in varchar2)
    is select a.field
    from table3 a
    where a.emplid = in_emplid
    order by a.emplid;
    topic_descrformal table3.field%type;
    result varchar2(4000);
    begin
    result := null;
    open cur (in_emplid);
    loop
    fetch cur into topic_descrformal;
    exit when cur%notfound;
    if result is not null then
    result := result || '|';
    end if;
    result := result || topic_descrformal;
    end loop;
    close cur;
    return result;
    end;

    This was initially working fine until we fetched data that had a concatenated length of more than 4000. From what I understand, varchar2 should be able to handle up to 32k+ and yet even if we recreate the function with the result variable as varchar2(30000), it would still fail with the same error even for lengths of only 4001.

    Any workaround on this?

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by tamulmol14 View Post
    This was initially working fine until we fetched data that had a concatenated length of more than 4000. From what I understand, varchar2 should be able to handle up to 32k+ and yet even if we recreate the function with the result variable as varchar2(30000), it would still fail with the same error even for lengths of only 4001.
    Not correct in SQL.
    SQL VARCHAR2 type != PL/SQL VARCHAR2 type
    The first can hold only up to 4000 bytes.
    In SQL (SELECT statement) SQL types are used, so...
    Quote Originally Posted by tamulmol14 View Post
    Any workaround on this?
    Either use that function in PL/SQL (e.g. anonymous block) or change the data type to CLOB.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •