DBMS_METADATA

參考網址 : https://www.itread01.com/article/1503040590.html

範例 A : 

SELECT table_name, ddlTable,
dbms_lob.substr(lob_loc => ddlTable,
                amount  => dbms_lob.getlength(ddlTable) ,
                offset  => 1)
from
(
SELECT utable.table_name,
       dbms_metadata.get_ddl('TABLE', utable.table_name) ddlTable
FROM   user_tables utable
WHERE  rownum <= 10
) tabs

範例 B : 

DECLARE

  vObjectName VARCHAR2(40) := 'FND_API';
  vObjectType VARCHAR2(40) := 'PACKAGE';
  xClob       CLOB;
  vClobLength NUMBER;
  vChar       VARCHAR2(1);

  TYPE rLine IS RECORD(
    idx  NUMBER,
    data VARCHAR2(2000));
  TYPE tLine IS TABLE OF rLine;
  tblLine tLine := tLine();
BEGIN
 
  xClob       := dbms_metadata.get_ddl(vObjectType, vObjectName);
  vClobLength := dbms_lob.getlength(xClob);
  FOR i IN 1 .. vClobLength
  LOOP
    vChar := dbms_lob.substr(lob_loc => xClob, amount => 1, offset => i);
    IF vChar = chr(10) THEN
      tblLine.extend();
    ELSE
     tblLine(tblLine.last).data := tblLine(tblLine.last).data || vChar;
    END IF;
  END LOOP;
 
  dbms_output.enable(100000);
 
  IF tblLine IS NOT empty THEN
     FOR i IN tblLine.first .. tblLine.last LOOP
        dbms_output.put_line(tblLine(i).data);
     END LOOP;
  END IF;
END;