8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 9i » Here

Metadata API (DBMS_METADATA)

Prior to Oracle 9i metadata could only be extracted using SQL statements, export utilities and the OCIDescribeAny interface, all of which are limited.

To solve these issues Oracle 9i has introduced the DBMS_METADATA package, which can be used to retrieve object definitions as DDL or XML. There are a number of ways to use the API, but I suspect the majority of people will only ever use the following functions.

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;

FUNCTION get_dependent_ddl (
  object_type         IN VARCHAR2,
  base_object_name    IN VARCHAR2,
  base_object_schema  IN VARCHAR2 DEFAULT NULL,
  version             IN VARCHAR2 DEFAULT 'COMPATIBLE',
  model               IN VARCHAR2 DEFAULT 'ORACLE',
  transform           IN VARCHAR2 DEFAULT 'DDL',
  object_count        IN NUMBER   DEFAULT 10000)
RETURN CLOB;

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;

These functions can be used as part of a select statement to produce output for groups of objects, such as object creation scripts, like the one shown below.

CONN scott/tiger

SET LONG 20000
SET PAGESIZE 0

SELECT DBMS_METADATA.get_ddl (object_type, object_name, USER)
FROM   user_objects;

  CREATE TABLE "SCOTT"."BONUS"
   (	"ENAME" VARCHAR2(10),
	"JOB" VARCHAR2(9),
	"SAL" NUMBER,
	"COMM" NUMBER
   ) 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 "SYSTEM"



  CREATE TABLE "SCOTT"."DEPT"
   (	"DEPTNO" NUMBER(2,0),
	"DNAME" VARCHAR2(14),
	"LOC" VARCHAR2(13),
	 CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SYSTEM"  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 "SYSTEM"



  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
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SYSTEM"  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 "SYSTEM"



  CREATE UNIQUE INDEX "SCOTT"."PK_DEPT" ON "SCOTT"."DEPT" ("DEPTNO")
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SYSTEM"



  CREATE UNIQUE INDEX "SCOTT"."PK_EMP" ON "SCOTT"."EMP" ("EMPNO")
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SYSTEM"



  CREATE TABLE "SCOTT"."SALGRADE"
   (	"GRADE" NUMBER,
	"LOSAL" NUMBER,
	"HISAL" NUMBER
   ) 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 "SYSTEM"

6 rows selected.

SQL>

Notice the output does not include terminators. The formatting of the output is improved using the following commands.

BEGIN
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
END;
/

The results can be spooled into a file for editing.

The following scripts use DBMS_METADATA to generate DDL scripts.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.