Extract SQL from an Oracle SQL Trace File (10046)

trc2sql.perl

#!/usr/bin/perl
 
# David Mann
# ba6.us
# Extract SQL from an Oracle SQL Trace File (10046)
#
# Usage:
# Data is read from STDIN
# trc2sql.pl  output.sql
#
# Change log:
# 13-OCT-2011 : Release

# 6-Mar-2013  : Support bind var, add by  @henices
 
$InSQL = 0;
$InBIND = 0;

while (<>) {
  $MyLine = $_;
  $LineNum = $LineNum + 1;

  if ($MyLine =~ /====/) {
    print "$MyLine\n";
  }
 
  # If END OF STMT found, stop printing
  if (($InSQL==1) && ($MyLine =~ /END OF STMT/ )) {
    print ";\n\n\n";
    $InSQL = 0;
  }

  if (($InBIND==1) && ($MyLine =~ /value/ )) {
    print $MyLine;
  }

  if (($InBIND==1) && ($MyLine =~ /EXEC/ )) {
    print ";\n\n\n";
    $InSQL = 0;
  }
  
 
  # In matching pattern, continue printing
  if ($InSQL == 1) {
    print $MyLine;
  }
 
  # If PARSING IN CURSOR found, start printing
  if ($MyLine =~ /PARSING IN CURSOR/) {
    print "-- " . $MyLine;
    $InSQL = 1;
  }

  if ($MyLine =~ /BINDS #/) {
    print "-- " . $MyLine;
    $InBIND = 1;
  }
 
}