Guid in oracle

Guid is sturcture, ToString will generate different string.

I'm passing a GUID as an Oracle Parameter using this :

sqlParm = new OracleParameter();
sqlParm.ParameterName = prm.ParamName;
sqlParm.OracleDbType = OracleDbType.RAW
sqlParm.Direction = prm.ParamDirection;
sqlParm.Size = 16;
sqlParm.Value = ((Guid)prm.ParamValue).ToByteArray();

cmd.Parameters.Add(sqlParm)

The Guid.ToString() is 32750000-0000-0000-0400-1f262144bf86

In SQLPlus I however see this as 000075320000000004001F262144BF86

When the code reaches the cmd.ExecuteReader() I get an exception saying "OracleCommand.CommandText is invalid"

The sql sent at this stage is

Select Distinct(prj.Name) as PrjName, prj.oid as PrjOid
From ERLS.PRJMGTProjectRoot prj, ERLS.CORERELATIONDEST ic, ERLS.PRJMGTDatabase pd
Where prj.oid=ic.oidTarget And ic.oid=pd.oid And pd.oid= :plant_oid

Is there something else I need to do when I pass a GUID as parameter. ?

thanks
======================================================================
It looks like you're inserting the correct way. As far as display goes, a GUID is a struct, as opposed to simply a string of bytes. If you iterate through the byte array you'll see in essence that the bytes stored in oracle are the raw bytes of the byte [], and the order of the raw bytes are not the order displayed by guid.ToString().
A display difference is that when selecting from sqlplus, 0 displays as 00.

With respect to your error "OracleCommand.CommandText is invalid", I'd supect that that's just a logic error in your app not related to guids. You can get that error, for example if you simply dont set cmd.CommandText

Anyway, here's the code I tested with, hope it helps.

Greg




class Program
{
static void Main(string[] args)
{
System.Guid guid = new System.Guid("32750000-0000-0000-0400-1f262144bf86");
Console.WriteLine("original guid string is {0}", guid.ToString());

using (OracleConnection con = new OracleConnection())
{
con.ConnectionString = "user id=scott;password=tiger;data source=orcl";
con.Open();
using (OracleCommand cmd = new OracleCommand())
{
cmd.CommandText = "insert into testguid values(:1)";
cmd.Connection = con;
OracleParameter sqlParm = new OracleParameter();
sqlParm.ParameterName = "myguid";
sqlParm.OracleDbType = OracleDbType.Raw;
sqlParm.Direction = ParameterDirection.Input;
sqlParm.Size = 16;
byte[] b1 = guid.ToByteArray();
for (int i = 0; i < b1.Length; i++)
{
Console.Write("{0:X} ", b1 );
}
sqlParm.Value = b1;
cmd.Parameters.Add(sqlParm);
cmd.ExecuteNonQuery();

cmd.CommandText = "select col1 from testguid";
OracleDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
byte[] b = new byte[16];
rdr.GetBytes(0, 0, b, 0, 16);
Guid newguid = new Guid(b);
Console.WriteLine("/n new guid is " + newguid.ToString());
}
}
}
}
}



OUTPUT
============
original guid string is 32750000-0000-0000-0400-1f262144bf86
0 0 75 32 0 0 0 0 4 0 1F 26 21 44 BF 86
new guid is 32750000-0000-0000-0400-1f262144bf86


SQLPLUS
========
SQL> select * from testguid;

COL1
--------------------------------
000075320000000004001F262144BF86