Oracle HEXTORAW Function: A Simple Guide

The HEXTORAW function in Oracle SQL is a data type conversion function. Its one and only job is to convert a string of hexadecimal digits (like '4A', 'FF', '7D') into its equivalent RAW data type.

A RAW data type is used to store "raw" binary data, such as a byte sequence. HEXTORAW is the standard way to insert binary data into a RAW column using a human-readable hexadecimal string.

What is the HEXTORAW Function in Oracle?

The HEXTORAW(char) function takes a text string (CHAR, VARCHAR2, etc.) containing hexadecimal characters (0-9, A-F) and returns a RAW binary value.

This is useful for:

• Inserting or updating RAW columns.
• Storing binary values that are represented as hex strings.
• Working with encryption keys or other binary data.

This is the opposite of the RAWTOHEX function, which converts RAW data back into a hexadecimal string.

HEXTORAW Function Syntax

The syntax for HEXTORAW is very simple:

HEXTORAW(char)

Let's break that down:

• char: The text string containing hexadecimal digits. The string must have an even number of characters (since two hex digits make one byte).

Oracle HEXTORAW Function Examples

Here are two practical examples of how to use HEXTORAW.

Example 1: Converting a Hex String to See its Text Equivalent using HEXTORAW

This example shows how to convert a hex string into RAW and then use UTL_RAW.CAST_TO_VARCHAR2 to see what text it represents.

The hex string '486920576F726C64' corresponds to the ASCII text "Hi World".

Query:

SELECT 
  UTL_RAW.CAST_TO_VARCHAR2(
    HEXTORAW('486920576F726C64')
  ) AS "Text_Value"
FROM DUAL;

Result:

Text_Value
----------
Hi World

Example 2: Inserting RAW Data into a Table using HEXTORAW

This is the most common use case. You have a table with a RAW column, and you want to INSERT a binary value.

Query:

-- First, create a sample table
CREATE TABLE test_raw (
  raw_id    NUMBER,
  raw_data  RAW(10)
);

-- Now, insert a row using HEXTORAW to provide the RAW value
INSERT INTO test_raw (raw_id, raw_data)
VALUES (1, HEXTORAW('7D'));

-- Let's see the data (RAWTOHEX converts it back to text for display)
SELECT raw_id, RAWTOHEX(raw_data) AS "Hex_Data" FROM test_raw;

Result: (The table now stores the binary equivalent of '7D'.)

    RAW_ID Hex_Data
---------- --------
         1 7D