Detailed anatomy of the Oracle Raw type

The following articles mainly explain the Oracle Raw type in detail. We first talk about the concept of RAW, which is similar to CHAR in that aspect, the declared method is RAW (L). It takes L as the length, and the relevant bytes as the unit. It serves as the maximum 2000 bytes of the database column and the maximum 32767 bytes of the variable.

Long raw, similar to LONG, is used to store up to 2G bytes of data as a database column and up to 32760 bytes as a variable.

Table creation:

 
 

  
  
create table raw_test (id number, raw_date raw(10)); 
 
 

Insert raw data:

 
 

  
  
insert into raw_test values (1, hextoraw('ff'));  
  
  
insert into raw_test values (utl_raw.cast_to_raw('051'));  
 
 

Delete A table:

 
 

  
  
drop table raw_test; 
 
 

When HEXTORAW is used, the data in the string is treated as a hexadecimal number. When UTL_RAW.CAST_TO_RAW is used, the ASCII code of each character in the string is directly stored in Oracle RAW fields.

You can use the dump function to query the Storage conditions:

 
 

  
  
select id,raw_date, dump(raw_date, 16) dump_raw from raw_test; 
 
 

Two common conversion functions of RAW and Varchar2 in Oracle

1. UTL_RAW.CAST_TO_RAW

This function converts the VARCHAR2 string to RAW according to the default Character Set GB2312.

 
 

  
  
Insert into cmpp_submit (dest_terminal_id, msg_content)
Values ('123', UTL_RAW.CAST_TO_RAW ('Hello! '));
 
 

2. UTL_RAW.CAST_TO_VARCHAR2

This function is generally GB2312 according to the default character set, and converts RAW to VARCHAR2.

 
 

  
  
select UTL_RAW.CAST_TO_VARCHAR2(msg_content) from cmpp_deliver; 
 
 

In fact, RAW and VARCHAR are similar, but the binary values stored in RAW are not automatically converted into character sets at any time. This is a difference between RAW and VARCHAR, RAW is only an external type, and its internal storage is VARRAW.

Oracle internal definition is:

 
 

  
  
struct { ub2 len; char arr[n] } 
 
 

The internal ORACLE definition of VARRAW is:

 
 

  
  
struct { ub2 len; unsigned char arr[n] }