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

Jaideep has more than 20 years of professional software development experience. He has delivered solutions for several domains including financial services, e-Government, criminal justice, and wireless application services. He is a Certified Scrum Master and has mentored several teams to use agile software development techniques. He has delivered several SOA/BPM solutions based on the BEA/Oracle SOA Suite over the last 6 years . His current focus is on helping customers build solutions using Oracle BPM, Webcenter, ADF and SOA Suite 11g and 12c. Jaideep also trains teams on using Oracle BPM and has developed several self paced online video training courses for Oracle BPM 11g and 12c.
Jaideep

Leave a Reply

avatar
  Subscribe  
Notify of