Re: v$sql_bind_capture – is it buggy?

On Jun 2, 9:22 pm, zigzag..._at_yahoo.com wrote:
> I am using Oracle 10.2.0.3 on Windows 2003. I am using
> V$sql_bind_capture to find values of bind vars used in my query.
> When I look at the description of v$sql_bind_capture:
> SQL> desc v$sql_bind_capture;
>  Name                                      Null?    Type
>  ----------------------------------------- --------
> ----------------------------
>  ADDRESS                                            RAW(4)
>  HASH_VALUE                                         NUMBER
>  SQL_ID                                             VARCHAR2(13)
>  CHILD_ADDRESS                                      RAW(4)
>  CHILD_NUMBER                                       NUMBER
>  NAME                                               VARCHAR2(30)
>  POSITION                                           NUMBER
>  DUP_POSITION                                       NUMBER
>  DATATYPE                                           NUMBER
>  DATATYPE_STRING                                    VARCHAR2(15)
>  CHARACTER_SID                                      NUMBER
>  PRECISION                                          NUMBER
>  SCALE                                              NUMBER
>  MAX_LENGTH                                         NUMBER
>  WAS_CAPTURED                                       VARCHAR2(3)
>  LAST_CAPTURED                                      DATE
>  VALUE_STRING                                       VARCHAR2(4000)
>  VALUE_ANYDATA                                      SYS.ANYDATA
>
> It has a column was_captured. My query  has 206 bind vars, I see it
> only has value YES for this field for ~40 bind vars. Rest of the
> variables. Its value is NO, as a result VALUE_STRING is not shown
> (i.e., null).
>
> Why is not capturing all the bind variables. Where can I get values of
> all bind variables? I do not want to do sql trace with bind variables,
> I was hoping some v$ provides me all the values.

No, it isn't. I would take the time to read up on the view rather than speculating on whether or not your lack of knowledge makes this object 'buggy' and waiting for someone to respond to your question:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2114.htm#REFRN30310

From the documentation:

"Bind data

One of the bind values used for the bind variable during a past execution of its associated SQL statement. Bind values are not always captured for this view. Bind values are displayed by this view only when the type of the bind variable is simple (this excludes LONG, LOB, and ADT datatypes) and when the bind variable is used in the WHERE or HAVING clauses of the SQL statement."

Bind capture is also disabled when STATISTICS_LEVEL = BASIC.

Funny how you have the time to wait for someone ELSE to read the documentation and report back to you.

David Fitzjarrell Received on Tue Jun 03 2008 - 08:55:16 CDT