豆知識18 実行したSQLのバインド変数の値の取得

V$SQL_Bind_Captureは10gR1で追加された動的パフォーマンスビューを使って、
実行したSQL文のバインド変数値をチェックするSelect文

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
バインド変数にバインドした値の一覧と
実行したSQL文の先頭50文字を表示するSelect文

var BindVar1 Number;
exec :BindVar1 := 111;
var BindVar2 Number;
exec :BindVar2 := 222;
var BindVar3 Number;
exec :BindVar3 := 333;

with t(Col1,Col2,Col3) as(
select 1,2,3 from dual)
select * from t
 where Col1 in(:BindVar1 , :BindVar2 , :BindVar3)
   and Col2 in(:BindVar1 , :BindVar2 , :BindVar3)
   and Col3 in(:BindVar1 , :BindVar2 , :BindVar3);

col rn for 99
col SQL_text for a50
col Name for a15
col Value_String for a15

select Dense_Rank() over(order by a.SQL_ID) as rn,
substr(a.SQL_text,1,50) as SQL_text,
b.Name,b.Value_String,b.Position
  from V$SQL a Join V$SQL_Bind_Capture b
    on a.SQL_ID = b.SQL_ID
 where InStr(a.SQL_text,':BindVar1') > 0
order by a.SQL_ID,a.SQL_ID,b.Position;

rn  SQL_text                                            Name       Value_String  Position
--  --------------------------------------------------  ---------  ------------  --------
 1  BEGIN :BindVar1 := 111; END;                        :BINDVAR1  null                 1
 2  with t(Col1,Col2,Col3) as( select 1,2,3 from dual)  :BINDVAR1  111                  1
 2  with t(Col1,Col2,Col3) as( select 1,2,3 from dual)  :BINDVAR2  222                  2
 2  with t(Col1,Col2,Col3) as( select 1,2,3 from dual)  :BINDVAR3  333                  3
 2  with t(Col1,Col2,Col3) as( select 1,2,3 from dual)  :BINDVAR1  111                  4
 2  with t(Col1,Col2,Col3) as( select 1,2,3 from dual)  :BINDVAR2  222                  5
 2  with t(Col1,Col2,Col3) as( select 1,2,3 from dual)  :BINDVAR3  333                  6
 2  with t(Col1,Col2,Col3) as( select 1,2,3 from dual)  :BINDVAR1  111                  7
 2  with t(Col1,Col2,Col3) as( select 1,2,3 from dual)  :BINDVAR2  222                  8
 2  with t(Col1,Col2,Col3) as( select 1,2,3 from dual)  :BINDVAR3  333                  9

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
バインド変数にバインドした値の一覧と
実行したSQL文を全て表示するSelect文

--バインド変数の値を列挙
with tmp as(
select a.SQL_ID,a.SQL_text,
b.Name,b.DataType,b.Value_String,b.Position,
sum(case when InStr(a.SQL_text,'SELECT aaaaaaa')
     > 0 then 1 else 0 end) over(partition by a.SQL_ID) as SumVal
  from V$SQL a Left Join V$SQL_Bind_Capture b
    on a.SQL_ID = b.SQL_ID
order by a.SQL_ID,b.Position)
select *
  from tmp
 where SumVal > 0;

--バインドしたSQL文を取得
select SQL_Text
  from V$SQLText
 where SQL_ID='1234567890'
order by PIECE;