Thursday, October 19, 2017

Oracle export unwrapped object's DDL

i had to mass extract source code for all unwrapped objects from the db, and here is what I used

select   u.name,  u.type,   dbms_metadata.get_ddl(replace(u.type,' BODY'),u.name) ddl_source
from    user_source u
where   line = 1
and     instr(text, 'wrapped') = 0
and type  in ( 'PACKAGE BODY'   ,   'PROCEDURE','FUNCTION')
order by u.type,    u.name

I ran above sql to extract Package, Procedure and Function and ran it in Toad/SQL Developer and then export the output

Here is an example to extract table definition from scott schema

Select t.table_name ,dbms_metadata.get_ddl('TABLE',t.table_name) ddl_source
from user_tables t


No comments:

Post a Comment