Oracle RAW datatype problem

[Davidb]

Hi,

We use uniqueidentifier as the primary key for our records. We are storing
them in UniqueIdentifier columns in SQL Server and in RAW(16) columns in
Oracle. We use the HEXTORAW function to insert the value in the Oracle
database.

My problem occurs when I read back the record with OracleClient. I'm using a
DataReader to fetch the records (using a simple SELECT * FROM TABLE
statement). Then, I want to convert the ID value to the System.Guid type. I
thought that would be simple using the reader.GetBytes() method and then
creating a Guid using the resulting byte array. As silly as it might sound,
the bytes are not ordered correctly in the array compared to the original id
(guid). The byte at index 0 should be at index 3, index 1 at index 2 and so
on for the first four bytes. The next 2 pair of bytes are also inverted
(index 4-5 and 6-7).

Example:
Original id = 9726cac0-3886-4b54-bfc4-bd66209b0714
Fetched from db = c0ca2697-8638-544b-bfc4-bd66209b0714

Does any one as ever encoutered this problem????

For now I simply added a small procedure that switch the bytes and place
them in the correct order within the array, but I'm not sure it will work in
all situation.

FYI, if I use a statement like "SELECT RAWTOHEX(ID) FROM TABLE" the returned
value is equal to the original Guid value. But unfortunately I'm loosing
database portability with that type of query because RAWTOHEX is an
Oracle-only function. It also lead me to believe that the problem is more
related to odp.net that to the db itself.

Thanks,

David B.

 

[Paul Clement]

¤ Hi,
¤
¤ We use uniqueidentifier as the primary key for our records. We are storing
¤ them in UniqueIdentifier columns in SQL Server and in RAW(16) columns in
¤ Oracle. We use the HEXTORAW function to insert the value in the Oracle
¤ database.
¤
¤ My problem occurs when I read back the record with OracleClient. I'm using a
¤ DataReader to fetch the records (using a simple SELECT * FROM TABLE
¤ statement). Then, I want to convert the ID value to the System.Guid type. I
¤ thought that would be simple using the reader.GetBytes() method and then
¤ creating a Guid using the resulting byte array. As silly as it might sound,
¤ the bytes are not ordered correctly in the array compared to the original id
¤ (guid). The byte at index 0 should be at index 3, index 1 at index 2 and so
¤ on for the first four bytes. The next 2 pair of bytes are also inverted
¤ (index 4-5 and 6-7).
¤
¤ Example:
¤ Original id = 9726cac0-3886-4b54-bfc4-bd66209b0714
¤ Fetched from db = c0ca2697-8638-544b-bfc4-bd66209b0714
¤
¤ Does any one as ever encoutered this problem????
¤
¤ For now I simply added a small procedure that switch the bytes and place
¤ them in the correct order within the array, but I'm not sure it will work in
¤ all situation.
¤
¤ FYI, if I use a statement like "SELECT RAWTOHEX(ID) FROM TABLE" the returned
¤ value is equal to the original Guid value. But unfortunately I'm loosing
¤ database portability with that type of query because RAWTOHEX is an
¤ Oracle-only function. It also lead me to believe that the problem is more
¤ related to odp.net that to the db itself.

You have to keep in mind that by using the RAW datatype you're using an Oracle specific
implementation. There is no guarantee that the handling of this datatype would be the same in
another database platform. The same would apply to the use of GUID. Implementations are highly
likely to be platform specific.

Since a GUID is fixed length you may want to consider using a Oracle CHAR column instead.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)

 

[Davidb]

Using a CHAR(32) columns in Oracle was my first idea. However, our DBA told
us that would waste 16 bytes of disk space for every single row and for each
foreign key and suggested that we should iuse RAW(16) instead. Imagine a
database with 2 x 1 million rows tables. That cause a lot of disk space to
be used to store Guids. But, storing 2 million rows requires a lot of space
anyway!

Using CHAR(32) column would be great since any RDBMS I can think of support
this data type.


"Paul Clement" <[email protected]> a écrit dans le
message de > You have to keep in mind that by using the RAW datatype you're
using an Oracle specific