Trace: how_to_get_bind_variables_values_from_oracle

Table of Contents

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



kakakaookakakaoo, 2018/05/12 05:51


chenyingyichenyingyi, 2018/06/27 04:44 chenyingyi20180627Bold Text

Pantangan MakananUntuk Penderita Batu EmpeduPantangan MakananUntuk Penderita Batu Empedu, 2018/07/01 08:49

The site is always a lot of visitors, because it presents an interesting article.

obat herbal alamiobat herbal alami, 2018/07/06 09:08

chenlixiangchenlixiang, 2018/07/16 04:04 2018.7.16chenlixiang

medianmedian, 2018/07/21 06:37

obat herbal walatra bersih wanitaobat herbal walatra bersih wanita, 2018/07/25 06:36

Terimakasih Atas Informasinya sangat kren Mampir ke Blogger Kami (

chenqiuyingchenqiuying, 2018/08/01 04:47 chenqiuying20180731

Pengobatan Alami Eksim Atopik Yang AmanPengobatan Alami Eksim Atopik Yang Aman, 2018/08/06 10:45

Information that is always in waiting by the visitors.

Obat Eksim Kering Yang ManjurObat Eksim Kering Yang Manjur, 2018/08/08 02:27

An offer of information that is always in waiting by the visitors.

obat herbalobat herbal, 2018/08/08 08:48

chenlinachenlina, 2018/08/15 02:25

chenlina20180815 http://www.trikotsgü chenlina20180815

obat herbalobat herbal, 2018/08/15 06:11

Enter your comment. Wiki syntax is allowed:
blog_tech/5/how_to_get_bind_variables_values_from_oracle.txt · Last modified: 2015/04/06 21:12 by bflorat