Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Getting the Oracle Statement from OCI

Re: Getting the Oracle Statement from OCI

From: John K. Hinsdale <hin_at_alma.com>
Date: 23 Jan 2007 14:49:19 -0800
Message-ID: <1169592559.474705.140130@s48g2000cws.googlegroups.com>


faceman28208_at_yahoo.com wrote:

Hi Faceman ...

> Is there any way to get the text of the Oracle SQL statement as
> executed by OCI?

Actually, the "text of the statement, as executed" is exactly the text you give OCI, including the placeholders. Oracle does NOT transform the text of the SQL, substituting the placeholder variables with values as you might think.

> When I use the OCI_ATTR_STATEMENT attribute, I get all the placeholders
> (e.g. ":XYZ").

Yes, that is the expected behavior.

> For debugging purposes need the text of the statement with the
> parameters filled in with the values I gave it.

This is not something you can get from Oracle, as Oracle does NOT transform the text of the SQL, substituting the placeholder variables with values as you might think. In essence, the values are substituted AFTER the SQL is compiled. But you can still do what you want...

Presumably you have the mapping of the placeholder names and their values in some "C" data structure? and are supplying these name/value pairs to OCI with OCIBindByName() or similar, right?

The simplest way to issue diagnostic information when something goes awry is to output the SQL text, including placeholders, just as you gave it to OCIStmtPrepare() or similar, together with a printout of the name/value pairs. The name/value mapping you need to print from YOUR data structure. There is no way to "get it back" from OCI. It will be up to the viewer of the debug message to do the "substituting." This is about the best you can do.

Below is an example of the message my Oracle OCI driver for the CLISP language gives on error in SQL query with parameters. If you want to see the code that produces this, you can download the CLISP source and see the "C" file under the subdir modules/oracle. It contains an entire OCI interface. The link is
http://sourceforge.net/projects/clisp/
Or, see Tim Bunce's OCI interface for perl ("DBD::Oracle") at: http://search.cpan.org/search?query=DBD::Oracle

Hope this helps.
John Hinsdale

-=-=-=-=-=-=-= Example -=-=-=-=-=-

[10]> (oracle:run-sql "select :a || :b fro dual" '(("a" "value-of-a") ("b" "b-value")))

-=-=-=-=-=-=-= Example -=-=-=-=-=- Received on Tue Jan 23 2007 - 16:49:19 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US