Search This Blog

Friday, February 26, 2010

How to TRACE JDBC Statements from a JDBC Program

1. Copy the jar file ojdbc14_g.jar in to the classpath. In the case of JBOSS application Server copy the file in to the directory D:\OIM9101-Jboss\jboss-4.2.3.GA\server\default\lib.

Please note that if there is a ojdbc14.jar you need to remove this file first.

2. Add the following java arguments to your java program

-Doracle.jdbc.Trace=true -Djava.util.logging.config.file=OracleLog.properties 

If you are using JBOSS App Server then use the following

set JAVA_OPTS=%JAVA_OPTS% -Xms256m -Xmx1024m -XX:PermSize=128m -Doracle.jdbc.Trace=true -Djava.util.logging.config.file=OracleLog.properties 

3. The OracleLog.properties  file contains the following entries

#### Console Handler ######

#java.util.logging.ConsoleHandler.level = ALL
#java.util.logging.ConsoleHandler.formatter =
#java.util.logging.SimpleFormatter
#handlers = java.util.logging.ConsoleHandler

#### File  Handler ######

oracle.jdbc.handlers=java.util.logging.FileHandler
java.util.logging.FileHandler.level=ALL
java.util.logging.FileHandler.pattern=jdbc.log
java.util.logging.FileHandler.count=1
java.util.logging.FileHandler.formatter=java.util.logging.SimpleFormatter

# Uncomment and/or change the levels for more detail
#oracle.jdbc.level = FINEST
#oracle.jdbc.connector.level = FINE
#oracle.jdbc.driver.level = FINEST
#oracle.jdbc.internal.level = FINEST
#oracle.jdbc.oci.level = FINE
#oracle.jdbc.oracore.level = FINE
#oracle.jdbc.pool.level = FINE
#oracle.jdbc.rowset.level = FINEST
#oracle.jdbc.util.level = FINEST
#oracle.jdbc.xa.level = FINE
#oracle.jdbc.xa.client.level = FINE
#oracle.jpub.level = FINE
#oracle.net.level = FINE
#oracle.sql.level = FINEST
#.level=CONFIG
#oracle.level=CONFIG
# This is the setting needed for sql debug
oracle.jdbc.driver.level=CONFIG
#oracle.jdbc.pool.level=CONFIG
#oracle.jdbc.util.level=CONFIG
#oracle.sql.level=CONFIG

#oracle.jdbc.driver.level=FINE

6. Run your JDBC Program , assuming you statement is

select pty_key , pty_value from pty  where pty_keyword = :1

This is the Bind Parameter value

INFO: OraclePreparedStatement.setString(paramIndex=1, x=XL.MaxLoginAttempts)

Feb 27, 2010 12:00:41 PM oracle.jdbc.driver.PhysicalConnection getDefaultFixedString
INFO: PhysicalConnection.getDefaultFixedString() returning false
Feb 27, 2010 12:00:41 PM oracle.jdbc.driver.OraclePreparedStatement setString
INFO: OraclePreparedStatement.setString(paramIndex=1, x=XL.MaxLoginAttempts)
Feb 27, 2010 12:00:41 PM oracle.jdbc.driver.OracleStatement doExecuteWithTimeout
CONFIG: SQL: "select pty_key , pty_value from pty  where pty_keyword = :1"
Feb 27, 2010 12:00:41 PM oracle.jdbc.driver.T4CPreparedStatement allocateTmpByteArray
SEVERE: oracle.jdbc.driver.T4CPreparedStatement.allocateTmpByteArray : Re-allocate byte array of size : 80
Feb 27, 2010 12:00:41 PM oracle.jdbc.driver.OracleResultSetImpl findColumn
INFO: OracleResultSetImpl.findColumn(columnName=pty_value)
Feb 27, 2010 12:00:41 PM oracle.jdbc.driver.OracleResultSetImpl getString
INFO: OracleResultSetImpl.getString(columnIndex=2)
Feb 27, 2010 12:00:41 PM oracle.jdbc.driver.OracleResultSetImpl close
INFO: OracleResultSetImpl.close()