How to debug the SQL generated by the DBAdapter

To debug DBAdapter execution, it is very helpful to know the exact SQL being generated by the adapter. This post describes how you can set up your environment so that you can inspect the SQL being generated.

Enable JDBC Debugging

Log in to the WebLogic Console, select Servers

WlConsole_servers

Select each server on which you want to debug the DBAdapter. In my case here, I’m running a combined Admin and SOA Server on the AdminServer (for Development).

SelectServer

Select the Debug tab

SelectDebugTab

Enable Debugging for /weblogic/jdbc/sql/DebugJDBCSQL

EnableDebugJDBCSql

Configure Log Levels

Now log in to the EM Console. Select soa-infra and navigate to the Log Configuration as shown below

SOAInfraLogConfiguration

You may want to create a new log file to separately capture just the Adapter logs in isolation. An easy way to do this is to select an existing log file handler and then click Create Like.

CreateLikeLogFile

Just change the Log Path to name your specific log file and set the trace level to 32. Also associate the logger with oracle.soa.adapter

EditLogFileConfiguration

This is how your Log Configuration will look now. Note the adapterDiagnostics handler associated with oracle.soa.adapter.

ConfigureOracleSoaAdapterTraceLogging

Inspect the SQL

Execute your scenario, which includes exercising the DBAdapter. You can now inspect the specific log file you had created in the previous step for all the SQL generated, in a text editor. Or, you can export specific log entries by filtering the log from EM as shown below.

ViewLogMessages

ExportLogFile

Example Log shown below

SqlLogged

Depending on the complexity of the DBAdapter, you may see multiple SQL statements from the same DBAdapter. Look for all sql statements with the same ECID and same DBAdapter name.

Jaideep