Printing sql statements using oracle jdbc driver features on jboss
I’m tring to print all the sql statements executed by oracle jdbc driver (ojdbc14.jar).
It seems that the logging is possible only with java.util.logging feature, i’ve followed this paper to set the logger, in particular:
i’ve added this jvm parameters to jboss java options:
-Doracle.jdbc.Trace=true -Djava.util.logging.config.file=c:\myConfig.properties ##################myConfig.properties################## .level=SEVERE oracle.jdbc.level=INFO oracle.jdbc.handlers=java.util.logging.FileHandler java.util.logging.FileHandler.level=INFO java.util.logging.FileHandler.pattern=c:/jdbc.log java.util.logging.FileHandler.count=1 java.util.logging.FileHandler.formatter=java.util.logging.SimpleFormatter
furthermore i’ve used ojdbc14_g.jar library insted of ojbc14.jar.
With this configuration, it writes a jdbc.log file containing all the logs from ojdbc14_g.jar library.
The first problem I encountered is the duplication of log entries in the jboss server.log.
The second problem is that the generated logs don’t contain the sql statements but other less important infos.
This last problem seems due to the library version less then 10.2.0.4.0
I’m going to try a different driver version…
My notes 
I’ve tried to use the last version of ojdbc14_g.jar and this config file:
#
# set levels
#
.level=CONFIG
oracle.level=CONFIG
oracle.jdbc.driver.level=CONFIG
oracle.jdbc.pool.level=CONFIG
oracle.jdbc.util.level=CONFIG
oracle.sql.level=CONFIG
#
# configure handlers
#
#oracle.handlers=java.util.logging.ConsoleHandler
#java.util.logging.ConsoleHandler.level=OFF
#java.util.logging.ConsoleHandler.formatter=java.util.logging.SimpleFormatter
oracle.handlers=java.util.logging.FileHandler
java.util.logging.FileHandler.level=CONFIG
java.util.logging.FileHandler.pattern=c:/jdbc.log
#java.util.logging.FileHandler.count=1
java.util.logging.FileHandler.formatter=java.util.logging.SimpleFormatter
the result is that the system prints all the queries together with other infos. The duplicate log (console and file) issue remains.
There is another problem with file generation, together with jdbc.log file are created a cuple of .lck and .log.#number# files.
I’ve found a realy useful tool:p6spy. It permits to log all the sql statements via a proxy mode.
I’ve found another interesting project: http://code.google.com/p/log4jdbc/