Today we will mainly introduce you to the interpretation of common Oracle Raw types. We all know RAW, which is very similar to CHAR. We declare it in Oracle RAW (L ), L is the length, in the unit of correlation bytes. It is used as the maximum 2000 bytes of the actual database column and as 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 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 Oracle RAW and Varchar2
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 ('20170101', 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, Oracle RAW and VARCHAR are similar, but the binary values stored in RAW are not automatically converted to 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.
The Oracle internal definition of VARCHAR is:
- struct { ub2 len; char arr[n] }
The internal ORACLE definition of VARRAW is:
- struct { ub2 len; unsigned char arr[n] }
The above content is the description of Oracle Raw type, and I hope it will help you in this regard.