Script Analyzing TraceSQL File And Extracting SQL Statements

TraceSQL is a great tool for Peoplesoft development debugging and application troubleshooting. But TraceSQL file only logs SQL statements and SQL variable values separately and so is less readable and hard to re-run.

This script is developed to analyze TraceSQL files, filter out unnecessary information, extract SQL statements and replace all SQL variables with the actual values.

For example, for the following contents in a tracesql file:

PSAPPSRV.12271 (951)     1-190    20.22.39    0.008245 Cur#1.12271.CS90SUP RC=0 Dur=0.000238 COM Stmt=SELECT OBJNAME, FLAG, PTCUSTOMFORMAT FROM PSUSEROBJTYPE WHERE MENUNAME = :1 AND PNLGRPNAME = :2 AND PNLNAME = :3 AND OPRID = :4 AND FIELDTYPE = :5
PSAPPSRV.12271 (951)     1-191    20.22.39    0.000013 Cur#1.12271.CS90SUP RC=0 Dur=0.000001 Bind-1 type=2 length=26 value=CALCULATE_TUITION_AND_FEES
PSAPPSRV.12271 (951)     1-192    20.22.39    0.000008 Cur#1.12271.CS90SUP RC=0 Dur=0.000000 Bind-2 type=2 length=14 value=ADJ_TERM_PANEL
PSAPPSRV.12271 (951)     1-193    20.22.39    0.000008 Cur#1.12271.CS90SUP RC=0 Dur=0.000001 Bind-3 type=2 length=1 value=
PSAPPSRV.12271 (951)     1-194    20.22.39    0.000006 Cur#1.12271.CS90SUP RC=0 Dur=0.000000 Bind-4 type=2 length=2 value=PS
PSAPPSRV.12271 (951)     1-195    20.22.39    0.000010 Cur#1.12271.CS90SUP RC=0 Dur=0.000000 Bind-5 type=18 length=2 value=-1

the script comes out with below SQL:

SELECT OBJNAME, FLAG, PTCUSTOMFORMAT FROM PSUSEROBJTYPE WHERE MENUNAME = 'CALCULATE_TUITION_AND_FEES' AND PNLGRPNAME = 'ADJ_TERM_PANEL' AND PNLNAME = ' ' AND OPRID = 'PS' AND FIELDTYPE = -1;

Script usage: xsql /path/to/tracesql

Update:
- 03-Mar-2010: Bug fix: encapsulated date/time values with quotes. Fixed the issue that the last SQL statement is not outputed.