ORA-06502 with ORA-06512

Question

I have a procedure in which I'm trying to write a source code (1290 lines) to dbms_output like this:

dbms_output.put_line(DBMS_METADATA.GET_DDL('FUNCTION', 'name', 'owner')); --MYPROC, line 6

I'm getting :

ORA-06502: PL/SQL: numeric or value error
ORA-06512: in "MYPROC", line 6

. This error occures in toad. I can execute in editor tab of toad:

SELECT DBMS_METADATA.GET_DDL('FUNCTION', 'name', 'owner') FROM DUAL;

I mean I'm getting the source code in 'Data grid'.

Same happens when I try to store the code in a CLOB variable:

src CLOB;
...
src := DBMS_METADATA.GET_DDL('FUNCTION', 'name', 'owner') ; --MYPROC, line 6

Any clue?

You said the function is 1290 lines, but how many characters is it - if it is more than 32k then you need to split it up into smaller chunks. [Like this maybe](https://stackoverflow.com/a/55288064/266304). — Alex Poole, Feb 15 '23 at 12:51
You aren't clear on what happens if you run the last two commands. The error is easy to explain on the first one, dbms_output is limited to 32K (used to be 255 bytes but thankfully that was lifted). The second two should be fine. Can you please post the exact error message each of them gives you? — Paul W, Feb 15 '23 at 13:46

Littlefoot · Answer 1 · 2023-02-15T11:29:05.493

What you are saying can't be true. DBMS_OUTPUT.PUT_LINE can't be used at SQL level, it belongs to PL/SQL.

What is MYPROC and what does it contain at line #6?
Which "editor" is "I can execute in editor"?

Numeric or value error is usually related to the fact that you're trying to store "large" values into a "small" variable:

SQL> declare
  2    l_var varchar2(2);
  3  begin
  4    l_var := 'ABC';
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 4


SQL>

which is what - I presume - you did.

Another cause is wrongly declared variable, e.g.

SQL> declare
  2    l_var number;
  3  begin
  4    l_var := 'A';
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 4


SQL>

I'll try to guess what you might be doing:

SQL> set serveroutput on
SQL> DECLARE
  2     src  CLOB;
  3  BEGIN
  4     src := DBMS_METADATA.GET_DDL ('PACKAGE', 'MY_PKG', 'SCOTT');
  5     DBMS_OUTPUT.put_line ('len = ' || DBMS_LOB.getlength (src));
  6  END;
  7  /
len = 67239

PL/SQL procedure successfully completed.

SQL>

As you can see, it works OK for me. Package isn't that small (see its length), so - can't really tell what you did wrong. I'd suggest you to do exactly as I did - copy/paste code I posted above (those 7 lines), fix information (function, its name, owner) and post the result by editing the original question, not as a comment.

Indeed, I made corrections and added more information in my question. — hajduk, Feb 15 '23 at 10:50

score 1 · Accepted Answer · answered Feb 15 '23 at 15:58

From the documentation for dbms_output:

The maximum line size is 32767 bytes.

That means that you can't pass more than that in a single put_line call. You are currently passing your whole CLOB, which at 1290 lines is likely to exceed that limit. And the error you get when you do that is "ORA-06502: PL/SQL: numeric or value error", as you are seeing.

You can split your CLOB into smaller chunks, and as it is already multiple lines it makes sense to make each chunk a single line from the DDL. You can do that by looking for newline characters, extracting all the text up to the next one, and printing that. You need a few variables to keep track of where you are. Something like this should work for you:

declare
  src clob;
  src_length pls_integer;
  pos pls_integer := 1;
  buffer varchar2(32767);
  amount pls_integer := 32767;
begin
  src := dbms_metadata.get_ddl('FUNCTION', 'TEST_FUNCTION_1', user);
  src_length := dbms_lob.getlength(src);

  while pos < src_length loop
    -- read to next newline if there is one, rest of CLOB if not
    if dbms_lob.instr(src, chr(10), pos) > 0 then
      -- see how many charcaters there are until next newline
      amount := dbms_lob.instr(src, chr(10), pos) - pos;
      -- if there are any, read them into the buffer; otherwise clear it
      if amount > 0 then
        dbms_lob.read(src, amount, pos, buffer);
      else
        buffer := null;
      end if;
      pos := pos + amount + 1; -- skip newline character
    else
      -- no newline so read everything that is left
      amount := 32767;
      dbms_lob.read(src, amount, pos, buffer);
      pos := pos + amount;
    end if;

    dbms_output.put_line(buffer);
  end loop;
end;
/

It won't work if you have a single line (without or without a newline at the end) that is more than 32k, which hopefully won't be an issue with DDL. (You could sort of handle it, but doing so would inject additional newlines, which wouldn't be good either.)

ORA-06502 with ORA-06512

2 Answers2