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
  IS
    l_handle NUMBER;
    l_clob CLOB;
    l_transform_number NUMBER;
    l_object_type VARCHAR2(30);
  BEGIN
    
    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 );

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

    dbms_metadata.set_filter
      ( 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...

    dbms_lob.write
      ( 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;
  EXCEPTION
    WHEN others THEN  
      dbms_metadata.close( handle => l_handle );
      RAISE;
  END get_ddl;

Voila!

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.

Labels: database, dbms_metadata, howto, oracle, utl_file

  ¶ 1/27/2009 12:17:00 AM