One of the bind values used for the bind variable during a past execution of its associated SQL statement. Internal identifier for the bind datatype, Textual representation of the bind datatype, Indicates whether the bind value was captured (. Each row in the view contains information for one bind variable defined in a cursor. This view can be joined with V$SQLAREA on (HASH_VALUE, ADDRESS) and with V$SQL on (HASH_VALUE, CHILD_ADDRESS). This includes: ( hash_value, address) for the parent cursor and ( hash_value, child_address) for the child cursor. This includes: Reference to the cursor defining the bind variable. (hash_value, address) for the parent cursor and (hash_value, child_address) for the child cursor. In 10g, I can see the value of bind variable to use v$sql_bind_capture. Just went through the documentation and it clearly says. Asked: June 14, 2002 - 10:18 am UTC. V$SQL_BIND_CAPTURE displays information on bind variables used by SQL cursors. SQL identifier of the parent cursor in the library cache, Position of the bind variable in the SQL statement. The SQL text does not contain the actual value of the bind variables. Scripting on this page enhances content navigation, but does not change the content in any way. Thanks for the question, Antonio. Bind values are captured when SQL statements are executed. BUT in 9i or 8i v$sql_bind_capture is not available. It looks like you're new here. For each distinct bind variable in each cursor owned by the session querying this view, this view describes: Actual bind data, if the bind variable is user defined, The underlying literal, if the CURSOR_SHARING parameter is set to FORCE and the bind variable is system generated. I want to know the bind value in sql command, in 10g I can join v$sql_bind_capture to fine the value. I have written the following query to get the last executed SQL statement in the oracle database for a particular session. 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. i want know that value by using select query , not using trace. Date when the bind value was captured. V$SQL_BIND_CAPTURE displays information on bind variables used by SQL cursors. My mistake. In 10g, I can see the value of bind variable to use v$sql_bind_capture. Sobre Alexandre Pires ORACLE OCS Goldengate Specialist, OCE RAC 10g R2, OCP 12C, 11g, 10g , 9i e 8i - Mais de 25 anos de experiência na área de TI. No. The hash value is a fixed index for the view and should always be used to speed up the access to the view. Answered by: Tom Kyte - Last updated: August 02, 2013 - 5:32 pm UTC But in 9i, there is no v$sql_bind_capture. Name, position, datatype, character set ID, precision, scale, and maximum length of the bind variable. I was working on a database with no applications running against the same. Value of the bind represented as a string, Value of the bind represented using the self-descriptive. I only see a value "0" in all columns of this view on 10.2.0.3.0 apart from cursor_num column. Please note that if the shared_cursors init.ora parameter is set to similar FGA do not capture the exact values of the bind variables. If you want to get involved, click one of these buttons! Bind capture is disabled when the STATISTICS_LEVEL initialization parameter is set to BASIC. (System-generated binds have a value of 256 in the SHARED_FLAG2 column. ), Shared maximum length for this bind from the shared cursor object associated with this bind, Private maximum length for this bind sent from the client, Maximum number of array elements (for array binds only). Column Datatype Description; CURSOR_NUM: NUMBER: Cursor number for this bind: POSITION: NUMBER: Bind position: DATATYPE: NUMBER: Bind datatype: SHARED_MAX_LEN: NUMBER If the binding is performed by name and the bind variable is duplicated, then this column gives the position of the primary bind variable. The only way I think is to enable the 10046 trace with level 4 which gives the value of the bind variables for the given query. Hi, Can we write a PL/SQL procedure in Oracle 9i to capture the values of bind variables in the dba_fga_audit_trail table. Hash value of the parent cursor in the library cache. Each row in the view contains information for one bind variable defined in a cursor. Name, position, datatype, character set ID, precision, scale, and maximum length of the bind variable. Bind values are not always captured for this view. Is there Software in Silicon (Sample Code & Resources). Well, very useful... Only if your application can forget what it was doing five minutes ago and you want to give it a change to remember. NAME , VALUE and DATATYPE_STRING. Each row in the view contains information for one bind variable defined in a cursor. Each row in the view contains information for one bind variable defined in a cursor. You are right. V$SQL_BIND_CAPTURE displays information on bind variables used by SQL cursors. Anything wrong here? To limit the overhead, binds are captured at most every 15 minutes for a given cursor. Apart from extended SQL trace, there is no method which will give you the bind variable value. Sorry.