Geek asks and answers

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

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

This is useful for logging, especially for dynamic statements.

But the SQL statement returned by these SQLLIB functions includes only anchor labels (e.g. :b1, :b2...). Do not include the actual values ​​of the host variables used.

So I have the following question: How to display the last SQL statement, including the values ​​of the host variable?

Otherwise, I need to manually print all the variables used after printing the string returned sqlgls(). This is not very convenient than printing an SQL statement without using it sqlglsat all.

For example, instead of

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

I want to print:

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

(in addition to logging, to simplify copying, "insert it into the SQL shell, i.e. for testing)

+5
source share
2 answers

You can use v $ sql_bind_capture to track the values ​​of binding variables.

v $ sql_bind_capture was introduced to represent information about the binding variables used by SQL cursors. This view allows you to retrieve the actual values ​​of the binding variables for a given SQL cursor. Moreover, you can get 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;

sql . , . script, sql , , , , .

10046 , copynpaste.

+1

, Pro * C, API/ABI Oracle.

, libtraceproc, - - Oracle - SQL, .

, . , / Oracle.

API OCI, .. OCI/OCCI/OTL.

$ LD_PRELOAD=./libtraceproc.so TRACEPROC_OPTIONS="-intercept -notime -sql" ./example/main
[..]
-- Before execution:
-- example/main.pc:274
insert into example_tbl (str,n) values ('a' ,0 ) returning n into :s5:s6  ;
insert into example_tbl (str,n) values ('b' ,1 ) returning n into :s5:s6  ;
[..]
-- After execution:
-- example/main.pc:274
insert into example_tbl (str,n) values ('a' ,0 ) returning n into 0  ;
insert into example_tbl (str,n) values ('b' ,1 ) returning n into 1  ;
[..]

libtraceproc.so - , main - Pro * C.

, Pro * C . .

$ LD_PRELOAD=./libtraceproc.so TRACEPROC_OPTIONS="-help" ./example/main

.

0

More articles:


All Articles