The DBMS_METADATA package

The DBMS_METADATA package was introduced in Oracle9i. It is a metadata API which is used to extract the definitions (DDL) of schema objects. The package was introduced to get rid of DDL exports, which used to produce poorly formatted DDL scripts. It is a powerful package which can generate DDL and retrieve relevant information associated with an object in XML (by default), or textual format. The package is owned by SYS while all other users work with its public synonym.

The package provides utilities to set the required formatting for the DDL, transforms, and parse items. Once the formatting settings start over, using transform handlers, the definition of an object can be retrieved as XML or text. It also provides the flexibility to execute DDL. Let us see some of the major features of DBMS_METADATA:

• Generate DDL through GET_DDL (GET_XML is its XML equivalent).

• Generate DDL for object dependencies through GET_DEPENDENT_DDL (GET_DEPENDENT_XML is its XML equivalent...