How to use the JDBC Driver Tracing in Weblogic/SOA 11g to capture the sql statements issued by the JDBC Drivers

Before starting the weblogic server set the following environment variables ,

On Windows

set EXT_PRE_CLASSPATH=C:\Oracle\Middleware\wlserver_10.3\server\ext\jdbc\oracle\11g\ojdbc6_g.jar
set JAVA_OPTIONS=-Doracle.jdbc.Trace=true -Djava.util.logging.config.file=c:/temp/OracleLog.properties 

On Unix

set EXT_PRE_CLASSPATH=/home/oracle/Middleware/wlserver_10.3/server/ext/jdbc/oracle/11g/ojdbc6_g.jar
set JAVA_OPTIONS=-Doracle.jdbc.Trace=true -Djava.util.logging.config.file=/tmp/OracleLog.properties 

In the OracleLog.properties have 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=c:/temp/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
#oracle.jdbc.pool.level=CONFIG
#oracle.jdbc.util.level=CONFIG
#oracle.sql.level=CONFIG

#oracle.jdbc.driver.level=FINE

# This is the setting needed for sql debug
oracle.jdbc.driver.level=FINE
oracle.level=OFF

 

TRACE_1: Public Enter: "DELETE FROM MDS_LABELS WHERE LABEL_NAME = ? AND LABEL_PARTITION_ID =?"
Mar 12, 2011 10:18:10 PM oracle.jdbc.driver.PhysicalConnection prepareStatement
TRACE_1: return: oracle.jdbc.driver.OraclePreparedStatementWrapper@1d56ac0
Mar 12, 2011 10:18:10 PM oracle.jdbc.driver.PhysicalConnection prepareStatement
TRACE_1: Exit
Mar 12, 2011 10:18:10 PM oracle.jdbc.driver.OracleStatement setPoolable
TRACE_1: Public Enter: true
Mar 12, 2011 10:18:10 PM oracle.jdbc.driver.OracleStatement setPoolable
TRACE_1: Exit
Mar 12, 2011 10:18:10 PM oracle.jdbc.driver.OraclePreparedStatement setString
TRACE_1: Public Enter: 1, "preDeployLabel_soa-infra"
Mar 12, 2011 10:18:10 PM oracle.jdbc.driver.OraclePreparedStatement setString
TRACE_1: Exit
Mar 12, 2011 10:18:10 PM oracle.jdbc.driver.OraclePreparedStatement setLong
TRACE_1: Public Enter: 2, 1
Mar 12, 2011 10:18:10 PM oracle.jdbc.driver.OraclePreparedStatement setLong
TRACE_1: Exit
Mar 12, 2011 10:18:10 PM oracle.jdbc.driver.OraclePreparedStatement executeUpdate
lTRACE_1: Public Enter:
Mar 12, 2011 10:18:10 PM oracle.jdbc.driver.OracleStatement doExecuteWithTimeout
CONFIG: SQL: DELETE FROM MDS_LABELS WHERE LABEL_NAME = ? AND LABEL_PARTITION_ID =?

The sql statement is "DELETE FROM MDS_LABELS WHERE LABEL_NAME = ? AND LABEL_PARTITION_ID =?"
The bind variales are "preDeployLabel_soa-infra" and 1.
Mar 12, 2011 10:18:10 PM oracle.jdbc.driver.OraclePreparedStatement setString
TRACE_1: Public Enter: 1, "preDeployLabel_soa-infra"
Mar 12, 2011 10:18:10 PM oracle.jdbc.driver.OraclePreparedStatement setLong
TRACE_1: Public Enter: 2, 1

The sql statement is "DELETE FROM MDS_LABELS WHERE LABEL_NAME = 'preDeployLabel_soa-infra' AND LABEL_PARTITION_ID =1"

Another example ,

TRACE_1: Public Enter: "SELECT PARTITION_ID FROM MDS_PARTITIONS WHERE PARTITION_NAME=?"

Mar 12, 2011 10:18:10 PM oracle.jdbc.driver.OraclePreparedStatement setString
TRACE_1: Public Enter: 1, "soa-infra"
Mar 12, 2011 10:18:10 PM oracle.jdbc.driver.OraclePreparedStatement setString

The sql statement is "SELECT PARTITION_ID FROM MDS_PARTITIONS WHERE PARTITION_NAME='soa-infra'

If you want only the sql statements with out bind variables use
oracle.jdbc.driver.level=CONFIG

This displays the sql statemnets as

CONFIG: 13EEB53C SQL: update  MEDIATOR_CASE_INSTANCE  set  STATUS ='locked',  LOCK_TIME =?,  CONTAINER_ID =? where  STATUS  = 'ready' and rownum < ?
CONFIG: 466208C SQL: SELECT CASE_ID, CONTAINER_ID, MSG_ID, OPERATION, COMPONENT_DN, PRIORITY, LOCK_TIME, QNAME_LOCAL_PART, IS_EVENT, QNAME_NAMESPACE, CASE_INFO, SOURCE_URI, COMPONENT_STATUS, INSTANCE_CREATED, CREATION_DATE, STATUS, DUMMY1 FROM MEDIATOR_DEFERRED_MESSAGE WHERE (((STATUS = ?) AND (LOCK_TIME = ?)) AND (CONTAINER_ID = ?))