Oracle/PLSQL: RawToHex Function

In Oracle/PLSQL, the 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 is:

rawtohex( raw )

raw is the raw value to convert to a hexademical 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, rawtohex 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

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

For Example

rawtohex('AB')	would return '4142' if run as an SQL function and 'AB' if run as a PLSQL function
rawtohex('7E')	would return '3745' if run as an SQL function and '7E' as a PLSQL function