GUIDs in Oracle

We’re starting a new project at work and we’ve decided to use GUIDs as unique identifiers in the tables.  Now, if your database server is SQL Server, this is no big deal: You simply choose the uniqueidentifier column type and you’re in business.  You can read and write that from .NET, use it in ad hoc SELECT statements, etc…

Oracle GUIDs don’t have much to do with the ones from Microsoft software, so let’s not go there.  I started looking for a solution on the Internet and boy, did I see just about every possible solution out there, including storing it as a 37 character string.  Call me crazy, but I wasn’t about to use this as a primary key.

Eventually I found a short post in a lengthy discussion, almost missed it actually, and the solution couldn’t have been simpler.  So here it is.  In this article I’ll explain how to use .NET GUIDs with Oracle, with a few short code samples.  Hopefully it’ll result in some people not wasting as much time on this as I did.

Oracle Data Type for GUIDs

There is no GUID or uniqueidentifier type in Oracle, so what do you use to store a .NET GUID? (If you don’t want to store it as a string, that is.)  A .NET GUID can be passed to Oracle as a 16-byte byte array, so the most efficient (at least I think so) data type is RAW(16).

Writing and reading GUIDs from .NET

You’ll have to convert your .NET GUID to pass it to Oracle.  This is simple, since the GUID class has a ToByteArray method.  This is some Visual BASIC sample code that creates a new GUID and puts it in an outbound parameter – in this example I use the syntax from the Oracle .NET Data Provider, but I’ve also done this using the Microsoft .NET Provider for Oracle, the Microsoft ODBC driver for Oracle, and Oracle’s own ODBC provider.

‘ Create a new GUID
Dim newguid As Guid = Guid.NewGuid

‘ Parameter settings
Dim empid As OracleParameter = _
cmd.Parameters.Add (“in_empid”, OracleDbType.Raw)
empid.Direction = ParameterDirection.Input
empid.Value = newguid.ToByteArray

Pretty simple, isn’t it?  Of course, if you look at the result in the Oracle table, it won’t look like a GUID, but just a string of bytes – the content of the byte array. You can SELECT on that value by enclosing the content of the RAW(16) field in single quotes (a string), and using the HEXTORAW() function.  That’s almost as good as selecting a GUID in SQL Server. 🙂

Reading the GUID back into .NET is quite straightforward as well.  In the code below, dr is a DataReader, and the GUID is the first value returned in the row (hence the reference to the 0 index in GetOracleBinary).  The code displays the GUID on the console:

Console.WriteLine(“GUID read from Oracle table: ” & _
New Guid(CType(dr.GetOracleBinary(0), _
Byte())).ToString)

A little bit cramped, but here’s what’s going on:

  1. We get the content of the RAW(16) field using GetOracleBinary
  2. We cast the value to a Byte array
  3. We create a new GUID object from that Byte array

That’s all there is to it!