coldfusion - oracle guid oddity

Question

Hello all, I am seeing an interesting result trying to use as a sys_guid() value in Coldfusion 7.

If I do

<cfquery>select sys_guid() as guid from dual</cfquery>

The return guid value is seen as a binary field by Coldfusion.

However, if I do

<cfquery>select sys_guid() || '' as guid from dual</cfquery>

The return guid value is seen as a string.

I have tried a couple different Oracle and Coldfusion/Macromedia drivers, and they all respond the same. The latter statement is what I have been using, so I can process the guids later on in code, and works fine. I am hoping someone can explain why I am getting these results.

Answer 1

GUIDs are 128-bit (16-byte) binary values. They are not strings. Take a look at the Oracle documentation for sys_guid.

Your goal is to deal with GUIDs as strings. That means that somehow you need to convert the GUID value to a string value. When you simply do select sys_guid() as guid from dual, then you get a 128-bit binary value, as expected. When you instead do select sys_guid() || '' as guid from dual, then first Oracle has to convert a 128-bit binary value to a string value before it can perform the concatenation, because concatenation only works on two string values. Oracle knows how to convert binary values to string values already, and it will perform this conversion if you put a binary value where Oracle expects a string value.