Puthran's Oracle Blog

Find bind variable values in oracle August 24, 2012

 Well the blog has been moved to www.puthranv.com . All 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

SQL> SELECT NAME,VALUE FROM V$PARAMETER WHERE NAME like ‘%cursor_sharing%’;

NAME VALUE
————– ———-
cursor_sharing EXACT

SQL> VARIABLE X NUMBER;
SQL> EXEC :X:=10;

PL/SQL procedure successfully completed.
SQL> SELECT ID,ID_VAL FROM BV_CAPTURE WHERE ID = :X;

ID ID_VAL
———- ———
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

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()

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

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

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()

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

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.

Varchar2 also has the same issue. Have raised an SR 3-6119554511 and lets see that happens –