RAW(16) to Guid conversion issue

RAW(16) to Guid conversion issue

Postby LordFjord » Fri 23 Apr 2010 08:26

I am porting a MS SQL database to oracle and have some trouble getting the Guid conversion from UNIQUEIDENTIFIER to RAW(16) to work.

I am using the Devart.Data.oracle and Devart.Data.Oracle.Linq modules (both trial version for the evaluation, we would buy dotConnect Oracle if things work out).

This is what i have observed:
a generated Guid in C# looks like {a6cec596-7a3d-4730-bce4-822bf7242867}. It is inserted correctly and i can read it out again via C# and LINQ. The column mapping is from System.Guid to RAW(16).

However, in the database it shows up as '96C5CEA63D7A3047BCE4822BF7242867' if i run a simple query in Oracle SQL Developer.

The bytes in the first 3 parts of the Guid are inverted.
I have found the same issue described here. (not related to dotConnect Oracle, but the same conversion issue)

My problem is, that the Guids are stored in a different way and i can't access them in an easy and comfortable way for debugging purposes.
If i stay in the C# and LINQ world, it is consistent.

If I only try to make simple insert and select queries on SQL level it is also fine, i.e. an insert with value 'ADC463D2F8FB4D8BAB2AB8F7EF3BB718' for the RAW(16) column will be returned as the same value on a select.

I need to access the Guids from both ways, preferably in the same format.
Is this a known issue or is there a known workaround?
Posts: 18
Joined: Thu 22 Apr 2010 07:55

Postby LordFjord » Fri 23 Apr 2010 09:43

I have found a workaround. The reason for the different Guid format is that the 1st 3 parts of a Guid are stored as ints in littleendian format -> in C# a Guid.ToByteArray() also provides them in the littleendian format while .ToString() returns the formatted value.

Here are the hints for reference:
hint #1
hint #2

For now, I am using a workaround in the skripts on SQL level that reformats the old Guids.
Posts: 18
Joined: Thu 22 Apr 2010 07:55

Return to dotConnect for Oracle