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

Use RAWTOHEX(USER_GUID).

Answer 2

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.

Answer 3

Please don't mod-1 if I'm wrong. I'm going from memory so this a disclaimer to verify.

TO_CHAR is actually different between SQL and PL/SQL.

In SQL TO_CHAR does not take a raw as you have found out.

In PL/SQL To_CHAR will take a raw value.

So if you're in a procedure anyways, sometimes it easier to use a variable, but if you're just using SQL, go with the other answers here.

Answer 4

select RAWTOHEX(USER_GUID) from user where email = '[email protected]'