How to get bind variables values from Oracle

If you already used JDBC prepared statement, you know what are bind variables : the '?' in the query, like in : SELECT col1,col2 from t_table where col1 in (?,?,?) AND col2 = ? For the record, all compiled queries with the same number of '?' are cached by Oracle, hence (most of the time) faster to execute. But how to debug passed values ? This is often valuable like yesterday where one of our services tried to insert value too large for a column (a 4 digits integer into a NUMBER(5,2)).

There is several ways to achieve it, one is using a 'wrapper' JDBC driver (like log4jdbc) that audit and log the values but it's a bit intrusive.

A very simple non-intrusive way for a specific need is to query the v$sql table, the Oracle internal log. A sample query is given bellow (source Stack Overflow) :

select s.sql_id, 
       bc.position, 
       bc.value_string, 
       s.last_load_time, 
       bc.last_captured
from v$sql s
  left join v$sql_bind_capture bc 
         on bc.sql_id = s.sql_id 
        and bc.child_number = s.child_number
where s.sql_text like 'delete from tableA where fk%' -- or any other method to identify the SQL statement
order by s.sql_id, bc.position;

It works like a charm !