CODEWITHSUNDEEP
oracle-databasebindtrace
Ken H
Ken H

Reputation: 476

Query Oracle for running sql and value of bind variables

If I run the SQL in Fig. 1 below, it may return something like this: 

Select fname, lname from name_tbl where nam_key = :key

Without using some fancy DBA trace utility, how can I query an Oracle system table to find the value of the bind variable “:key”?

Figure 1. - List the current running sql statement. 

select sid, username, sql_text  
from v$session,   
       v$sqltext  
 where sql_address    = address   
   and sql_hash_value = hash_value  
order by sid, piece;

Upvotes: 7

Views: 10002

Answers (1)

Lev Khomich
Lev Khomich

Reputation: 2247

select name, value_string
from v$sql_bind_capture
where sql_id = your_query_id

Upd. or, of course:

select sql_id, value_string
from v$sql_bind_capture
where name = ':key'

Upvotes: 6

Related Questions