RE: Using dbms_metadata to extract a full schema

From: Pete Sharman <peter.sharman_at_oracle.com>
Date: Sat, 1 May 2004 06:12:37 +1000
Message-Id: <200404302012.i3UKCduN021656@rgmgw3.us.oracle.com>

Dan, Dan, Dan, Dan, Dan, what can I say? You believe the doc? You foolish= person! :)

Of course, there is the standard Oracle answer.  Not "it depends", but "fix=
ed in the next release".  In 10g we support heterogeneous object types, i.e=
., collections of related objects of different types.  Four such types are =
currently defined: DATABASE_EXPORT, SCHEMA_EXPORT, TABLE_EXPORT and TRANSPO=
RTABLE_EXPORT.  Objects are returned in a valid creation order.  The Data P=

ump uses these heterogeneous types in export. =

Programming is similar to homogeneous types - note completely untested code= here, but I'll leave debugging as an exercise for you since I've given you= the rest of the answer ;) :

declare
h number;
th number;
c clob;
begin
h :=3D dbms_metadata.open('DATABASE_EXPORT'); th :=3D dbms_metadata.add_transform(h,'DDL'); dbms_metadata.set_transform_param(th,'SQLTERMINATOR',true); loop
c :=3D 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; /

Pete
=

"Controlling developers is like herding cats." Kevin Loney, Oracle DBA Handbook
=

"Oh no, it's not. It's much harder than that!" Bruce Pihlamae, long-term Oracle DBA

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] = On Behalf Of Daniel Fink
Sent: Saturday, 1 May 2004 4:55 AM
To: oracle-l_at_freelists.org
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_at_freelists.org 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
-----------------------------------------------------------------

Received on Fri Apr 30 2004 - 15:10:30 CDT