DDL create commands for Objects (DBMS_METADATA)

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.

The results can be spooled into a file for editing.