OBIEE: Parse NQQuery Log

It's been quite some time since I've dabbled in Java, thankfully JDeveloper makes it pretty easy for me.

I've been annoyed at how often I have to go dig through the NQQuery logs lately. Yes, I know about Session Manager and the ability to view logs there.

So I decided to write a quick and dirty java program that will pull the physical SQL out of the file. I believe there are 7 log levels in OBIEE, this is done at level 5 so no guarantee it will work on anything but 5.

I'll be putting the source code up soon and will update as appropriate. For the time being, here it is:

package oraclenerd;
import java.io.*;

public class ParseSQL
{
  public static void main(String[] args)
  {
    ParseSQL run = new ParseSQL();
    String query = new String();
    int i = 0;
    try
    {
      FileInputStream file = new FileInputStream( "/opt/projects/oraclenerd/classes/oraclenerd/test.log" );
      DataInputStream in = new DataInputStream( file );
      BufferedReader br = new BufferedReader( new InputStreamReader( in ) );
      String strLine;
      while ( ( strLine = br.readLine() ) != null )
      {
        if ( strLine.startsWith( "-------------------- Sending query" ) )
        {
          i = 1;          
        }
        else if ( i == 1 )
        {
          if ( strLine.startsWith( "+++" ) )
          {
            System.out.println( "***************" );
            System.out.println( query );
            System.out.println( "***************" );
            query = "";
            i = 0;
          }
          else
            query = query + "\r\n" + strLine;
        }
      }
      in.close();
    }
    catch (Exception e)
    {
      System.err.println( "Error:  " + e.getMessage() ); 
    }
  }
}

Obviously you'll need to change the path unless you have that exact path at home. You can find the log I used here.

There are 17 physical SQL statements in the log file. You should get 17 back.

The 17 SQL statements I retrieved are here (via console).

The source code is up above...until I get unlazy enough to put it under source control (Google Code). If you do make it pretty or add some functionality to it, please let me know, I'd love to include it.