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


Monday, October 2, 2017

ORA-04043 object XXXXXX does not exist

When I tried to drop my user, I got the error

drop user mrrame cascade;

ORA-00604: error occurred at recursive SQL level 1
ORA-04043: object SYS_PLSQL_816EF5E1_361_1 does not exist

Not sure what is causing it, but quick workaround is to drop the TYPE and replace the numeric value with Dummy as shown

drop type SYS_PLSQL_816EF5E1_DUMMY_1;

drop user mrrame cascade;
user dropped;