Skip to Main Content

Breadcrumb

We're taking a break this week as Connor and Chris will both be at CloudWorld, the premier Oracle conference of 2024. If you're in Vegas, please come say Hi or pop into our sessions

Question and Answer

Connor McDonald

Thanks for the question, Sunny.

Asked: July 06, 2019 - 5:31 pm UTC

Last updated: July 11, 2019 - 3:03 pm UTC

Version: 12c

Viewed 10K+ times! This question is

You Asked

I found some issue while I query to find out value of a bind variable using v$sql_bind_capture.

So sometimes bind value capture by a single SQL query or some time not capture.
In some case suppose more than three bind variable are there so some of them represent value and one have null value so what is the reason behind this situation?

and Connor said...

v$sql_bind_capture does not capture all bind variables for every execution, typically only for when we parse the SQL.

If you need to get every bind, then I think you'd have to look at doing it via tracing, eg

dbms_monitor.session_trace_enable(binds=>true)

and extracting it from the trace file.

Rating

  (2 ratings)

We're not taking comments currently, so please try again later if you want to add a comment.

Comments

See also

John Keymer, July 08, 2019 - 10:57 am UTC

I asked this question a while back which may help:

https://asktom.oracle.com/pls/apex/f?p=100:11:::::P11_QUESTION_ID:9533607800346493502
Chris Saxon
July 11, 2019 - 3:03 pm UTC

Good point, thanks for sharing.

using FGA (Fine Grained Access)

Rajeshwaran Jeyabal, July 16, 2019 - 6:44 am UTC


....
If you need to get every bind, then I think you'd have to look at doing it via tracing.
....

Other option would be to have FGA configured like this, that would help us to track all the bind variable values used in each execution. something like this.
demo@PDB1> create table t as select * from emp;

Table created.

demo@PDB1> begin
  2     dbms_fga.add_policy(object_schema=>user,
  3             object_name=>'T',
  4             policy_name=>'T_ACCESS_POLICY' );
  5  end;
  6  /

PL/SQL procedure successfully completed.

demo@PDB1> variable x1 number
demo@PDB1> variable x2 varchar2(20)
demo@PDB1> variable x3 varchar2(20)
demo@PDB1> exec :x1 := 10; :x2 := 'KING'; :x3 := '15-Aug-2019';

PL/SQL procedure successfully completed.

demo@PDB1> select count(*) from t where deptno = :x1;

  COUNT(*)
----------
         3

demo@PDB1> col db_user format a5
demo@PDB1> col sql_text format a30
demo@PDB1> col sql_bind format a15
demo@PDB1> col policy_name format a15
demo@PDB1> select db_user,sql_text,sql_bind,policy_name
  2  from dba_fga_audit_trail
  3  where policy_name ='T_ACCESS_POLICY' ;

DB_US SQL_TEXT                       SQL_BIND        POLICY_NAME
----- ------------------------------ --------------- ---------------
DEMO  select count(*) from t where d  #1(2):10       T_ACCESS_POLICY
      eptno = :x1


demo@PDB1> select count(*) from t where ename = :x2;

  COUNT(*)
----------
         1

demo@PDB1> select db_user,sql_text,sql_bind,policy_name
  2  from dba_fga_audit_trail
  3  where policy_name ='T_ACCESS_POLICY' ;

DB_US SQL_TEXT                       SQL_BIND        POLICY_NAME
----- ------------------------------ --------------- ---------------
DEMO  select count(*) from t where d  #1(2):10       T_ACCESS_POLICY
      eptno = :x1

DEMO  select count(*) from t where e  #1(4):KING     T_ACCESS_POLICY
      name = :x2


demo@PDB1> select count(*) from t where hiredate < to_date(:x3,'dd-mon-yyyy');

  COUNT(*)
----------
        14

demo@PDB1> select db_user,sql_text,sql_bind,policy_name
  2  from dba_fga_audit_trail
  3  where policy_name ='T_ACCESS_POLICY' ;

DB_US SQL_TEXT                       SQL_BIND        POLICY_NAME
----- ------------------------------ --------------- ---------------
DEMO  select count(*) from t where d  #1(2):10       T_ACCESS_POLICY
      eptno = :x1

DEMO  select count(*) from t where e  #1(4):KING     T_ACCESS_POLICY
      name = :x2

DEMO  select count(*) from t where h  #1(11):15-Aug- T_ACCESS_POLICY
      iredate < to_date(:x3,'dd-mon- 2019
      yyyy')


demo@PDB1>
demo@PDB1>
demo@PDB1> exec :x1 := 20;

PL/SQL procedure successfully completed.

demo@PDB1> select count(*) from t where deptno = :x1;

  COUNT(*)
----------
         5

demo@PDB1> select db_user,sql_text,sql_bind,policy_name
  2  from dba_fga_audit_trail
  3  where policy_name ='T_ACCESS_POLICY';

DB_US SQL_TEXT                       SQL_BIND        POLICY_NAME
----- ------------------------------ --------------- ---------------
DEMO  select count(*) from t where d  #1(2):10       T_ACCESS_POLICY
      eptno = :x1

DEMO  select count(*) from t where e  #1(4):KING     T_ACCESS_POLICY
      name = :x2

DEMO  select count(*) from t where h  #1(11):15-Aug- T_ACCESS_POLICY
      iredate < to_date(:x3,'dd-mon- 2019
      yyyy')

DEMO  select count(*) from t where d  #1(2):20       T_ACCESS_POLICY
      eptno = :x1


demo@PDB1>



more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.