Re: v$sql_bind_capture – is it buggy?

From: <fitzjarrell_at_cox.net>
Date: Tue, 3 Jun 2008 06:55:16 -0700 (PDT)
Message-ID: <052a1204-a596-4649-a4d8-2223dfab25b7@z66g2000hsc.googlegroups.com>


On Jun 2, 9:22pm, 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

Original text of this message