GUIDs
ODP.NET does support GUIDs. GUIDs can be inserted into a RAW(16) column which is big enough to hold any GUID value. But caution needs to be taken in order to handle GUIDs appropriately. This is due to the fact that as the .NET Guid structure flips the byte values in reverse order for the integer-based parts of the GUID values when Guid(byte[ ]) constructor is used and when the ToByteArray() method on the Guid struct is invoked.
When the Guid is provided to ODP.NET as an input parameter, for example, ODP.NET invokes the ToByteArray() method before passing the values to the Oracle Database as raw bytes. This means that if the Guid originally has a sequence of bytes of
9D4E51F764A940E4854D667F0DD61093
then the byte[ ] representation of the GUID would be changed to
F7514E9DA964E440854D667F0DD61093
after the ToByteArray() method invocation that is invoked by ODP.NET internally before it's inserted into the RAW(16) column.
And when the same column value is then retrieved from the Oracle database, the value that is obtained is
F7514E9DA964E440854D667F0DD61093
which is how it was stored in the database. When the GetGuid() method is invoked on the OracleDataReader object, ODP.NET constructs the Guid structure using the Guid(byte[ ]) constructor, which flips it back to the original sequence of bytes:
9D4E51F764A940E4854D667F0DD61093
This means that the sequence of bytes of the GUID in the application is not in the exact same order as how it is stored in the database. In other words, if the application executes a SQL using literal byte values, such as
select * from ... where <raw/guid_column> = '9D4E51F764A940E4854D667F0DD61093';
there will be no rows returned. The same GUID was inserted into the table as:
F7514E9DA964E440854D667F0DD61093
The application will be able to query for the row with the matching GUID if the application
-
binds the guid value using Guid structure as an input parameter
-
constructs the byte literal value that is to be used in the SQL from the
byte[]returned from theToByteArray()method invocation on the Guid structure.
The application developer should be cautious and mindful as to when the Guid(byte[ ]) constructor and the ToByteArray() method of the Guid structure is called, which can alter the sequence of the bytes. A simple program below demonstrates how the integer-based parts of the GUID values are flipped when Guid(byte[ ]) constructor and when the ToByteArray() method are invoked.
using System;
using System.Text;
using System.Data;
using Oracle.ManagedDataAccess.Client;
class T
{
static string ByteToString(byte[] data)
{
StringBuilder sb = new StringBuilder(16);
foreach (var b in data)
sb.Append($"{b:X2}");
return sb.ToString();
}
static void Main()
{
try
{
OracleConnection con = new OracleConnection("user id=<user id>;password=<password>;data source=<data source>");
con.Open();
//
// Generate a new GUID
//
Guid guid = Guid.NewGuid();
string original = guid.ToString().ToUpper().Replace("-", "");
Console.WriteLine("Original Guid : " + original);
//
// Drop the table
//
OracleCommand cmd = new OracleCommand("drop table test_guid_table", con);
try { cmd.ExecuteNonQuery(); } catch {}
//
// Create the table
//
cmd.CommandText = "create table test_guid_table (col1 RAW(16), col2 VARCHAR2(64))";
cmd.ExecuteNonQuery();
//
// Insert the newly generated GUID to the DB
//
cmd.CommandText = "insert into test_guid_table values (:1, 'new guid')";
cmd.Parameters.Add(string.Empty, OracleDbType.Raw);
cmd.Parameters[0].Value = guid;
cmd.ExecuteNonQuery();
//
// Query from the test table
//
cmd.CommandText = "select * from test_guid_table";
OracleDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
//
// Get the RAW data as byte[]
//
byte[] guid_byte_array = (byte[])reader.GetValue(0);
Console.WriteLine("GetValue() as byte[] / as-is in DB: " + ByteToString(guid_byte_array));
Console.WriteLine();
//
// Get the RAW data as Guid then convert to byte[]
//
Guid retrieved_guid = (Guid)reader.GetGuid(0);
byte[] retrieved_guid_byte_array = retrieved_guid.ToByteArray();
Console.WriteLine("GetGuid() then Guid.ToString() : " + retrieved_guid_byte_array.ToString());
Console.WriteLine("GetGuid() then Guid.ToByteArray() : " + ByteToString(retrieved_guid.ToByteArray()));
}
//
// Find a matching row by binding the original GUID as-is
//
cmd.Parameters.Clear();
Console.WriteLine("\nGuid Input Parameter : " + ByteToString(guid.ToByteArray()));
cmd.CommandText = "select count(*) from test_guid_table where col1 = :1";
cmd.Parameters.Add(string.Empty, OracleDbType.Raw);
cmd.Parameters[0].Value = guid;
reader = cmd.ExecuteReader();
while (reader.Read())
{
Console.WriteLine("Rows found by binding GUID : " + (decimal)reader.GetValue(0));
}
//
// Find a matching row by binding a byte[] from the original GUID
//
byte[] byte_array_param = guid.ToByteArray();
Console.WriteLine("\nbyte[] Input Parameter : " + ByteToString(byte_array_param));
cmd.CommandText = "select count(*) from test_guid_table where col1 = :1";
cmd.Parameters.Clear();
cmd.Parameters.Add(string.Empty, OracleDbType.Raw);
cmd.Parameters[0].Value = byte_array_param;
reader = cmd.ExecuteReader();
while (reader.Read())
{
Console.WriteLine("Rows found by binding byte[] : " + (decimal)reader.GetValue(0));
}
//
// Find a matching row by matching the binary/raw data (inline) using Guid.ToByteArray()
//
cmd.CommandText = "select count(*) from test_guid_table where col1 = '" + ByteToString(guid.ToByteArray()) + "'";
Console.WriteLine("\nLiteral RAW (from byte array) : " + ByteToString(guid.ToByteArray()));
cmd.Parameters.Clear();
reader = cmd.ExecuteReader();
while (reader.Read())
{
Console.WriteLine("Rows found by inlined data : " + (decimal)reader.GetValue(0));
}
//
// Find a matching row by matching the binary/raw data (inline) using Guid.ToString()
//
cmd.CommandText = "select count(*) from test_guid_table where col1 = '" + original + "'";
Console.WriteLine("\nLiteral RAW (from string) : " + original);
cmd.Parameters.Clear();
reader = cmd.ExecuteReader();
while (reader.Read())
{
Console.WriteLine("Rows found by inlined data : " + (decimal)reader.GetValue(0));
}
} catch (Exception ex) { Console.WriteLine(ex); }
}
}
A sample output from the sample code could be:
Original Guid : D54909F4169541CFA919F6752414909F GetValue() as byte[] / as-is in DB: F40949D59516CF41A919F6752414909F GetGuid() then Guid.ToString() : System.Byte[] GetGuid() then Guid.ToByteArray() : F40949D59516CF41A919F6752414909F Guid Input Parameter : F40949D59516CF41A919F6752414909F Rows found by binding GUID : 1 byte[] Input Parameter : F40949D59516CF41A919F6752414909F Rows found by binding byte[] : 1 Literal RAW (from byte array) : F40949D59516CF41A919F6752414909F Rows found by inlined data : 1 Literal RAW (from string) : D54909F4169541CFA919F6752414909F Rows found by inlined data : 0
Note:
Every execution of the test generates a new or different GUID.