Re: DBMS_METADATA

From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Wed, 26 Oct 2011 06:08:01 -0700 (PDT)
Message-ID: <3f7b9167-a0bb-44ec-b3ed-44574e1c6d98_at_19g2000yqk.googlegroups.com>

On Oct 25, 5:44 pm, Peter Schneider <pschneider1..._at_googlemail.com> wrote:
> Hi,
>
> 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

ExecMan, Have you considered just using SQLPlus's spool feature to capture the DDL in a text file?

HTH -- Mark D Powell -- Received on Wed Oct 26 2011 - 08:08:01 CDT