Can you provide and example of utl_raw.convert + utl_raw.cast_to_varchar2?

Question

Can somebody please tell me how the utl_raw.convert works with utl_raw.cast_to_varchar2 in Oracle 11g by giving a sample code as i am not able to find an example use case online.

Thanks Gautam

Answer 1

UTL_RAW.CONVERT is a function to convert a raw from one character set to another character set returning the result as a raw.

So, the functions admits 3 parameters:

• The raw you want to convert
• The characterset destination
• The characterset source

UTL_RAW.CAST_TO_VARCHAR2 converts a raw string into a varchar2 data type.

Let's put it into test

My settings: Oracle 11g Database Character Set: UTF-8

SQL> create table test_raw ( c1 raw(256) ) ;

Table created.

SQL>  insert into test_raw select UTL_RAW.CAST_TO_RAW ( '123009988poee' ) from dual ;

1 row created.

SQL> insert into test_raw select UTL_RAW.CAST_TO_RAW ( 'üäöüöä' ) from dual ;

1 row created.

SQL> commit ;

Commit complete.

SQL> select * from test_raw ;

C1
--------------------------------------------------------------------------------
313233303039393838706F6565
EFBFBDEFBFBDEFBFBDEFBFBDEFBFBDEFBFBDEFBFBDEFBFBDEFBFBDEFBFBDEFBFBDEFBFBD

SQL> select utl_raw.cast_to_varchar2 ( a.c1 ) from test_raw a ;

UTL_RAW.CAST_TO_VARCHAR2(A.C1)
--------------------------------------------------------------------------------
123009988poee
????????????

Here my representation of the second raw is not accurate, as I have to cast_to_raw in the first place. If you don't do that , you got ORA-O1465: invalid hex number.

SQL> select utl_raw.cast_to_varchar2 ( utl_raw.convert ( utl_raw.cast_to_raw ( a.c1 ) , 'we8mswin1252' , 'al32utf8' ) ) as result
  2  from test_raw a ;

RESULT
--------------------------------------------------------------------------------
313233303039393838706F6565
EFBFBDEFBFBDEFBFBDEFBFBDEFBFBDEFBFBDEFBFBDEFBFBDEFBFBDEFBFBDEFBFBDEFBFBD

Use as well the dump() function to investigate the byte values that make up the string. This way you can determine if the string is made up of the correct values.

SQL> select dump ( utl_raw.cast_to_varchar2 ( utl_raw.convert ( utl_raw.cast_to_raw ( a.c1 ) , 'we8mswin1252' , 'al32utf8' )  ) ) as dump_result
from test_raw a

RESULT
--------------------------------------------------------------------------------
Typ=1 Len=26: 51,49,51,50,51,51,51,48,51,48,51,57,51,57,51,56,51,56,55,48,54,70,
54,53,54,53

Typ=1 Len=72: 69,70,66,70,66,68,69,70,66,70,66,68,69,70,66,70,66,68,69,70,66,70,
66,68,69,70,66,70,66,68,69,70,66,70,66,68,69,70,66,70,66,68,69,70,66,70,66,68,69
,70,66,70,66,68,69,70,66,70,66,68,69,70,66,70,66,68,69,70,66,70,66,68


SQL> select dump ( c1 ) from test_raw ;

DUMP(C1)
--------------------------------------------------------------------------------
Typ=23 Len=13: 49,50,51,48,48,57,57,56,56,112,111,101,101
Typ=23 Len=36: 239,191,189,239,191,189,239,191,189,239,191,189,239,191,189,239,1
91,189,239,191,189,239,191,189,239,191,189,239,191,189,239,191,189,239,191,189