Oracle guid to other table

At the moment 开发者_运维知识库I'm writing a small conversion program, it will convert the primary key strategy to the using of GUIDs in stead of integers. This is a simple client induced requirement and I can't change that.

I've added a substitute pk candidate of the RAW(16) to every table in the database and filled each record with a SYS_GUID(). I did the same for the FKs, I added a substitute column for each FK. Now I'm in the process of linking the FKs to their PKs, by querying the parent table I get the guid/new key for the specific row, after that I want to insert into the substitute candidate FK in the child table.

Somewhat like this:

sqlString = "SELECT PK FROM " + t+ " WHERE " + fkcol+ " = " + childValue;
OracleDataReader guidReader = GetDataReader(sqlString);

while (guidReader.Read())
{
    sqlString = "UPDATE T SET FK = " + guidReader["PK"];
}

Debugging this sqlString gets me the following value:

UPDATE SIS_T_USER SET FK_C_EMPLOYEE_ID = System.Byte[]

Now, how do I go forth and save this as a nice guid in my oracle database?

EDit how:

OracleCommand command = new OracleCommand(sqlString, this.oracleConnection);
                                command.CommandType = CommandType.Text;
                                OracleParameter op1 = new OracleParameter("guid", OracleDbType.Raw);
                                op1.Value = guidReader["PK"];
                                command.Parameters.Add(op1);
                                try
                                {
                                    command.ExecuteNonQuery();
                                }
                                catch (OracleException oex)
                                {
                                    Console.WriteLine("Unable to update: {0}", oex.Message);
                                }

---

Why don't you just do this all on Oracle side?

MERGE
INTO    sis_t_user s
USING   employee e
ON      (s.integer_fk = e.integer_pk)
WHEN MATCHED THEN
UPDATE
SET     s.guid_fk = e.guid_pk

---

Try this code:

sqlString = "UPDATE T SET FK = '" + (new Guid((byte[])guidReader["PK"])).ToString() + "'";

Basically, you just need to create guid from bytes and then convert it to string. There is Guid constructor that allows it: http://msdn.microsoft.com/en-us/library/90ck37x3(v=VS.100).aspx.