CODEWITHSUNDEEP
sqloracle-databaseoracle11g
mattsmith5
mattsmith5

Reputation: 1123

Convert from Oracle's RAW(16) to .NET's GUID using SQL

How do I convert a Oracle Raw 16 to GUID in MSSQL? The following is not working in Oracle DB SQL Console,

select hextoraw(Raw16Column), *
from OracleDb.PRODUCTS
order by PRODUCTS_ID desc
fetch next 20 rows only

Error:

[42000][936] ORA-00936: missing expression Position: 23

Resources: Convert from Oracle's RAW(16) to .NET's GUID

https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions064.htm

Upvotes: 0

Views: 530

Answers (1)

MT0
MT0

Reputation: 168001

If you are using * and are also selecting other columns then you must prefix the * with the table name or alias (and you probably want RAWTOHEX rather than HEXTORAW):

select p.*,
       RAWTOHEX(raw16column)
from   PRODUCTS p
order by PRODUCTS_ID desc
fetch next 20 rows only

and, if you want to convert it to a GUID with the correct endianess then:

select p.*,
       SUBSTR(raw16column,7,2)
       ||SUBSTR(raw16column,5,2)
       ||SUBSTR(raw16column,3,2)
       ||SUBSTR(raw16column,1,2)
       ||'-'
       ||SUBSTR(raw16column,11,2)
       ||SUBSTR(raw16column,9,2)
       ||'-'
       ||SUBSTR(raw16column,15,2)
       ||SUBSTR(raw16column,13,2)
       ||'-'
       ||SUBSTR(raw16column,17,16)
         AS guid
from   PRODUCTS p
order by PRODUCTS_ID desc
fetch next 20 rows only

db<>fiddle here

Upvotes: 2

Related Questions