Well the blog has been moved to www.puthranv.com . All of the old articles are also moved.
How to get bind variable values in oracle ?
To retrieve the value of the bind variables V$SQL_BIND_CAPTURE view has to be queried, But does oracle really provides us the bind variables ? lets see in detail
SQL> SELECT * FROM V$VERSION;
BANNER
——————————————————————————
——————————————————————————
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
PL/SQL Release 11.2.0.1.0 – Production
CORE 11.2.0.1.0 Production
TNS for 32-bit Windows: Version 11.2.0.1.0 – Production
NLSRTL Version 11.2.0.1.0 – Production
PL/SQL Release 11.2.0.1.0 – Production
CORE 11.2.0.1.0 Production
TNS for 32-bit Windows: Version 11.2.0.1.0 – Production
NLSRTL Version 11.2.0.1.0 – Production
SQL> SELECT NAME,VALUE FROM V$PARAMETER WHERE NAME like ‘%cursor_sharing%’;
NAME VALUE
————– ———-
cursor_sharing EXACT
————– ———-
cursor_sharing EXACT
SQL> VARIABLE X NUMBER;
SQL> EXEC :X:=10;
SQL> EXEC :X:=10;
PL/SQL procedure successfully completed.
SQL> SELECT ID,ID_VAL FROM BV_CAPTURE WHERE ID = :X;
SQL> SELECT ID,ID_VAL FROM BV_CAPTURE WHERE ID = :X;
ID ID_VAL
———- ———
10 10ORACLE
———- ———
10 10ORACLE
SQL> SELECT SQL_ID,SQL_TEXT,EXECUTIONS AS EXEC,CHILD_NUMBER CHD_NUM FROM V$SQL WHERE SQL_ID = ’4pfw91tshj4yp’;
SQL_ID SQL_TEXT EXEC CHD_NUM
——————– —————————————- —- ——-
4pfw91tshj4yp SELECT ID,ID_VAL FROM BV_CAPTURE WHERE I 1 0
D = :X
——————– —————————————- —- ——-
4pfw91tshj4yp SELECT ID,ID_VAL FROM BV_CAPTURE WHERE I 1 0
D = :X
Lets query V$SQL_BIND_CAPTURE to retrieve the bind variable data
SQL> SELECT SQL_ID,DATATYPE_STRING,NAME,VALUE_STRING,VALUE_ANYDATA FROM V$SQL_BIND_CAPTURE WHERE SQL_ID = ’4pfw91tshj4yp’;
SQL_ID DATATYPE_STRING NAME VALUE_STRING VALUE_ANYDATA()
————– —————- —– ————- —————-
4pfw91tshj4yp NUMBER :X 10 ANYDATA()
————– —————- —– ————- —————-
4pfw91tshj4yp NUMBER :X 10 ANYDATA()
Lets Repeat the same and check what we get,
SQL> EXEC :X:=100;
PL/SQL procedure successfully completed.
SQL> SELECT ID,ID_VAL FROM BV_CAPTURE WHERE ID = :X;
ID ID_VAL
———- ———-
100 100ORACLE
———- ———-
100 100ORACLE
SQL> SELECT SQL_ID,SQL_TEXT,EXECUTIONS AS EXEC,CHILD_NUMBER CHD_NUM FROM V$SQL WHERE SQL_ID = ’4pfw91tshj4yp’;
SQL_ID SQL_TEXT EXEC CHD_NUM
——————– —————————————- —- ——-
4pfw91tshj4yp SELECT ID,ID_VAL FROM BV_CAPTURE WHERE I 2 0
D = :X
——————– —————————————- —- ——-
4pfw91tshj4yp SELECT ID,ID_VAL FROM BV_CAPTURE WHERE I 2 0
D = :X
and now what does V$SQL_BIND_CAPTURE provide us
SQL> SELECT SQL_ID,DATATYPE_STRING,NAME,VALUE_STRING,VALUE_ANYDATA FROM V$SQL_BIND_CAPTURE WHERE SQL_ID = ’4pfw91tshj4yp’;
SQL_ID DATATYPE_STRING NAME VALUE_STRING VALUE_ANYDATA()
————– —————- —– ————- —————-
4pfw91tshj4yp NUMBER :X 10 ANYDATA()
————– —————- —– ————- —————-
4pfw91tshj4yp NUMBER :X 10 ANYDATA()
oh! still sticking to value 10, it has to be 100. The support link Note-444551.1 provides us an work around to get the timestamp values using “anydata.accesstimestamp(value_anydata)” but does anydata.accessnumber help in our case, lets check that too
SQL> SELECT SQL_ID,DATATYPE_STRING,NAME,VALUE_STRING,ANYDATA.ACCESSNUMBER(VALUE_ANYDATA) AS VALUE_ANYDATA FROM V$SQL_BIND_CAPTURE WHERE SQL_ID = ’4pfw91tshj4yp’;
SQL_ID DATATYPE_STRING NAME VALUE_STRING VALUE_ANYDATA
————- —————– —– ———— ————-
4pfw91tshj4yp NUMBER :X 10 10
————- —————– —– ———— ————-
4pfw91tshj4yp NUMBER :X 10 10
Ops! the issue is not with timestamp alone, it is also with Numbers. The run time bind variables are not updated for executions greater than one.
