Re: DBMS_METADATA

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:
>
> DECLARE
> 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.

Regards
Peter

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