Oracle / PLSQL: RAWTOHEX Function

This Oracle tutorial explains how to use the Oracle/PLSQL RAWTOHEX function with syntax and examples.

Description

The Oracle/PLSQL RAWTOHEX function converts a raw value into a hexadecimal value. One of our viewers says that this function comes in handy to move a varchar value to a blob field.

Syntax

The syntax for the RAWTOHEX function in Oracle/PLSQL is:

RAWTOHEX( raw )

Parameters or Arguments

raw

The raw value to convert to a hexademical value.

Returns

The RAWTOHEX function returns a string value.

Note

This function works differently when used as a PLSQL built-in function as opposed to running it in SQL. As a PLSQL function, the RAWTOHEX function may perform an implicit conversion before converting to a hexadecimal value. This may result in a different value being returned by this function between PLSQL and SQL.

For example, if you ran the following:

DECLARE
a varchar2(8);

BEGIN

   a := rawtohex('AB');
   dbms_output.put_line(a);

   SELECT RAWTOHEX('AB') INTO a FROM dual;

   dbms_output.put_line(a);

END;

The following would be output as the result:

AB
4142

The reason for the difference is that PLSQL is doing an implicit conversion of 'AB' into a RAW (treats 'AB' as a single byte equal to CHR(171)). A RAWTOHEX on that returns the string 'AB'.

Whereas, SQL is not doing that implicit conversion. 'AB' is 2 byte RAW already and a RAWTOHEX of that retuns 4142.

Applies To

The RAWTOHEX function can be used in the following versions of Oracle/PLSQL:

• Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

Example

Let's look at some Oracle RAWTOHEX function examples and explore how to use the RAWTOHEX function in Oracle/PLSQL.

For example:

RAWTOHEX('AB')
Output: '4142' if run as a SQL function and 'AB' if run as a PLSQL function

RAWTOHEX('7E')
Output: '3745' if run as a SQL function and '7E' as a PLSQL function