Oracle/PL SQL equivalent to Convert(VARBINARY)

user_15048320 0
Viewed 591

I'm looking for the Oracle equivalent of something that is easy to do in MS SQL, namely:

DECLARE @INDEC AS INT
SET @INDEC = 37000
RIGHT(CONVERT(VARBINARY(8),@INDEC),4)

This replicates a legacy software data storage format in a MS SQL database. The same software can run against oracle, and I now need to replicate this same statement there.

Specifically, when I look at the result of this character by I see a unique set of ASCII values:

SELECT 
    ASCII(SUBSTRING(RIGHT(CONVERT(VARBINARY(8),37000),4),1,1)) AS POS1,
    ASCII(SUBSTRING(RIGHT(CONVERT(VARBINARY(8),37000),4),2,1)) AS POS2,
    ASCII(SUBSTRING(RIGHT(CONVERT(VARBINARY(8),37000),4),3,1)) AS POS3,
    ASCII(SUBSTRING(RIGHT(CONVERT(VARBINARY(8),37000),4),4,1)) AS POS4
FROM MYTABLE
POS1 POS2 POS3 POS4
0 0 144 136

When I try this on Oracle, using CAST as RAW, I either get my input value returned to me, or I get error messages.

I have specifically tried a variety of cast options including specifying a length for the data and converting the input to hex prior to casting as raw.

SELECT 
  ASCII(SUBSTR(CAST('37000' AS RAW(8)),3,1)) AS POS1, 
  ASCII(SUBSTR(CAST('37000' AS RAW(8)),4,1)) AS POS2,
  ASCII(SUBSTR(CAST('37000' AS RAW(8)),5,1)) AS POS3,
  ASCII(SUBSTR(CAST('37000' AS RAW(8)),6,1)) AS POS4
FROM dual;

This simply returns "7000" or:

POS1 POS2 POS3 POS4
55 48 48 48

If I try converting to hex first, I get an error message (invalid hex number):

select HEXTORAW(to_char(37000,'XXXXXXXX')) from dual

If I separate the conversion I know that hex for 37000 is 9088. But the following returns the input string (I have taken it apart.)

select HEXTORAW('9088') from dual

Ultimately the output of this conversion operation will be concatenated with other string data and stored in a column that is defined as VARCHAR.

I feel like I'm missing something very fundamental here.

Any thoughts or input would be greatly appreciated.

oracle-sqldeveloper oracle11g oracle
2 Answers

If you want the value as numbers, just use arithmetic:

SELECT MOD( FLOOR( value / (256*256*256) ), 256 ) AS pos1,
       MOD( FLOOR( value / (256*256)     ), 256 ) AS pos2,
       MOD( FLOOR( value / 256           ), 256 ) AS pos3,
       MOD( value,                          256 ) AS pos4
FROM   table_name

Then, for your sample data:

CREATE TABLE table_name ( value ) AS
SELECT 37000 FROM DUAL;

It outputs:

POS1 | POS2 | POS3 | POS4
---: | ---: | ---: | ---:
   0 |    0 |  144 |  136

If you want it as a 4-byte RAW then prepend the FM format model in the hexadecimal conversion to strip the leading white-space and use 0XXXXXXX to get 4-bytes with leading zeroes:

SELECT HEXTORAW(TO_CHAR(value,'FM0XXXXXXX')) AS raw_value
FROM table_name;

Which outputs:

| RAW_VALUE  |
| :--------- |
| 0x00009088 |

db<>fiddle here

user_15092640

This thing:

select HEXTORAW(to_char(37000,'XXXXXXXX')) from dual

returns an error, because by default to_char adds a space to the left of the hex representation of your number, and hextoraw isn't smart enough to know what to do with it. (That is a placeholder for the algebraic sign of the number, + or, more importantly, -).

To fix that, you must use the fm format modifier, which causes the resulting hex value to omit the leading space.

Separately, the 'XXXXXXXX' model causes leading zeros to be omitted. If you want to cause the raw value to be four bytes long (instead of the length of two, which would result from converting the integer 37000), you would need to use 0 instead of X in the format model, at least for the first digit (from the left). The last digit placeholder (to the right) in the model must still be X to indicate hex.

So, we are left with this:

select hextoraw(to_char(37000, 'fm0XXXXXXX')) as converted_to_raw
from   dual;

CONVERTED_TO_RAW
----------------
00009088

You can't really tell by looking at the result of this query (it just looks like a hex number), but the value is indeed of RAW data type, of length 4, and the bytes are the ones you were getting in SQL Server (I assume that's what you mean by MS SQL). In Oracle, you can see both the data type (23 means RAW) and the exact bytes by using the dump function. By default, it shows the bytes in decimal notation (which is exactly what you were showing from your SQL Server thing). It would look like this:

select dump(hextoraw(to_char(37000, 'fm0XXXXXXX'))) as converted_to_raw
from   dual;

CONVERTED_TO_RAW              
------------------------------
Typ=23 Len=4: 0,0,144,136

So, in summary, your attempt with hextoraw was on the right path, but you needed to use the very precise format model in the conversion of integer to hex string (avoid the leading space, include leading zeros); and, if you really wanted to see the resulting data type and all the bytes, you had to use dump. Remember dump - it turns out to be very useful in many different contexts.

Admin1
Related