DBMS_METADATA – how to recreate the DDL for PL/SQL objects

by Granville Bonyata on November 2, 2013

DBMS_METADATA.get_ddl works great when looking for the DDL of a table or a view:


select dbms_metadata.get_ddl('VIEW','TEST_DDL_VW')
from dual;

CREATE OR REPLACE FORCE VIEW "GBONYATA"."TEST_DDL_VW" ("CHAR_COL", "NUM_COL", "DATE_COL") AS
select char_col, num_col, date_col from test_ddl

But not so much for some objects:


select dbms_metadata.get_ddl('SCHEDULE','DEMO_SCHEDULE')
from dual;

ORA-31600: invalid input value SCHEDULE for parameter OBJECT_TYPE in function GET_DDL
ORA-06512: at "SYS.DBMS_METADATA", line 3773
ORA-06512: at "SYS.DBMS_METADATA", line 3828
ORA-06512: at "SYS.DBMS_METADATA", line 5678
ORA-06512: at line 1
31600. 00000 - "invalid input value %s for parameter %s in function %s"
*Cause: A NULL or invalid value was supplied for the parameter.
*Action: Correct the input value and try the call again.

For these, when looking at objects created via PL/SQL, use the type PROCOBJ:

select dbms_metadata.get_ddl('PROCOBJ','DEMO_SCHEDULE')
from dual;

Previous post:

Next post: