Re: SCHEMA_EXPORT with DBMS_METADATA -- assigning and referencing nested table elements

From: Paul Baumgartel <paul.baumgartel_at_gmail.com>
Date: Sat, 21 Jan 2006 13:25:32 -0500
Message-ID: <f8c47710601211025w20c14484x5ff75df04a6bd32a@mail.gmail.com>

 The code below (thanks Henry) works fine...but I'm unsure of the mechanics of assigning to and referencing elements of a nested table.

 loop

      v_localddls := dbms_metadata.fetch_ddl(v_handle);
      v_i := v_i + 1;

      exit when v_localddls is null;
      INSERT INTO schema_metadata(object_type, object_name, md_text)
        SELECT cv_schema_export, pi_schema_name, ddlText FROM
          table(cast(v_localddls as ku$_ddls));

    end loop;
    dbms_metadata.close(v_handle);
    dbms_output.put_line(to_char(v_i)||' items');

I don't understand how assignments can be made repeatedly in a loop without any subscript on v_localddls (which is defined as v_localddls sys.ku$_ddls;). How is it that each fetch advances to the next element in the array? I added the counter to the loop to verify that there are multiple iterations--in this case, 2833. Also, I've not been successful in attempts to access individual elements using a subscript after the loop completion--something like

for i in v_localddls.first..v_locaddls.last loop   v_ddl := v_localddls(i).ddlText;
end loop;

As you might guess, I don't have a lot of experience with these collection types, and the docs aren't particularly helpful or comprehensive.

One last thing: can parse items be set for SCHEMA_EXPORT, so that I could get the object type and object name for each DDL returned?

Thanks again for the help.

On 1/20/06, Paul Baumgartel <paul.baumgartel_at_gmail.com> wrote:
>
> Henry,
>
> I modified my code to reflect your approach and I'm now getting all of the
> DDL--thanks again! I modeled my original code on some working code that
> gets dependent DDL (*that* was modeled on something I found on the Net), but
> it wasn't right for a schema export. My modified code gets one DDL per
> object, which is fine--now I want to set parse items to get object type and
> object name for each one. Hopefully I'll get that working and then post it
> to the list.
>
> Regards,
>
> Paul
>
> On 1/20/06, Henry Poras <henry_at_itasoftware.com> wrote:
> >
> > Paul,
> >
> > Here is a rough script I wrote for this. Haven't used it too much, but
> > it might help.
> >
> > Henry Poras
> >
> >
> > -----Original Message-----
> > *From:* oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
> > *On Behalf Of *Paul Baumgartel
> > *Sent:* Thursday, January 19, 2006 7:21 PM
> > *To:* Oracle-L
> > *Subject:* SCHEMA_EXPORT with DBMS_METADATA
> >
> > Has anyone successfully used DBMS_METADATA's SCHEMA_EXPORT capability to
> > generate DDL for all schema objects? If so, please let me know; perhaps
> > you'll be good enough to take a look at my code and tell me what I'm doing
> > wrong. I have a TAR..er, SR, open with Oracle but as usual the analyst is
> > clueless. Thanks.
> >
> > --
> > Paul Baumgartel
> > paul.baumgartel_at_aya.yale.edu
> >
> >
> >
>
>
> --
> Paul Baumgartel
> paul.baumgartel_at_aya.yale.edu
>
>

--
Paul Baumgartel
paul.baumgartel_at_aya.yale.edu

--
http://www.freelists.org/webpage/oracle-l

Received on Sat Jan 21 2006 - 12:25:32 CST