Oracle RAWTONHEX Function: A Simple Guide

The RAWTONHEX function in Oracle SQL is a character set-specific version of RAWTOHEX. Its job is to convert a RAW binary value into a human-readable hexadecimal string, but it specifically returns the result in the national character set (as an NVARCHAR2 data type).

This function is essentially a shortcut for TO_NCHAR(RAWTOHEX(raw)).

What is the RAWTONHEX Function in Oracle?

The RAWTONHEX(raw) function takes a RAW data type (a sequence of binary bytes) and returns a NVARCHAR2 string containing its hexadecimal representation. Each byte in the RAW value is converted into two hexadecimal characters.

This is a technical function used when you need to display or handle binary data as hexadecimal text specifically within the national character set, which is often used for Unicode (like AL16UTF16).

RAWTONHEX Function Syntax

The syntax for RAWTONHEX is very simple:

RAWTONHEX(raw_value)

Let's break that down:

• raw_value: The RAW value or column you want to convert. This can also be any other scalar data type (like VARCHAR2), which Oracle will first convert to RAW and then to hex.

Oracle RAWTONHEX Function Examples

Here are two practical examples of how to use RAWTONHEX.

Example 1: Basic Conversion using RAWTONHEX

This example converts a simple RAW value (created with HEXTORAW) back into its hexadecimal string representation. The output will be an NVARCHAR2 string.

Query:

SELECT
  RAWTONHEX(HEXTORAW('7D')) AS "Hex_String"
FROM DUAL;

Result: (The result looks like a standard string, but its data type is NVARCHAR2)

Hex_String
----------
7D

Example 2: Comparing RAWTONHEX and RAWTOHEX using DUMP

This example uses the DUMP function to show the internal difference between the NVARCHAR2 string returned by RAWTONHEX and the VARCHAR2 string returned by RAWTOHEX.

Query:

SELECT
  RAWTONHEX(HEXTORAW('7D')) AS "NCHAR_Result",
  DUMP(RAWTONHEX(HEXTORAW('7D'))) AS "NCHAR_Dump",
  RAWTOHEX(HEXTORAW('7D')) AS "CHAR_Result",
  DUMP(RAWTOHEX(HEXTORAW('7D'))) AS "CHAR_Dump"
FROM DUAL;

Result: (The results will vary, but DUMP shows Typ=1 (NVARCHAR2) for RAWTONHEX and Typ=96 (VARCHAR2) for RAWTOHEX, along with different byte representations.)

NCHAR_Result NCHAR_Dump                      CHAR_Result CHAR_Dump
------------ ------------------------------- ----------- --------------------
7D           Typ=1 Len=4: 0,55,0,68            7D          Typ=96 Len=2: 55,68

This shows that RAWTONHEX is working specifically with the national character set.