{"id":1919,"date":"2012-03-01T08:36:36","date_gmt":"2012-03-01T13:36:36","guid":{"rendered":"http:\/\/appcrawler.com\/wordpress\/?p=1919"},"modified":"2013-04-19T19:42:04","modified_gmt":"2013-04-20T00:42:04","slug":"reverse-engineering-a-sql-replay-script-from-a-10046-trace-file","status":"publish","type":"post","link":"http:\/\/appcrawler.com\/wordpress\/2012\/03\/01\/reverse-engineering-a-sql-replay-script-from-a-10046-trace-file\/","title":{"rendered":"Reverse engineering a SQL replay script from a 10046 trace file"},"content":{"rendered":"

This will be the coup de gr\u00e2ce (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.<\/p>\n

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.<\/p>\n

The awk script below will reverse engineer SQL statements with their associated bind variables. I still have to:<\/p>\n

    \n
  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.<\/li>\n
  2. Handle more datatypes, as it currently only does VARCHAR2, NUMBER, and DATE<\/li>\n
  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.<\/li>\n<\/ol>\n
    #!\/bin\/awk -f\r\n#----------------------------------------------------------------------------------\r\n#Author:        Steve Howard\r\n#Date:          March 1, 2012\r\n#Purpose:       Reverse engineer a script complete with bind variables that can be replayed.\r\n#----------------------------------------------------------------------------------\r\n\r\nBEGIN {\r\n  if (ARGC != 3) {\r\n    print \"Usage:\",ARGV[0],\" -f script_name tracefile_name \\\"sqlid_for_which_to_search\\\"\"\r\n    exit(1)\r\n  }\r\n  start = systime()\r\n  SQL=ARGV[2]\r\n  BIND=ARGV[3]\r\n  ARGC=2\r\n}\r\n\r\n{\r\n  if ($0 ~ \"^PARSING\" && $NF ~ SQL) {\r\n    C=$4;\r\n    i = 0\r\n    sub(\"#\",\"\",C);\r\n    getline;\r\n    FULLSQL = $0\r\n  }\r\n  else if (length(C) > 0 && $0 ~ \"^BINDS\") {\r\n    if ($0 ~ \"BINDS #\"C\":\") {\r\n      looking = 1;\r\n      i = 0\r\n    }\r\n    else {\r\n      looking = 0;\r\n    }\r\n  }\r\n  else if ($0 ~ \"^ Bind#\" && looking == 1) {\r\n    sub(\"#\",\"\",$1)\r\n    BNAME = $1;\r\n    getline;\r\n    sub(\"oacdty=\",\"\",$1)\r\n    if ($1 == \"01\") {\r\n      sub(\"mxl=\",\"\",$2)\r\n      split($2,t,\"(\")\r\n      vals[i] = \"variable \"BNAME\" varchar2(\"t[1]\")\"\r\n      i++\r\n    }\r\n    else if ($1 == \"02\") {\r\n      vals[i] = \"variable \"BNAME\" number\"\r\n      i++\r\n    }\r\n    else if ($1 == \"12\") {\r\n      vals[i] = \"variable \"BNAME\" date\"\r\n      type = \"date\"\r\n      i++\r\n    }\r\n  }\r\n  else if ($0 ~ \"value=\" && looking == 1) {\r\n    sub(\"value=\",\"\",$0)\r\n    gsub(\"\\\"\",\"'\",$0)\r\n    if ( type == \"date\") {\r\n      vals[i] = \"exec :\"BNAME\" := to_date(\"$0\")\"\r\n      type = \"\"\r\n    }\r\n    else {\r\n      vals[i] = \"exec :\"BNAME\" := \"$0\r\n    }\r\n    i++\r\n  }\r\n  else if (length(C) > 0 && $0 ~ \"EXEC #\"C && length(FULLSQL) > 0) {\r\n    for (j = 0; j < i; j++) {\r\n      print vals[j]\r\n    }\r\n    print FULLSQL\";\"\r\n    delete vals\r\n  }\r\n}<\/pre>\n
    When we pass in a SQL_ID, we get output similar to what is below:<\/p>\n
    [root@dell11gr1 ~]# .\/revengineer_10046.awk OATEST_ora_17563710.trc 53242u7v7j2fw | head -3\r\nvariable Bind0 date\r\nexec :Bind0 := to_date(  '12\/1\/2011 0:0:0')\r\nvariable Bind1 varchar2(32)\r\nexec :Bind1 :=   'DEC-11'\r\nvariable Bind2 number\r\nexec :Bind2 :=   17968032\r\nUPDATE 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;\r\nvariable Bind0 date\r\nexec :Bind0 := to_date(  '12\/1\/2011 0:0:0')\r\nvariable Bind1 varchar2(32)\r\nexec :Bind1 :=   'DEC-11'\r\nvariable Bind2 number\r\nexec :Bind2 :=   20075260\r\nUPDATE 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;\r\nvariable Bind0 date\r\nexec :Bind0 := to_date(  '12\/1\/2011 0:0:0')\r\nvariable Bind1 varchar2(32)\r\nexec :Bind1 :=   'DEC-11'\r\nvariable Bind2 number\r\nexec :Bind2 :=   20075261\r\n[root@dell11gr1 ~]#<\/pre>\n","protected":false},"excerpt":{"rendered":"
    This will be the coup de gr\u00e2ce (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…<\/p>\n
    Read more →<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_mi_skip_tracking":false,"footnotes":""},"categories":[19,28,27,22],"tags":[],"_links":{"self":[{"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/posts\/1919"}],"collection":[{"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/comments?post=1919"}],"version-history":[{"count":32,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/posts\/1919\/revisions"}],"predecessor-version":[{"id":2751,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/posts\/1919\/revisions\/2751"}],"wp:attachment":[{"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/media?parent=1919"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/categories?post=1919"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/tags?post=1919"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}