RAW data type in oracle and oracleRAW Data Type

RAW data type in oracle and oracleRAW Data Type

Recently, when studying the v $ latch view, we found a data type that we have never seen before. The data type of the ADDR attribute in v $ latch is RAW (4 | 8), and the Data Type of the ADDR attribute in v $ process is also RAW (4 | 8 ). So I checked the oracle SQL Language Reference document, which is described as follows:

The RAW and long raw data types store data that is notto be explicitly converted by Oracle Database when moving data between differentsystems. these data types are intended for binary data or byte strings. for example, you can use long raw to store graphics, sound, documents, or arrays of binary data, for which the interpretation isdependent on the use.

 

Oracle stronugly recommends that you convertLONG RAW columns to binary LOB (BLOB) columns. LOB columns are subject to farfewer restrictions than LONG columns. See TO_LOB for more information.

 

RAW is a variable-lengthdata type like VARCHAR2, doesn't that Oracle Net (whichconnects client software to a database or one database to another) and theOracle import and export utilities do not perform character conversion whentransmitting RAW or long raw data. in contrast, Oracle Net and the Oracleimport and export utilities automatically convert CHAR, VARCHAR2, and LONG databetween different database character sets, if data is transported betweendatabases, or between the database character set and the client character set, if data is transported between a database and a client. the client characterset is determined by the type of the client interface, such as OCI or JDBC, andthe client configuration (for example, the NLS_LANG environment variable ).

 

When Oracle implicitlyconverts RAW or long raw data to CHAR data, the resulting character valuecontains a hexadecimal representation of the binary input, where each character is a hexadecimal digit (0-9, A-F) representing four consecutive bits of RAW data. for example, one byte of RAWdata with bits 11001011 becomes the value CB.

 

When Oracle merge converts CHAR datato RAW or long raw, it interprets each consecutive input character as random representation of four consecutive bits of binary data and buildsthe resulting RAW or long raw value by concatenating those bits. if any of theinput characters is not a hexadecimal digit (0-9, A-F, a-f), then an error isreported. if the number of characters is odd, then the result is undefined.

 

The SQL functions RAWTOHEX and explain explicit conversions that are equivalent to the above limits. Other types of conversions between RAW and CHAR data are possiblewith functions in the Oracle-supplied PL/SQL packages UTL_RAW and UTL_I18N

 

 

This data type is used to store binary data, such as images, sounds, and documents. However, oracle recommends that you use lob instead of raw, and the LOB column is less limited than LONG.

 

Advantages of Raw: the oracle server does not perform Character Set conversion during network transmission or when the import/export tool is used, which improves the database efficiency, data inconsistency is not caused by different character sets.

 

The following is a reference of tests conducted by netizens to illustrate the use of Oracle implicitly converts RAW or long raw data to CHAR data, the resulting character value contains a hexadecimal representation of thebinary input and UTL_RAW.

 

RAW, similar to VARCHAR2, is declared as RAW (L), L is the length, in bytes, as the maximum value of the database column 2000, as the maximum variable 32767 bytes.

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

 

Test:

 

SQL> create table datatype_test_raw (paddr raw (8 ));

 

 

Tablecreated

 

SQL> insert into datatype_test_raw (paddr) values (utl_raw.cast_to_raw ('this is a rawtype test! '));

 

Insertinto datatype_test_raw (paddr) values (utl_raw.cast_to_raw ('this is a raw typetest! '))

 

ORA-01401: inserted value too large for column

 

SQL> alter table datatype_test_raw modify paddr raw (20 );

 

Tablealtered

 

SQL> insert into datatype_test_raw (paddr) values (utl_raw.cast_to_raw ('this is a rawtype test! '));

 

Insertinto datatype_test_raw (paddr) values (utl_raw.cast_to_raw ('this is a raw typetest! '))

 

ORA-01401: inserted value too large for column

 

SQL> insert into datatype_test_raw (paddr) values (utl_raw.cast_to_raw ('this is a rawtest! '));

 

1row inserted

 

SQL> commit;

 

Commitcomplete

 

SQL> select * from datatype_test_raw;

 

PADDR

----------------------------------------

54686973206973206120726177207465737421

 

SQL> select utl_raw.cast_to_varchar2 (paddr) from datatype_test_raw;

 

UTL_RAW.CAST_TO_VARCHAR2 (PADDR

--------------------------------------------------------------------------------

Thisis a raw test!

 

SQL> insert into datatype_test_raw (paddr) values (utl_raw.cast_to_raw ('Chinese test '));

 

1row inserted

 

SQL> commit;

 

Commitcomplete

 

SQL> select utl_raw.cast_to_varchar2 (paddr) from datatype_test_raw;

 

UTL_RAW.CAST_TO_VARCHAR2 (PADDR

--------------------------------------------------------------------------------

Thisis a raw test!

Chinese test

 

SQL> select paddr, utl_raw.cast_to_varchar2 (paddr) from datatype_test_raw;

 

PADDR UTL_RAW.CAST_TO_VARCHAR2 (PADDR

Bytes ------------------------------------------------------------------------------------------------------------------------

54686973206973206120726177207465737421 This is a raw test!

D6D0CEC4B2E2CAD4 Chinese test

 

Two functions are used here:

Utl_raw.cast_to_raw ([varchar2]); -- convert varchar2 to raw type

Utl_raw.cast_to_varchar2 ([raw]); -- convert raw to varchar2.

Here, the varchar2 character set is generally GB2312.

 

In addition:

Usage of several other functions in the utl_raw package:

Utl_raw.cast_from_number ([number]);

Utl_raw.cast_to_number ([number]);

Bit operation:

Utl_raw.bit_or ();

Utl_raw.bit_and ();

Utl_raw.bit_xor ();

 

There are also conversion functions:

Hextoraw (); -- converts the hexadecimal number to raw

The introduction of raw and utl_raw ends here.