Validating a Process I mentioned sometime back that I would be posting the code. I can't post the entire set of code but I can post the relevant parts.

Problem

Inbound Remittance Advice files are loaded into our Operational Data Store (ODS). We have absolutely no control over this, it lies with another group. On occasion, those files are double, or even triple, loaded.

Goal

To provide the business (and ourselves) with a way to track when files came in and if the entire processed worked as expected (no lost dollars, no lost record counts).

What do we do?

1. Read the files when they first come in (on disk).
2. Query the appropriate tables at certain intervals to verify it matches the file amounts.
3. Load those results into a table and then fail the normal load process if we detect any incongruities.

Solution (proposed)

1. Create a directory object on the Oracle server.
2. Copy inbound files to that directory.
3. Read files from that directory (Java anyone?)
4. Load files into CLOBs (so that I don't have to spend half my day finding the damn things, simple APEX app and I'm good to go).
5. Parse files to find relevant information (Java)
6. Query tables at various stages, blah blah blah.

Solution

Since the UTL_FILE doesn't have a function to read the contents of a directory, Java comes into play. I've done it before and I found the code originally on asktom (where else?).

For those of you too lazy (like me) to click the link, here's the important stuff:

snip...
      File file = new File( path );

      list = file.list();

      for ( int i = 0; i < list.length; i++ )
      {
        File indvidualFile = new File( path + list[i] );

        if ( indvidualFile.isFile() )
        {
          element = list[i];
          statement.setString( 1, element );
        }
      }
snip...

Since I'm using Java, I might as well use the StringTokenizer, makes like so much easier. But wait, since I'm reading it as a CLOB (and not a String), what do I do? I tried clob.toString(). Nope, it's just a pointer to the actual CLOB.

I have to use Reader and CharacterStream, getting well beyond my knowledge of Java.

With the help of a fellow Java developer, I was pointed towards StreamTokenizer which works in a similar fashion to StringTokenizer, or so I thought. Apparently StreamTokenizer is one of the lower level classes...so I had to figure out the ASCII values of the character I wanted to split on (a tilde: ~). I think my Java friend was surprised I figured this out...

      Reader characterStream = clob.getCharacterStream();
      StreamTokenizer stream = new StreamTokenizer( characterStream );
      stream.resetSyntax();
      stream.wordChars( 32, 125 );
      stream.parseNumbers();

Fun.

After I got that as a String, I could then use the StringTokenizer, which I knew.

Could I have split the CLOB using PL/SQL? Yes. Did I want to? Not really. I was already using Java so why not just use the Tokenizer?

The five classes were jarred and loaded into Oracle via the loadjava command. Wrap it all up in PL/SQL and life is easy!

  PROCEDURE get_directory_contents( i_directory IN VARCHAR2 )
  AS 
  LANGUAGE JAVA NAME 'com.hmocompany.dw.LoadFileNames.getFileNames( java.lang.String )';

Labels: java, oracle, work

  ¶ 4/29/2008 07:51:00 PM