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

Question

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

This is probably a duplicate of https://stackoverflow.com/q/7289734/1509264 or https://stackoverflow.com/q/7993931/1509264 — MT0, Nov 02 '21 at 23:31

MT0 · Accepted Answer · 2021-11-02T23:22:51.397

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

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

1 Answers1