Look into the demos provided by Oracle on using Metadata .=20 Under $ORACLE_HOME/rdbms/demo directory. (see mddemo.sql and mddemo2.sql) Yuval. =20 -----Original Message----- From: Pete Sharman [mailto:peter.sharman@xxxxxxxxxx]=20 Sent: Friday, April 30, 2004 4:13 PM To: oracle-l@xxxxxxxxxxxxx Cc: Peter Ross Sharman Subject: RE: Using dbms_metadata to extract a full schema Dan, Dan, Dan, Dan, Dan, what can I say? You believe the doc? You foolish=3D person! :) Of course, there is the standard Oracle answer. Not "it depends", but "fix=3D ed in the next release". In 10g we support heterogeneous object types, i.e=3D ., collections of related objects of different types. Four such types are =3D currently defined: DATABASE_EXPORT, SCHEMA_EXPORT, TABLE_EXPORT and TRANSPO=3D RTABLE_EXPORT. Objects are returned in a valid creation order. The Data P=3D ump uses these heterogeneous types in export. =3D Programming is similar to homogeneous types - note completely untested code=3D here, but I'll leave debugging as an exercise for you since I've given you=3D the rest of the answer ;) : declare h number; th number; c clob; begin h :=3D3D dbms_metadata.open('DATABASE_EXPORT'); th :=3D3D dbms_metadata.add_transform(h,'DDL'); dbms_metadata.set_transform_param(th,'SQLTERMINATOR',true); loop c :=3D3D dbms_metadata.fetch_clob(h); exit when c is null; -- do what you want with the returned DDL end loop; dbms_metadata.close(h); end; / =3D Pete =3D "Controlling developers is like herding cats." Kevin Loney, Oracle DBA Handbook =3D "Oh no, it's not. It's much harder than that!" Bruce Pihlamae, long-term Oracle DBA -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] =3D On Behalf Of Daniel Fink Sent: Saturday, 1 May 2004 4:55 AM To: oracle-l@xxxxxxxxxxxxx Subject: Re: Using dbms_metadata to extract a full schema Thanks for all the code and packages. It looks like this is the only way, I was just hoping that there was a method using the dbms_metadata package to extract all the schema ddl with one call. Of course, this would make sense and the documentation SAYS you can do it, but it looks not to be the case. Regards, Daniel ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------