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
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
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 !