From: Peter Schneider <>
Date: Tue, 25 Oct 2011 23:44:35 +0200
Message-ID: <j87aj8$has$>


Am 25.10.2011 20:22, schrieb ExecMan:
> Hi,
> I've written a quick script (below) to go through and dump the DDL for
> each users objects in their schema to a log file on disk. The script
> runs fine except for a couple of small things.
> First, for certain Packages I am getting this error:
> PACKAGE - LOAD_ZN - ORA-06502: PL/SQL: numeric or value error
> PACKAGE_BODY - LOAD_ZN - ORA-06502: PL/SQL: numeric or value error
> You'll see in the script I select the data into a CLOB data type.
> These packages are only like 3000 or 4000 lines, so I am not sure why
> I am getting this error since a CLOB should handle up to 4GB, right?


> Here is the actual script:
> v_ddl CLOB;
> v_file_id UTL_FILE.FILE_TYPE;


> UTL_FILE.PUT_LINE(v_file_id,CHR(10)||CHR(10)||v_ddl||CHR(10)||
> CHR(10));
Your call to UTL_FILE.PUT_LINE typecasts the expression with v_ddl from CLOB to VARCHAR2; this can only hold 32767 bytes/characters (depending on your NLS_LENGTH_SEMANTICS). Most likely the code you dump is larger than 32K, no?

Also, DBMS_METADATA will deliver CLOBs which already contain line breaks. UTL_FILE.PUT_LINE, otoh, will also add a (superflouos) line break after each call, which you don't want in this case.


The only way to keep your health is to eat what you don't want, drink what
you don't like, and do what you'd rather not. -- Mark Twain
Received on Tue Oct 25 2011 - 16:44:35 CDT

Original text of this message