Laurent Schneider

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)

This entry was posted on Wednesday, May 2nd, 2007 at 13:47 and is filed under oracle, sql, dba. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.
Post to : del.icio.us - Digg it - Furl - reddit - Spurl

Put your code in <code> and </code> tags

5 Responses to “v$sql and bind variable”

Dominic Says:
May 2nd, 2007 at 15:15
And the fact that the datatype_string is a varchar2(2000) as opposed to varchar2(255) ties into these posts by Jonathan Lewis and Tom Kyte.
Ligarius Says:
May 2nd, 2007 at 19:04
Hi Laurent…

Very useful!!

Regards from Santiago , Chile, South America

http://ligarius.wordpress.com
Coskan Says:
May 3rd, 2007 at 07:37
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
youngleei Says:
June 15th, 2007 at 02:48
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.. ^^
Laurent Schneider Says:
June 20th, 2007 at 13:07
check what Tom Kyte has to say about it
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4254158186083

Use <code> and </code> to post code

v$sql and bind variable

5 Responses to “v$sql and bind variable”

Leave a Reply