How to solve PLSQL ORA-06502

Question

I have a small function which uses a dynamic sql. I also need the dynamic sql, because I get the Viewname and the Where_clause during the runtime of my stored procedure.

create or replace FUNCTION Costs_MK(VIEWNAME     IN VARCHAR2,
                                    WHERE_Clause IN VARCHAR2)
RETURN VARCHAR2
IS
  v_Costs VARCHAR2(3000);
BEGIN
  EXECUTE IMMEDIATE 'Select Listagg(Costs, ' || '''' || ';' || '''' || ' )
                     WITHIN GROUP (ORDER BY Costs)
                       from (select distinct (Costs)
                       from ' || Viewname || ' where ' || where_Clause || ')'
  INTO v_Costs;

  dbms_output.put_line(length(v_Costs));

  RETURN v_Costs;
END Costs_MK;

Output:

ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: in Line 9
1600

The length is 1600 of the variable v_Costs and I already set the length to 3000. However I am getting this error everytime and I dont know what I can do, to solve this one.

Line 9 is:

Line 8: BEGIN
Line 9:
Line 10:   EXECUTE IMMEDIATE

Anonymous block:

DECLARE
  VIEWNAME VARCHAR2(200);
  WHERE_Clause VARCHAR2(200);
  v_Return VARCHAR2(200);
BEGIN
  VIEWNAME := 'Orders';
  WHERE_BEDINGUNG := 'Orders.key like ' || '''' || '  B01  230/01/123456%' || '''';

  v_Return := Costs_MK(
    VIEWNAME => VIEWNAME,
    WHERE_Clause => WHERE_Clause
  );

  /* Legacy output:
     DBMS_OUTPUT.PUT_LINE('v_Return = ' || v_Return);
   */
  :v_Return := v_Return;

  --rollback;
END;

And now I found the error. Because v_Return VARCHAR2(200) is only 200. If I raise it for example to 4000, I get the desired result. Ok, so I guess, I know how to solve this.

Answer 1

You are seeing the output from

dbms_output.put_line(length(v_Costs));

... so your function is reaching that point, and therefore is not throwing an exception on line 9 (which is an empty line) or anywhere else before that output is generated.

Therefore it must be the caller that is getting the exception; either the procedure that calls the function, or the anonymous block that calls the procedure. But not the function itself.

Based on the exception stack shown it's coming from the anonymous block, since neither the procedure or function names are in that stack (though it's feasible you've left the procedure name out). Line 9 of the anonymous block must be assigning a value that is too long for a variable, but it's nothing to do with the function, unless you're appending the returned number to a string and it is that which is then too long.

Your anonymous block is doing this:

DECLARE
  ...
  v_Return VARCHAR2(200);
BEGIN
  ...
  v_Return := Costs_MK(
    VIEWNAME => VIEWNAME,
    WHERE_Clause => WHERE_Clause
  );

So on line 9 you're trying to assign what you know is a 1600-character value to a variable you've declared as 200 characters; hence the error. Change the declaration of v_Return to match the function variable's size (or make them both 4000 to allow some more overhead).

Answer 2

Try extending v_Costs out to 4000. If you still receive the error, then the problem is that the string generated by LISTAGG is over 4000 (the maximum length of a VARCHAR2).

If that is the issue, you'll need to use XMLAGG or one of the solutions supplied in this question: How to tweak LISTAGG to support more than 4000 character in select query?