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?

Answer 1

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.)