* Just to show  how far ORACLE has come...
  I had to write a procedure to extract DDL from the database, when I was using 8i.
  It is much easier now with this package. Return type is clob. This can easily
  be handled from within PL/SQL.
  A simple test from SQL*PLUS shows the following...

* For valid data type to use with the package, refer to DBMS_METADATA.OPEN



SQL> set long 3000
SQL> set head off
SQL> select DBMS_METADATA.GET_DDL('TABLE','PART_RANGE_TEMP','JYANG') from dual;


  CREATE TABLE "JYANG"."PART_RANGE_TEMP"
   (    "C1" NUMBER(3,0),
        "C2" VARCHAR2(2),
        "C3" CHAR(1)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"
  PARTITION BY RANGE ("C1")
  SUBPARTITION BY LIST ("C3")
 (PARTITION "P1"  VALUES LESS THAN (100)
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"
 ( SUBPARTITION "P1_D1"  VALUES ('A')
   TABLESPACE "USERS",
  SUBPARTITION "P1_D2"  VALUES ('B')
   TABLESPACE "USERS") ,
 PARTITION "P2"  VALUES LESS THAN (200)
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"
 ( SUBPARTITION "P2_D1"  VALUES ('A')
   TABLESPACE "USERS",
  SUBPARTITION "P2_D2"  VALUES ('B')
   TABLESPACE "USERS") ,
 PARTITION "P3"  VALUES LESS THAN (300)
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"
 ( SUBPARTITION "P3_D1"  VALUES ('A')
   TABLESPACE "USERS",
  SUBPARTITION "P3_D2"  VALUES ('B')
   TABLESPACE "USERS") )


SQL> select DBMS_METADATA.GET_DDL('TRIGGER','T5_TRG') from dual;


  CREATE OR REPLACE TRIGGER "JYANG"."T5_TRG"

  after insert or update or delete
  on test5
  for each row
  begin
     if inserting then
        insert into t5_log values('insert');
     elsif deleting then
        insert into t5_log values('delete');
     else
        insert into t5_log values('update');

     end if;
   end;
ALTER TRIGGER "JYANG"."T5_TRG" ENABLE


SQL> select DBMS_METADATA.GET_DDL('PROCEDURE','UNIVERSALTIME') from dual;


  CREATE OR REPLACE PROCEDURE "JYANG"."UNIVERSALTIME" (
UTC OUT date)
AS
  atime varchar2(30);
  aday  varchar2(30);
  amon  varchar2(30);
  ayear varchar2(30);
  ServerTime  date;
BEGIN
 get_gmt(atime, aday, amon, ayear);
 UTC := to_date(aday||'-'||amon||'-'||ayear||' '||atime,'dd-mon-yyyy hh24:mi:ss'
);

END UniversalTime;


SQL> select DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW', 'MV_MVQR3') from dual;


  CREATE MATERIALIZED VIEW "JYANG"."MV_MVQR3"
  ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOG
GING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"
  BUILD IMMEDIATE
  USING INDEX
  REFRESH FORCE ON DEMAND
  WITH ROWID USING DEFAULT LOCAL ROLLBACK SEGMENT
  ENABLE QUERY REWRITE
  AS select c2, sum(c3) as sumc3 from mv_view group by c2


SQL> select DBMS_METADATA.GET_DDL('SYNONYM','PROC1_REMOTE') FROM DUAL;


  CREATE SYNONYM "JYANG"."PROC1_REMOTE" FOR "SCOTT"."PROC1"@"TESTLNK.US.ORACLE.C
OM"



SQL> select DBMS_METADATA.GET_DDL('PACKAGE','MY_PACKAGE') from dual;


  CREATE OR REPLACE PACKAGE "JYANG"."MY_PACKAGE" AS
   FUNCTION my_function RETURN NUMBER;
PRAGMA RESTRICT_REFERENCES (my_function, RNDS, RNPS);
end my_package;
CREATE OR REPLACE PACKAGE BODY "JYANG"."MY_PACKAGE" AS
   FUNCTION my_function RETURN NUMBER IS
   BEGIN
      DELETE FROM t1_;
   end;
end;



SQL> select DBMS_METADATA.GET_DDL('PACKAGE_BODY','MY_PACKAGE') from dual;


  CREATE OR REPLACE PACKAGE BODY "JYANG"."MY_PACKAGE" AS
   FUNCTION my_function RETURN NUMBER IS
   BEGIN
      DELETE FROM t1_;
   end;
end;



SQL>