HEXTORAW() Function in Oracle

In Oracle Database, the HEXTORAW() function converts hexadecimal to a raw value.

Syntax

The syntax goes like this:

HEXTORAW(char)

Where char can be any of the CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data types.

Here’s an example:

SELECT HEXTORAW('74a5cfe') FROM DUAL;

Result:

074A5CFE

We can use the DUMP() function to find out the data type of the return value:

SELECT DUMP(HEXTORAW('74a5cfe')) FROM DUAL;

Result:

Typ=23 Len=4: 7,74,92,254

The Typ=23 tells us that the return value is of type 23, which is the type ID for RAW.

Just to be clear, here’s an example that compares the hexadecimal string and the raw value:

SELECT 
    DUMP('123') AS "r1",
    DUMP(HEXTORAW('123')) AS "r2"
FROM DUAL;

Result:

                       r1                    r2 
_________________________ _____________________ 
Typ=96 Len=3: 49,50,51    Typ=23 Len=2: 1,35

The hexadecimal value is of type 96, which is the type ID for CHAR and NCHAR.

Passing a value that’s not a hexadecimal value results in an error.

Example:

SELECT HEXTORAW('z') FROM DUAL;

Result:

Error report -
ORA-01465: invalid hex number

If the argument is null, the result is null:

SET NULL 'null';
SELECT HEXTORAW(null)
FROM DUAL;

Result:

null

By default, SQLcl and SQL*Plus return a blank space whenever a null value occurs as a result of a SQL SELECT statement.

However, you can use SET NULL to specify a different string to be returned. Here I specified that the string null should be returned.

Calling HEXTORAW() without any arguments results in an error:

SELECT HEXTORAW()
FROM DUAL;

Result:

SQL Error: ORA-00909: invalid number of arguments
00909. 00000 -  "invalid number of arguments"

And passing too many arguments also results in an error:

SELECT HEXTORAW('a', 'b')
FROM DUAL;

Result:

SQL Error: ORA-00909: invalid number of arguments
00909. 00000 -  "invalid number of arguments"