How to See the Oracle 10g 11g Bind Variable Values Using v$sql_bind

advertisements

_____________________________________________________________________________________________________________________

Here is the simple query to see the bind variables from Oracle database.

SELECT s.sql_id, s.sql_text SQLTEXT,

bc.name BIND_VAR_NAME,

bc.value_string BIND_VAR_STRING

FROM v$sql s , v$sql_bind_capture bc

WHERE s.sql_id = bc.sql_id

AND bc.value_string IS NOT NULL

Example

SQL> variable owner_name varchar2(15);

SQL> exec :owner_name:='SCOTT';

PL/SQL procedure successfully completed.

SQL> SELECT DISTINCT OBJECT_NAME FROM object_list WHERE OWNER=:owner_name;

SQL> SELECT s.sql_id, s.sql_text SQLTEXT, bc.name BIND_VAR_NAME, bc.value_string BIND_VAR_STRING

FROM v$sql s , v$sql_bind_capture bc

where s.sql_id = bc.sql_id

and bc.value_string IS NOT NULL

and lower(s.sql_text) like '%object_list%'

SQL_ID

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

SQLTEXT

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

BIND_VAR_NAME

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

BIND_VAR_STRING

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

bky7kjtg041ca

SELECT DISTINCT OBJECT_NAME FROM object_list WHERE OWNER=:owner_name

:OWNER_NAME

SCOTT

_____________________________________________________________________________________________________________________

Archive