How do you convert SYS_GUID() to varchar?

Question

In oracle 10g, how do you convert SYS_GUID() to varchar? I am trying something like:

select USER_GUID from user where email = '[email protected]'

Which returns the RAW byte[]. Is it possible to use a function to convert the RAW to VARCHAR2 in the SQL statement?

Answer 1

Don't forget to use HEXTORAW(varchar2) when comparing this value to the RAW columns.

There is no implicit convesion from VARCHAR2 to RAW. That means that this clause:

WHERE raw_column = :varchar_value

will be impicitly converted into:

WHERE RAWTOHEX(raw_column) = :varchar_value

, thus making indices on raw_column unusable.

Use:

WHERE raw_column = HEXTORAW(:varchar_value)

instead.