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   0 Comments Links to this post

  UTL_FILE: Writing a File to the File System This is more for me that it is for you as I always seem to forget how to do this. Next time I'll simply search my site for the example.

Nothing fancy here, I just want to be able to write a file to a directory on the file system. This directory must be a database object (until java is used anyway).

So...

CREATE DIRECTORY test_dir AS '/temp';

No, I'm not doing this on Linux (yet), this is my c:\temp directory.

Here's the procedure:

CREATE OR REPLACE
PROCEDURE write_to_file
  ( p_dir IN VARCHAR2,
    p_filename IN VARCHAR2,
    p_file IN CLOB )
IS
  l_file_handle UTL_FILE.FILE_TYPE;
  l_loops INTEGER := 1;
  l_max_linesize NUMBER := 32767;
  l_start_position NUMBER := 1;
  l_source VARCHAR2(32767);
BEGIN
  l_file_handle := utl_file.fopen
                    ( location => p_dir,
                      filename => p_filename,
                      open_mode => 'w',
                      max_linesize => l_max_linesize );

  LOOP
    l_source := SUBSTR( p_file, l_start_position, l_max_linesize );
    EXIT WHEN l_source IS NULL;

    utl_file.put_line
      ( file => l_file_handle,
        buffer => l_source );

    l_start_position := l_loops + ( l_max_linesize * l_loops );
    l_loops := l_loops + 1;

  END LOOP;
  utl_file.fclose( l_file_handle );

EXCEPTION
  WHEN others THEN
    utl_file.fclose( l_file_handle );
    RAISE;
END write_to_file;
/
show errors

Simple enough right? Running it is easy from SQL*Plus.

EXEC write_to_file( 'TEST_DIR', 'testing.sql', 'HELLO WORLD!' );

I've done it 20 or 30 times, but each time I have to relearn it...next time I won't!

Labels: howto, utl_file

  ¶ 1/25/2009 12:15:00 AM   0 Comments Links to this post

  Validating a Process Part II Continued from my previous post.

While discussing external tables with my feisty colleague some time back, I explained that I liked using them but I couldn't figure out how to change the file name to match that of what was defined in the table definition.

Colleage to the rescue:

ALTER TABLE table_name LOCATION ( 'new_file_name.csv' );

Cool!

As I mentioned before, I had 4 files types I had to read: 820 and 835, both of the x12 format and two custom file layouts (flat files essentially). Since no one in the group knows Java yet, I wanted to keep the Java portion of the application as small as possible. So with the two custom files, I decided to use external tables. I could then put into practice the above ALTER TABLE statement.

As I looped through the list of files to be processed, I would issue an EXECUTE IMMEDIATE so that I could then SELECT from the table in the next step. It worked like a charm.

As I was doing some testing, I would issue the ROLLBACK statement to clear the tables for the next run. When I verified, there was still data there. WTF?

Oh wait, there's an EXECUTE IMMEDIATE...which runs DDL...which COMMITs...barnacles!

So I couldn't use that new thing I learned, oh well. Fortunately UTL_FILE does have the ability to rename files so I picked a name like 'external_table_file_name.txt' and rename the incoming file to that, then SELECT. Works like a charm.

Labels: ddl, java, sql, utl_file

  ¶ 5/01/2008 11:40:00 PM   5 Comments Links to this post