How can I capture query sent from Java (tomcat) to an Oracle DB?

Question

I have an application written by a 3rd party which uses Java/Tomcat talking to an Oracle 12c (12.2.0.1) DB. In its logs it reports "Error inserting into table" but provides no details. In talking with the author's support staff they indicate it is old code and they have no way to give more detail. They say the application is better supported with MSSQL which we do not support in our shop.

I would like to see what the insert statement going to the Oracle DB looks like, but haven't been able to find it in v$sqltext. As an alternative, I was hoping to find a tool like fiddler to view the outbound traffic on port 1521.

Is there specific tool that would allow trapping this traffic which is not encrypted so I can see the "query" sent and the response coming back from the Oracle DB?

A general sniffer may work, but they generally get a lot of extraneous traffic and require a fair amount of mucking about to find what you want.

Note:

As I mentioned in the comments I am not a Tomcat/Java person. I think I found where the classpath is set. Given the windows batch file below, is the "driver" that needs to be replaced bcprov-jdk16-138.jar?

set PROJLIB=..\..
set JAVA_HOME=%PROJLIB%\jdk\

set libDIR=%PROJLIB%\appserver\webapps\receiver\WEB-INF\lib
set consoleDIR=%PROJLIB%\bin\lib

set endorsedLibDir=%PROJLIB%\appserver\endorsed

set CPATH= %consoleDIR%\console.jar;%libDIR%\ebxml.jar;%libDIR%\commons-io-1.1.jar;%libDIR%\bcprov-jdk16-138.jar;%libDIR%\xercesImpl.jar

set CLASSPATH=%CPATH%

set PATH=%JAVA_HOME%\bin;%SystemRoot%;%SystemRoot%\system32

Additional Notes:

The above file is called setenv.bat.

Regarding trying to capture the SQL from the database, the application is not a windows app, it is an app which accepts data from the network and writes it to the DB. This makes knowing precisely when to start and stop monitoring difficult. It seems to be connected for a very short period. It does seem to be able to read data, but not insert.

Answer 1

Assuming that you are using the Oracle JDBC driver and that you have the ability to replace the JDBC driver in some environment in order to debug the problem, Oracle provides versions of the JDBC driver that can be configured to log the SQL statements that are executed.

An alternative would be to create a servererror trigger in the database that logs the SQL statements that fail. I believe that would require that the SQL statement that is failing is well-formed which isn't guaranteed if the third party app is encountering an error dynamically assembling the statement. If the statement never lands in v$sql that may indicate that it isn't well-formed but it's worth a try.

If you're licensed to use the AWR/ ASH tables, you could also try querying dba_hist_active_sess_history. Oracle samples the active sessions every second. If the failing statement happens to be caught in the sampling, you'd see it there. If this is a typical OLTP application doing single-row inserts, you may need to run through a lot of samples in order to catch an active session with that statement but that may be reasonable.

Answer 2

The simples approach is, if you can localize your database session (using gv$session selecting your connection USERNAME).

Get the SID and SERIAL# of the connection and activate the 10046 trace using the following statement. (substutite SID for session_id and SERIAL# for serial_num)

EXEC DBMS_MONITOR.session_trace_enable(session_id =>271, serial_num=>46473, binds=>TRUE);

Note that you need permissions for both querying gv$session and executing DBMS_MONITOR so DBA access is required to grant them to your user.

Than check the trace file on the database server in folder trace, the trace file has a name such as xe_m005_1336.trc

Grep for the table name, you schould see someting like this I simulated for failed insert on the table my_table

=====================
PARSING IN CURSOR #854854488 len=38 dep=0 uid=104 oct=2 lid=104 tim=380974114197 hv=1259660490 ad='7ff08904d88' sqlid='1ttgvst5j9t6a'
insert into my_table(col1) values(:1 )
END OF STMT
PARSE #854854488:c=0,e=495,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=380974114195
=====================
PARSE ERROR #854854488:len=39 dep=0 uid=104 oct=2 lid=104 tim=380974117361 err=904
insert into my_table(col1) values(:1 )

Note that this is example of an exception

java.sql.SQLSyntaxErrorException: ORA-00904: "COL1": invalid identifier

so the statement fails with an PARSE ERROR

If the insert fails due to some constraint vialotation, you will see such sequence

=====================
PARSING IN CURSOR #715594288 len=37 dep=0 uid=104 oct=2 lid=104 tim=382407621534 hv=3290870806 ad='7ff0032e238' sqlid='17t3q0v22dd0q'
insert into my_table(col) values(:1 )
END OF STMT
PARSE #715594288:c=0,e=245,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=382407621532
=====================

The cursor id is #715594288so check with this id further in the trace file

BINDS #715594288:

 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000000 frm=01 csi=873 siz=24 off=0
  kxsbbbfp=2aa71a00  bln=22  avl=02  flg=05
  value=7
=====================

Here you see the bind variables passed in the insert, it was the value = 7 that caused the failure.

EXEC #715594288:c=0,e=4614,p=0,cr=7,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=382407626259
ERROR #715594288:err=2290 tim=382407626283

The statement failed in the execution with exception such as

java.sql.SQLIntegrityConstraintViolationException: ORA-02290: check constraint (XXXX.SYS_C0012357) violated

Check the documentation for further details