Extract SQL from trace file with Perl

Perl is included with the Oracle database software (SE and EE), even on Windows. I wrote this to extract the SQL statements from a trace file (generated with sql_trace=true or setting the 10046 event). A simple indentation - one tab for each level - is used to show recursive statements.

while (<>) {
  if(/^PARSING IN CURSOR/) {
    ($level)=$_=~/\s+dep=(\d+)\s+/ ;
    $line=<>;
    while ($line!~/END OF STMT/){
      for($i=0;$i<$level;$i++) {
           print "\t" ;
      }
      print $line;
      $line=<>;
    }
    print "\n" ;
  }
}

You usually have to expand your PATH on Windows to find Perl; you'll find Perl.exe somewhere below %ORACLE_HOME%. The code will of course work on Linux and other OS where you have Perl. Store the code above in a file called xtract.pl and call it with:

perl xtract.pl your_trace_file.trc | more

There is an option (record=filename.sql) in tkprof to extract the SQL, but it does not include recursive statements. I guess the difference is that this is something to build on when you have a more complex task.