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

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.

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

If you have db access via sqldeveloper....

Go to reports tab, then drill down through data dictionary, database administration, sessions, and finally sessions.

In that view, look for your app's active module(s) and look at the Active SQL tab. One of them should have your insert statement....

This might help as well... https://docs.oracle.com/cd/E17781_01/server.112/e18804/monitoring.htm#ADMQS252

The ultimate approach is to trace the JDBC connection on the client. Please find the full documentation here

In the first step you must get the logging JDBC driver on the CLASSPATH. The logging driver has a suggix _g in the name, e.g. ojdbc8_g.jar if you use ojdbc8.jar

The driver can be found in the Oracle installation in the folder jdbc/lib/

Further you must define a properties file say jdbcLogging.properties with following content

.level=SEVERE
oracle.jdbc.level=ALL
oracle.jdbc.handlers=java.util.logging.ConsoleHandler
java.util.logging.ConsoleHandler.level=FINE
java.util.logging.ConsoleHandler.formatter=java.util.logging.SimpleFormatter

Finally when you run the Java application you must define two properties

 java -Doracle.jdbc.Trace=true -Djava.util.logging.config.file=jdbcLogging.properties ...

This will produce a trace file om the error output where you can find the executed statements.

Example

INFO: DRCP Enabled: false
Mar 23, 2021 10:40:31 PM oracle.jdbc.driver.OracleStatement logSQL
CONFIG: BAB2F1 SQL: insert into my_table(col1) values(?)

What I ended up doing was downloading WireShark, a sniffer, and monitored the TCP/IP packets.