ExpressDBA: How To Find The Value of a Bind Variable Without Tracing [ID 273121.1]

Tuesday, August 10, 2010

contribution Breno Tozo

How To Find The Value of a Bind Variable Without Tracing [ID 273121.1]

	Modified 09-JUL-2005 Type BULLETIN Status ARCHIVED

PURPOSE

-------

In this note we are going to demonstrate how

to find the value of a bind variable without enabling

sql_tracing.

SCOPE & APPLICATION

-------------------

Often the need arises to find the value of a bind variable

for diagnostic purposes.

How To Find The Value of a Bind Variable

----------------------------------------

SQL> variable bind varchar2(20);

SQL> exec :bind := 'SMITH';

PL/SQL procedure successfully completed.

SQL> select * from emp where ename=:bind;

EMPNO ENAME JOB MGR HIREDATE SAL COMM

---------- ---------- --------- ---------- --------- ---------- ----------

DEPTNO

----------

7369 SMITH CLERK 7902 17-DEC-80 800

SQL> select sql_id, sql_text from v$sql where sql_text like 'select * from emp%';

SQL_ID

-------------

SQL_TEXT

--------------------------------------------------------------------------------

d5c75d9t3yf8g

select * from emp where ename=:bind

SQL> select value_STRING from v$sql_bind_capture where sql_id='d5c75d9t3yf8g';

VALUE_STRING

--------------------------------------------------------------------------------

SMITH

Please note that bind capture is disabled when the STATISTICS_LEVEL initialization parameter

is set to BASIC

Pages