Metadata API (DBMS_METADATA)

Prior to Oracle9i metadata could only be extracted using SQL statements, export utilities and the OCIDescribeAny interface, all of which are limited:

The SQL approach is limited in that as versions change, so must your scripts.
Using export with ROWS=N and an import with SHOW=Y will produce the text to allow you to recreate a schema, but it can require considerable editing.
The OCIDescribeAny interface is limited in the objects it supports.

To solve these issues Oracle9i has introduced the DBMS_METADATA package which can be used to retrieve object definitions as XML or SQL DDL:

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 IN VARCHAR2,
           schema IN VARCHAR2 DEFAULT NULL,
           version IN VARCHAR2 DEFAULT 'COMPATIBLE',
           model IN VARCHAR2 DEFAULT 'ORACLE',
           transform IN VARCHAR2 DEFAULT NULL)
         RETURN CLOB;

These functions can be used as part of a select statement to produce output for groups of objects:

SET LONG 20000
SET PAGESIZE 0

SELECT DBMS_METADATA.get_ddl ('TABLE','EMP','SCOTT')
FROM   dual;

  CREATE TABLE "SCOTT"."EMP"
   (    "EMPNO" NUMBER(4,0),
        "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE,
        "SAL" NUMBER(7,2),
        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0),
         CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"  ENABLE,
         CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
          REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"



1 row selected.

SQL>

The results can be spooled into a file for editing.

For more information see:

DBMS_METADATA

Hope this helps. Regards Tim...

Back to the Top.