That is because you are probably testing in SQLPlus or you are binding a varchar in a host application.

Hexttoraw returns a RAW. SQLPlus will convert that into a varchar2 for display. When you convert a raw into a varchar2 -- you get hex.

select hexttoraw('00') from dual;

does in fact return a 'raw' but sqlplus is printing it in hex.

If you do it in pro*c where you have total control over the datatype (unlike in sqlplus which is just a "report" tool) -- you'll find that a program like:


typedef struct TAGmy_raw
{
long len;
unsigned char arr[255];
} my_raw;



main( int argc, char * argv[] )
{

EXEC SQL BEGIN DECLARE SECTION;
EXEC SQL TYPE my_raw IS LONG VARRAW(255) ;
my_raw raw_data;
EXEC SQL END DECLARE SECTION;


EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();
EXEC SQL CONNECT :USERID;
printf("\nConnected to ORACLE as user: %s\n\n", USERID);

EXEC SQL select hextoraw( '00' ) into :raw_data from dual;

printf( "it is %d bytes long and have the value %d\n", raw_data.len, raw_data.arr[0] );

EXEC SQL COMMIT WORK RELEASE;

exit(0);
}

returns:

$ ./t

Connected to ORACLE as user: /

it is 1 bytes long and have the value 0


Which is expected (hex '00' turns into \0 in C)