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