In oracle 10g, how do you convert SYS_GUID() to varchar? I am trying something like: select USER_GUID from user where email = 'user@example.com' Which returns the RAW byte[]. Is it possible to use a function to convert the RAW to VARCHAR2 in the SQL statement?

Reputation: 10449

How do you convert SYS_GUID() to varchar?

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?

Upvotes: 16

Reputation: 11119

select CAST (USER_GUID AS VARCHAR2(100)) from user where email = '[email protected]'

Upvotes: 1

Reputation: 875

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

Upvotes: 4

Reputation:

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.

Upvotes: 1

Reputation: 425411

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.

Upvotes: 24

Reputation: 10449

Use RAWTOHEX(USER_GUID).

Upvotes: 6