GET_XML and GET_DDL Functions

GET_XML and GET_DDL return the metadata for the specified object as XML or DDL.

Syntax

FUNCTION get_xml (
  object_type IN VARCHAR2,
  name IN VARCHAR2,
  schema IN VARCHAR2 DEFAULT NULL,
  version IN VARCHAR2 DEFAULT 'COMPATIBLE',
  model IN VARCHAR2 DEFAULT 'ORACLE',
  transform IN VARCHAR2 DEFAULT NULL)
RETURN CLOB;

FUNCTION get_ddl (							
  object_type IN VARCHAR2,
  name N VARCHAR2,
  schema IN VARCHAR2 DEFAULT NULL,
  version IN VARCHAR2 DEFAULT 'COMPATIBLE',
  model IN VARCHAR2 DEFAULT 'ORACLE',
  transform IN VARCHAR2 DEFAULT 'DDL')
RETURN CLOB;

Parameters

Table 28-15 GET_xxx Parameters 

Parameter	Description
object_type	The type of object to be retrieved. This parameter takes the same values as the OPEN object_type parameter. In addition the following types may be specified: PACKAGE_SPEC - package specification (without body) PACKAGE_BODY - package body TYPE_SPEC - type specification (without body) TYPE_BODY - type body
name	An object name (case-sensitive). If object_type is SYNONYM and name is longer than 30 characters, then name will be treated as a LONGNAME filter. See Table 28-5.
schema	A schema name (case sensitive). The default is the current schema if object_type refers to a schema object; otherwise the default is NULL.
version	The version of metadata to be extracted. This parameter takes the same values as the OPEN version parameter.
model	The object model to use. This parameter takes the same values as the OPEN model parameter.
transform	The name of a transformation on the output. This parameter takes the same values as the ADD_TRANSFORM name parameter. For GET_XML this must not be DDL.

Returns

The metadata for the specified object as XML or DDL.

Exceptions

• INVALID_ARGVAL. A NULL or invalid value was supplied for an input parameter. The error message text identifies the parameter.

• OBJECT_NOT_FOUND. The specified object was not found in the database.

Usage Notes

These functions provide a simple way to return the metadata for a single object. Conceptually each GET_xxx call is comprised of an OPEN, one or two SET_FILTER calls, optionally an ADD_TRANSFORM, a FETCH_xxx and a CLOSE. The object_type parameter has the same semantics as in OPEN. The schema and name parameters are used for filtering. If a transform is specified, schema-level transform flags are inherited.

This function can only be used to fetch named objects. It cannot be used to fetch objects of type OBJECT_GRANT or SYSTEM_GRANT. To fetch these objects, use the programmatic interface.

Example 1. Fetching the XML Representation of SCOTT.EMP

set pagesize 0
set long 90000
SELECT DBMS_METADATA.GET_XML
  (
   'TABLE','EMP','SCOTT')
    FROM DUAL;

Example 2. Fetching the DDL for all Complete Tables in the Current Schema, Filtering Out Nested Tables and Overflow Segments

This example fetches the DDL for all "complete" tables in the current schema, filtering out nested tables and overflow segments. The example uses SET_TRANSFORM_PARAM (with the handle value = DBMS_METADATA.SESSION_TRANSFORM meaning "for the current session") to specify that storage clauses are not to be returned in the SQL DDL. Afterwards, the example resets the session-level parameters to their defaults.

set pagesize 0
set long 90000
execute DBMS_METADATA.SET_TRANSFORM_PARAM(
  DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);
SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)
     FROM USER_ALL_TABLES u
     WHERE u.nested='NO' 
     AND (u.iot_type is null or u.iot_type='IOT');
execute DBMS_METADATA.SET_TRANSFORM_PARAM(
   DBMS_METADATA.SESSION_TRANSFORM,'DEFAULT');