v$sql and bind variable

When you see something like 

select * from t where x = :1

you may wonder what is :1

Ok, here is a quick join I tested in 10gR2

SQL> var y varchar2(255)
SQL> exec :y:='SCOTT'

PL/SQL procedure successfully completed.

SQL> select job from emp where ename=:y;
JOB
---------
ANALYST

SQL> select sql_text,name,value_string,datatype_string
  2  from v$sql_bind_capture join v$sql using (hash_value)
  3  where sql_text like
  4    'select job from emp where ename=:y%';
SQL_TEXT                              NAME VALUE DATATYPE_STRING
------------------------------------- ---- ----- ---------------
select job from emp where ename=:y    :Y   SCOTT VARCHAR2(2000)

5 thoughts on “v$sql and bind variable”

  3. Coskan

    To keep in mind;
    If you have license to use AWR than the snapshot collections of this view is located in DBA_HIST_SQLBIND view

  4. youngleei

    HI~~

    this is very useful..

    BUT v$sql_bind_capture is available only in 10g.

    in 8i or 9i, How can i get bind variable to use select query?

    please help me.. ^^

Comments are closed.