Script review


I have written dozens of scripts over the years and some of my favorites have been ones I created to reverse-engineer various database objects such as indexes and packages.  These scripts proved useful during difficult database migrations or when source code was needed to troubleshoot performance problems.

As Oracle tools and features have improved, I have used these scripts less and less, but every once in awhile, there is a situation when I have to reach back and put them to use once more.  Sometimes, though, they don’t work that well on newer versions of the Oracle database.  I then have to rework them to bring them up to speed.

One of these scripts is the one I use to reverse-engineer packages.  Originally, I used a temp table and output from dba_source to get the desired output.  Later on, I reworked this script to utilize the dbms_metadata.get_ddl function to get my output.  Last week, I noticed that this script wasn’t giving me the output I needed, so it was back to the drawing board.

When I first started using the dbms_metadata.get_ddl function, I was using a variable with a LONG datatype to store the returned value, then parsing the variable’s contents line-by-line.  It turns out that the actual datatype returned from that function is a CLOB.  I can only guess in my earlier version of my script that there was an implicit conversion happening.  My latest reworking of my package reverse-engineer script does a better job of converting CLOB data into something readable.  Since it uses the dbms_metadata.get_ddl function to return the desired object’s code, this script can be reworked to reverse-engineer other database objects such as indexes, tables, and procedures.  You can refer to the Oracle documentation on the dbms_metadata package to get more detail on the kinds of objects supported.

Keep in mind the number of objects you are trying to reverse-engineer.  In database environments with thousands of packages and procedures, such as EBS, it would be better to narrow the scope of the driving query.


–Substitute the owner for a different schema or remove the where clause
–for the current schema

set pagesize 9999
set linesize 200
set wrap on

set serveroutput on size unlimited;
spool ./pg.out
c clob;
procedure printout
(p_clob in out nocopy clob) is
offset number := 1;
amount number := 32767;
len    number := dbms_lob.getlength(p_clob);
lc_buffer varchar2(32767);
i pls_integer := 1;
if ( dbms_lob.isopen(p_clob) != 1 ) then, 0);
end if;
while ( offset < len )
— If no more newlines are found, read till end of CLOB
if (instr(p_clob, chr(10), offset) = 0) then
amount := len – offset + 1;
amount := instr(p_clob, chr(10), offset) – offset;
end if;

— This is to catch empty lines, otherwise we get a NULL error
if ( amount = 0 ) then
lc_buffer := ”;
else, amount, offset, lc_buffer);
end if;
–dbms_output.put_line(‘Line #’||i||’:’||lc_buffer);
— This is to catch a newline on the last line with 0 characters behind it
i := i + 1;
if (instr(p_clob, chr(10), offset) = len) then
lc_buffer := ”;
–dbms_output.put_line(‘Line #’||i||’:’||lc_buffer);
end if;

offset := offset + amount + 1;
end loop;
if ( dbms_lob.isopen(p_clob) = 1 ) then
end if;
when others then
dbms_output.put_line(‘Error : ‘||sqlerrm);
end printout;
begin  –This is the driving query of the script
for x in (select owner, object_name
from dba_objects
where owner not like ‘%SYS%’
and object_type = ‘PACKAGE’)
SELECT DBMS_METADATA.GET_DDL(‘PACKAGE’, x.object_name, x.owner) into c
FROM dual;
end loop;

spool off



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s