How to display SQL statement including host variable values in Oracle Pro*C?

Question

In Pro*C code you can get the last executed SQL statement via sqlgls() (or even SQLStmtGetText()).

This is useful for logging purposes - especially for dynamic statements.

But the SQL statement returned by these SQLLIB functions only inlcudes bind markers (i.e. something like :b1, :b2 ...). The do not include the actual values of the used host-variables.

Thus, I have following question: How to I display the last SQL-statement including host-variable values?

Else I have to manually print all used variables after printing the string returned by sqlgls(). Which is not really more convenient than printing the SQL statement without using sqlgls at all.

For example instead of

INSERT INTO MYTABLE VALUES (:b1, :b2, :b3);

I want to print:

INSERT INTO MYTABLE VALUES ("hello", "world", 12);

(besides logging, to make it easier to copy'n'paste it into an SQL-shell - i.e. for testing)

Answer 1

You can use v$sql_bind_capture for tracing bind variable values.

v$sql_bind_capture has been introduced to report information on bind variables used by SQL cursors. This view allows the retrieval of the actual values of bind variables for a given SQL cursor. Moreover, you can get the sql text from v$sqlarea or v$sqltext or v$sqltext_with_newlines.

SELECT 
   a.sql_text, 
   b.name, 
   b.position, 
   b.datatype_string, 
   b.value_string 
FROM
  v$sql_bind_capture b,
  v$sqlarea          a,
  v$session          c, 
WHERE
   c.sid = (select sys_context('USERENV','SID') from dual)
AND
   b.sql_id = c.prev_sql_id
AND 
   b.sql_id = a.sql_id;

You need to try embedded sql on this statement. And get the variable you need to get the full statement. And you need a simple script to get that sql text with binded value, which I think is not more convenient than printing the bind value yourself.

Another way is to run a 10046 level 4 trace, but again you need to do some trics to support the copynpaste feature.