Oracle SQL: Bind variables

Selecting bound values with dynamic performance views

create or replace procedure tq84_test_bind(p1 number, p2 varchar2, p3 date) as
   cnt number;
begin
   select /* find-me */ count(*) into cnt from dual where 42 = p1 and 'hello world' = p2 and sysdate = p3;
end tq84_test_bind;
/

exec tq84_test_bind(42, 'hello world', sysdate);

select
   sql.sql_id,
   to_char(cap.last_captured, 'yyyy-mm-dd hh24:mi:ss') last_captured,
   any_value(case when met.position =  1 then cap.value_string end) bind_01,
   any_value(case when met.position =  2 then cap.value_string end) bind_02,
   any_value(case when met.position =  3 then cap.value_string end) bind_03,
   any_value(case when met.position =  4 then cap.value_string end) bind_04,
   any_value(case when met.position =  5 then cap.value_string end) bind_05,
   any_value(case when met.position =  6 then cap.value_string end) bind_06,
   any_value(case when met.position =  7 then cap.value_string end) bind_07,
   any_value(case when met.position =  8 then cap.value_string end) bind_08,
   any_value(case when met.position =  9 then cap.value_string end) bind_09,
   any_value(case when met.position = 10 then cap.value_string end) bind_10,
   any_value(case when met.position = 11 then cap.value_string end) bind_11,
   any_value(case when met.position = 12 then cap.value_string end) bind_12,
   any_value(case when met.position = 13 then cap.value_string end) bind_13,
   any_value(case when met.position = 14 then cap.value_string end) bind_14,
   any_value(case when met.position = 15 then cap.value_string end) bind_15,
   any_value(case when met.position = 16 then cap.value_string end) bind_16,
   any_value(case when met.position = 17 then cap.value_string end) bind_17,
   any_value(case when met.position = 18 then cap.value_string end) bind_18,
   any_value(case when met.position = 19 then cap.value_string end) bind_19,
   sql.sql_text
from
   v$sql_bind_capture  cap                                             join
   v$sql_bind_metadata met on cap.child_address = met.address    and   
                              cap.position      = met.position         join
   v$sql               sql on cap.child_address = sql.child_address
where
   sql.sql_text like q'[SELECT COUNT(*) FROM DUAL WHERE 42 = :B3 AND 'hello world' = :B2 AND SYSDATE = :B1%]'
group by
   sql.sql_id,
   sql.sql_text,
   cap.last_captured;

See also

Links