Advanced SQL IDEs such as TOAD, SQL Developer provide ways to view DDL statements for objects. There is another way you can accomplish the task using GET_DDL method defined in DBMS_METADATA package. See usage below.
Usage : dbms_metadata.get_ddl('Object Type','Object Name','Owner')
SELECT dbms_metadata.get_ddl('FUNCTION','GEN_NUMBERS','SUSIL') FROM dual;
Output:
CREATE OR REPLACE
FUNCTION "SUSIL"."GEN_NUMBERS"(
n IN NUMBER DEFAULT NULL)
RETURN array PIPELINED
AS
BEGIN
FOR i IN 1 .. NVL(n,999999999)
LOOP
pipe row(i);
END LOOP;
RETURN;
END;
SELECT dbms_metadata.get_ddl('TABLE','EMP','SUSIL') FROM dual;
Output:
CREATE TABLE "SUSIL"."EMP"
(
"ENAME" VARCHAR2(10),
"SAL" NUMBER(7,2)
)
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
(
INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
)
TABLESPACE "USER_TS" ;
Note : You cannot view the output if the code is wrapped up.
Usage : dbms_metadata.get_ddl('Object Type','Object Name','Owner')
SELECT dbms_metadata.get_ddl('FUNCTION','GEN_NUMBERS','SUSIL') FROM dual;
Output:
CREATE OR REPLACE
FUNCTION "SUSIL"."GEN_NUMBERS"(
n IN NUMBER DEFAULT NULL)
RETURN array PIPELINED
AS
BEGIN
FOR i IN 1 .. NVL(n,999999999)
LOOP
pipe row(i);
END LOOP;
RETURN;
END;
SELECT dbms_metadata.get_ddl('TABLE','EMP','SUSIL') FROM dual;
Output:
CREATE TABLE "SUSIL"."EMP"
(
"ENAME" VARCHAR2(10),
"SAL" NUMBER(7,2)
)
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
(
INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
)
TABLESPACE "USER_TS" ;
Note : You cannot view the output if the code is wrapped up.
Comments