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

obat herbalobat herbal, 2018/08/24 08:34

obat herbaobat herba, 2018/08/29 04:56

Cara Mengatasi Mata Merah Akibat GadgetCara Mengatasi Mata Merah Akibat Gadget, 2018/09/06 11:15

You have brought up a very great points, thank you for the post.

huangliyuehuangliyue, 2018/09/07 16:25

20189.8huangliyue 20189.8huangliyue

Ramuan Herbal Untuk Pengobatan Penyakit KuningRamuan Herbal Untuk Pengobatan Penyakit Kuning, 2018/09/11 08:11

Articles like this that I have been waiting for, because the article is quite impressive.

chenqiuyingchenqiuying, 2018/09/14 15:18 chenqiuying20180914

obat herbaobat herba, 2018/09/21 08:33

rental mobil medanrental mobil medan, 2018/09/25 15:34

Thanks for the information you have provided

Obat Kanker OtakObat Kanker Otak, 2018/10/01 05:18

Obat Kanker Otak

obat herbalobat herbal, 2018/10/30 02:16

xiaozhengzhengxiaozhengzheng, 2018/11/03 10:33

2018-11-3 xiaozhengzheng

obat herbalobat herbal, 2018/11/09 05:28

xiaoouxiaoou, 2018/11/16 03:02

20181116 orf

zhoyzhouyan88zhoyzhouyan88, 2018/11/19 07:58

2018.11.19zhouyanhua 2018.11.19zhouyanhua

obat herbalobat herbal, 2018/11/26 05:44

Mengatasi Saluran Pipa Air MampetMengatasi Saluran Pipa Air Mampet, 2018/12/11 09:07

Thank you, the information you provide is interesting and has many benefits

Obat GinjalObat Ginjal, 2018/12/26 08:57

The information you provide is very useful