Tuesday, January 27, 2009

Dowload Source Code to Disk

I wanted to be able to download all the source to file system. The problem was that I couldn't figure out how to separate the Package Specification and the Package Body.

I sent out a tweet asking for help. @neilkod answered my plea. Try using DBMS_METADATA he said. I did I replied. He suggested using a filter. Hadn't tried that.

OK, I won't rehash the entire (slow) conversation here, suffice it to say, he pointed me in the right direction.

Here's the code I ended up with:
  FUNCTION get_ddl
( p_owner IN VARCHAR2,
p_object_name IN VARCHAR2,
p_object_type IN VARCHAR2 ) RETURN CLOB
l_handle NUMBER;
l_clob CLOB;
l_transform_number NUMBER;
l_object_type VARCHAR2(30);

l_object_type := get_object_type( p_object_type );
First, a call to an internal (my internal) routine that will determine the object type value for the OPEN function. I found that table here.

l_handle := dbms_metadata.open( object_type => l_object_type );

( handle => l_handle,
name => 'SCHEMA',
value => p_owner );

( handle => l_handle,
name => 'NAME',
value => p_object_name );
The SET_FILTER value values can be found here.

l_transform_number := dbms_metadata.add_transform
( handle => l_handle,
name => 'DDL' );
If you don't set this you'll get a bunch of XML and I really wasn't in the mood for XML.

l_clob := dbms_metadata.fetch_clob
( handle => l_handle,
cache_lob => TRUE,
lob_duration => DBMS_LOB.SESSION );
Fetch the CLOB...

( lob_loc => l_clob,
amount => 1,
offset => dbms_lob.getlength( l_clob ),
buffer => '/' );
Append a run "/" sign at the end of the file...

dbms_metadata.close( handle => l_handle );

RETURN l_clob;
WHEN others THEN
dbms_metadata.close( handle => l_handle );
END get_ddl;

All done. Now you just need a routine to write it to file and you're done.

I'm also putting this in the googlecode repository here: http://code.google.com/p/plsqlsourcedownload/

You should be able to view all the code there. If not, let me know. Not sure how that works yet but I'm giving it a go.

No comments: