• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1377
  • Last Modified:

capture oracle bind_variable value_string null


How can I capture bind variable for sql_id. I have tested the following script but the value_string is null and the sattistics level is typical.

select  sql_id,  t.sql_text SQL_TEXT,  b.name BIND_NAME,  b.value_string BIND_STRING
from  v$sql t  join DBA_HIST_SQLBIND b  using (sql_id)
where  b.value_string is not null  and sql_id='d4yj18kvu48j2'

  • 3
1 Solution
slightwv (䄆 Netminder) Commented:
I've not used DBA_HIST_SQLBIND.

Whenever I've looked up bind variables I've used v$sql_bind_capture
dba_hist_sqlbind isn't exhaustive.  It's only captures snapshots - as represented by the SNAP_ID column.

You can try to look in v$sql_bind_capture if you ran your query recently but even that isn't guaranteed.

There's even a column for that "WAS_CAPTURED" which, if NO, means it wasn't captured
If you want to be sure to capture all binds ever used then trace your session with bind capture on then run your query and check the file.

obviously, this isn't something you'd have turned on all the time.
A split is probably in order here.

slightwv also posted  v$sql_bind_capture, but before me
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now