Reverse engineering a SQL replay script from a 10046 trace file

This will be the coup de grâce (but in a good way:)) if I can ever finish it. I have wanted to do this for years, and keep getting closer. Search for awk 10046 in the search bar on this site for other articles as it relates to this.

UPDATE 2012_08_24: As noted by Ilya in the comments section, there is no DATE variable in SQL*Plus. I will change what is below when I have time, but for now, it would only be able to successfully run the types listed in Ilya’s response.

The awk script below will reverse engineer SQL statements with their associated bind variables. I still have to:

1. replace the bind variable names in the statement (:B1, or :1, or :custom_name. etc.). Technically, you could run it as is and simply issue a global replace using your favorite editor.
2. Handle more datatypes, as it currently only does VARCHAR2, NUMBER, and DATE
3. run all statements other than a single one, but figure out a way to eliminate the recursive ones such as “select * from sys.col$ where…” It’s not as simple as filtering for only dep=0, although that is a possibility. SQL generated from a PL/SQL loop is also considered recursive for the purposes of tracing.

#!/bin/awk -f
#----------------------------------------------------------------------------------
#Author:        Steve Howard
#Date:          March 1, 2012
#Purpose:       Reverse engineer a script complete with bind variables that can be replayed.
#----------------------------------------------------------------------------------

BEGIN {
  if (ARGC != 3) {
    print "Usage:",ARGV[0]," -f script_name tracefile_name \"sqlid_for_which_to_search\""
    exit(1)
  }
  start = systime()
  SQL=ARGV[2]
  BIND=ARGV[3]
  ARGC=2
}

{
  if ($0 ~ "^PARSING" && $NF ~ SQL) {
    C=$4;
    i = 0
    sub("#","",C);
    getline;
    FULLSQL = $0
  }
  else if (length(C) > 0 && $0 ~ "^BINDS") {
    if ($0 ~ "BINDS #"C":") {
      looking = 1;
      i = 0
    }
    else {
      looking = 0;
    }
  }
  else if ($0 ~ "^ Bind#" && looking == 1) {
    sub("#","",$1)
    BNAME = $1;
    getline;
    sub("oacdty=","",$1)
    if ($1 == "01") {
      sub("mxl=","",$2)
      split($2,t,"(")
      vals[i] = "variable "BNAME" varchar2("t[1]")"
      i++
    }
    else if ($1 == "02") {
      vals[i] = "variable "BNAME" number"
      i++
    }
    else if ($1 == "12") {
      vals[i] = "variable "BNAME" date"
      type = "date"
      i++
    }
  }
  else if ($0 ~ "value=" && looking == 1) {
    sub("value=","",$0)
    gsub("\"","'",$0)
    if ( type == "date") {
      vals[i] = "exec :"BNAME" := to_date("$0")"
      type = ""
    }
    else {
      vals[i] = "exec :"BNAME" := "$0
    }
    i++
  }
  else if (length(C) > 0 && $0 ~ "EXEC #"C && length(FULLSQL) > 0) {
    for (j = 0; j < i; j++) {
      print vals[j]
    }
    print FULLSQL";"
    delete vals
  }
}

When we pass in a SQL_ID, we get output similar to what is below:

[root@dell11gr1 ~]# ./revengineer_10046.awk OATEST_ora_17563710.trc 53242u7v7j2fw | head -3
variable Bind0 date
exec :Bind0 := to_date(  '12/1/2011 0:0:0')
variable Bind1 varchar2(32)
exec :Bind1 :=   'DEC-11'
variable Bind2 number
exec :Bind2 :=   17968032
UPDATE AP_INVOICE_DISTRIBUTIONS_ALL AID SET ACCOUNTING_DATE=:b1,PERIOD_NAME=:b2,LAST_UPDATE_DATE=SYSDATE,LAST_UPDATED_BY=5 WHERE AID.INVOICE_DISTRIBUTION_ID = :b3;
variable Bind0 date
exec :Bind0 := to_date(  '12/1/2011 0:0:0')
variable Bind1 varchar2(32)
exec :Bind1 :=   'DEC-11'
variable Bind2 number
exec :Bind2 :=   20075260
UPDATE AP_INVOICE_DISTRIBUTIONS_ALL AID SET ACCOUNTING_DATE=:b1,PERIOD_NAME=:b2,LAST_UPDATE_DATE=SYSDATE,LAST_UPDATED_BY=5 WHERE AID.INVOICE_DISTRIBUTION_ID = :b3;
variable Bind0 date
exec :Bind0 := to_date(  '12/1/2011 0:0:0')
variable Bind1 varchar2(32)
exec :Bind1 :=   'DEC-11'
variable Bind2 number
exec :Bind2 :=   20075261
[root@dell11gr1 ~]#