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

advertisements

_____________________________________________________________________________________________________________________

The Eucharistic Miracles of the World
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

_____________________________________________________________________________________________________________________

Website Stats

0 comments:

Post a Comment

Labels

Oracle (629) Script (86) General (77) Unix (47) Blog (23) Technology (19) gadget (6) games (6) Business (3) OCI (3) SQL* Loader (3) Datapump (2)
 

acehints.com Copyright 2011-23 All Rights Reserved | Site Map | Contact | Disclaimer