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.

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 8i, Oracle 9i, Oracle 10g, Oracle 11g

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.