CODEWITHSUNDEEP
pythonsqloracle-databasecx-oracle
bioffe
bioffe

Reputation: 6383

Actual SQL statement after bind variables specified

I am trying to log every SQL statement executed from my scripts. However I contemplate one problem I can not overcome.

Is there a way to compute actual SQL statement after bind variables were specified. In SQLite I had to compute the statement to be executed manually, using code below:

def __sql_to_str__(self, value,args):
    for p in args:
        if type(p) is IntType or p is None:
            value = value.replace("?", str(p) ,1)
        else:
            value = value.replace("?",'\'' + p + '\'',1)
    return value

It seems CX_Oracle has cursor.parse() facilities. But I can't figure out how to trick CX_Oracle to compute my query before its execution.

Upvotes: 2

Views: 2055

Answers (3)

Dave Costa
Dave Costa

Reputation: 48111

You might want to consider using Oracle's extended SQL trace feature for this. I would recommend starting here: http://carymillsap.blogspot.com/2011/01/new-paper-mastering-performance-with.html.

Upvotes: 0

ʇsәɹoɈ
ʇsәɹoɈ

Reputation: 23469

Your best bet is to do it at the database server, since a properly implemented Oracle connector will not put the bind variables into a string before sending the query to the server. See if you can find an Oracle server setting that makes it log the queries it executes.

Upvotes: 1

nosklo
nosklo

Reputation: 222852

The query is never computed as a single string. The actual text of the query and the params are never interpolated and don't produce a real full string with both.

That's the whole point of using parameterized queries - you separate the query from the data - preventing sql injections and limitations all in one go, and allowing easy query optimization. The database gets both separately, and does what it needs to do, without ever joining them together.

That said, you could generate the query yourself, but note that the query you generate, although probably equivalent, is not what gets actually executed on the database.

Upvotes: 7

Related Questions