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

 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@xxxxxxxxx> 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@xxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
> > *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@xxxxxxxxxxxx
> >
> >
> >
>
>
> --
> Paul Baumgartel
> paul.baumgartel@xxxxxxxxxxxx
>
>


--
Paul Baumgartel
paul.baumgartel@xxxxxxxxxxxx

Other related posts:

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