Convert byte array from Oracle RAW to System.Guid?

Question

My app interacts with both Oracle and SQL Server databases using a custom data access layer written in ADO.NET using DataReaders. Right now I'm having a problem with the conversion between GUIDs (which we use for primary keys) and the Oracle RAW datatype. Inserts into oracle are fine (I just use the ToByteArray() method on System.Guid). The problem is converting back to System.Guid when I load records from the database. Currently, I'm using the byte array I get from ADO.NET to pass into the constructor for System.Guid. This appears to be working, but the Guids that appear in the database do not correspond to the Guids I'm generating in this manner.

I can't change the database schema or the query (since it's reused for SQL Server). I need code to convert the byte array from Oracle into the correct Guid.

Answer 1

It turns out that the issue was the byte order you get in Guid.ToByteArray() and not Oracle itself. If you take the Guid "11223344-5566-7788-9900-aabbccddeeff" and call ToByteArray() on it, you get "44332211665588779900AABBCCDDEEFF". If you then pass that byte array back into the constructor for Guid, you get the original Guid. My mistake was trying to query the Oracle database by the original Guid format (with the dashes removed) instead of the result of the ToByteArray() call.

I still have no idea why the bytes are ordered that way, but it apparently has nothing to do with Oracle.

Answer 2

I have vague recollections that the GUIDs from Oracle are effectively reversed compared with the order .NET expects.

Try reversing the array before calling the Guid constructor.

It may not be quite as simple as reversing, however - you may need to do more detailed swapping. I suggest you create a GUID where each byte is easy to identify (use 0x01, 0x23, 0x45 etc) and work from there.

Answer 3

I just had this same issue when storing and reading Guids from Oracle.

If your app needs to store and read Guids from Oracle, use the FlipEndian function from this thread:

.NET Native GUID conversion

Byte[] rawBytesFromOracle;
Guid dotNetGuid = new Guid(rawBytesFromOracle).FlipEndian();

The flip is only required when reading back from Oracle.

When writing to Oracle use Guid.ToByteArray() as normal.

I spent TOO much time trying to get this simple task accomplished.

Steve