What is the difference between converting to hex on the client end and using rawtohex?

Question

I have a table that's created like this:

CREATE TABLE bin_test
(id INTEGER PRIMARY KEY, b BLOB)

Using Python and cx_Oracle, if I do this:

value = "\xff\x00\xff\x00" #The string represented in hex by ff00ff00
self.connection.execute("INSERT INTO bin_test (b) VALUES (rawtohex(?))",
                        (value,))
self.connection.execute("SELECT b FROM bin_test")

I eventually end up with a hex value of a000a000, which isn't correct! However if I do this:

import binascii
value = "\xff\x00\xff\x00"
self.connection.execute("INSERT INTO bin_test (b) VALUES (?)",
                        (binascii.hexlify(value,)))
self.connection.execute("SELECT b FROM bin_test")

I get the correct result. I have a type conversion system here, but it's a bit difficult to describe it here. Thus, can someone point me in the right direction as to whether I'm doing something wrong at the SQL level or whether something weird is happening with my conversions?

Answer 1

RAWTOHEX in Oracle is bit order insensitive, while on your machine it's of course sensitive.

Also note that an argument to RAWTOHEX() can be implicitly converted to VARCHAR2 by your library (i. e. transmitted as SQLT_STR), which makes it also encoding and collation sensitive.

Answer 2

rawtohex() is for converting Oracles RAW datatypes to hex strings. It's possible it gets confused by you passing it a string, even if the string contains binary data. In this case, since Oracle expects a string of hex characters, give it a string of hex characters.

Answer 3

I usually set the proper type of variable bindings specially when trying to pass an Oracle kinda of RAW data type into a query.

for example something like:

self.connection.setinputsizes(cx_Oracle.BINARY)
self.connection.execute(
    "INSERT INTO bin_test (b) VALUES (rawtohex(?))",
    (value,)
)