Cannot lookup row in database by UUID RAW(32)

I have the following table:

create table Mike_Test
( Id          raw(32)      default sys_guid() not null primary key,
  Value        varchar2(80) not null unique
);

I now insert into this tab开发者_StackOverflow中文版le:

INSERT INTO Mike_Test (VALUE) VALUES ('Blah');

I can confirm there's a new row in there:

select * from Mike_Test;

And I see:

08364fc81419429d83c9bcedb24a9a57    Blah

Now I try to SELECT that row with:

select * from Mike_Test WHERE ID='08364fc81419429d83c9bcedb24a9a57';

However, I get zero rows and zero errors. What am I doing wrong?

It's a RAW datatype, you have to query like this:

select .... where id=hextoraw('08364fc81419429d83c9bcedb24a9a57') ....

I think you might need to use the HEXTORAW function around your ID value. This function converts a hex string into the corresponding RAW value.

In other words, your query should look like the following:

select * from Mike_Test WHERE ID=HEXTORAW('08364fc81419429d83c9bcedb24a9a57');

Heh, I did some more toying and the answer is pretty silly. It seems Oracle will implicitly cast a string to a GUID (like a normal human being would expect), but the casts are case-sensitive! Which makes absolutely zero sense if you're parsing hexidecimal numbers and casing isn't relative to your base. The reason this was throwing me off was because I was using Aqua Data Studio, which for some reason displays GUIDs in all lower case.

The following is from SQLPlus, which handles the output correctly:

SQL> select * from Mike_Test;

ID
--------------------------------
VALUE
--------------------------------------------------------------------------------
4FBD50C370BC4A7F85E3DF034D120930
Blah


SQL> select * from Mike_Test WHERE ID='4fbd50c370bc4a7f85e3df034d120930';

no rows selected

SQL> select * from Mike_Test WHERE ID='4FBD50C370BC4A7F85E3DF034D120930';

ID
--------------------------------
VALUE
--------------------------------------------------------------------------------
4FBD50C370BC4A7F85E3DF034D120930
Blah

Oracle gets sillier each day I use it, I swear.

Adding to my answer (I didn't want to EDIT my original answer since this is such a tangent):

There's huge performance differences between the implicit cast I was trying to do, and the recommended HEXTORAW approach. The query plan for the implicit cast looks something like:

SELECT STATEMENT    3.0 3   37877   1   52  3                   ALL_ROWS                                            
TABLE ACCESS (FULL) 3.0 3   37877   1   52  1   TPMDBO  MIKE_TEST   FULL    TABLE       1

And HEXTORAW is:

SELECT STATEMENT    1.0 1   15463   1   52  1                   ALL_ROWS                                            
TABLE ACCESS (BY INDEX ROWID)   1.0 1   15463   1   52  1   TPMDBO  MIKE_TEST   BY INDEX ROWID  TABLE       1                                       
INDEX (UNIQUE SCAN) 1.0 1   8171    1       1   TPMDBO  SYS_C007969 UNIQUE SCAN INDEX (UNIQUE)                  1                           

As you can see, the HEXTORAW approach will hit the primary key index on the table. This is because the implicit conversion will honor the "string" type of the right operand (the GUID I specify), and convert all the GUIDs in the database to strings. Definitely something to consider.