Raw Data types in Oracle

Recently, when studying the V$latch view, I found a data type that I had never seen before. The data type of the Addr property in V$latch is Raw (4|8) and the data type of the addr attribute in v$process is also raw (4|8). So we looked at Oracle's SQL Language reference documentation, as described in the following document:

The raw and LONG RAW data types store data is Notto are explicitly converted by Oracle Database when moving data betwe En differentsystems. These data types is intended for binary data or byte strings. For example, you can use a LONG RAW to store graphics,sound, documents, or arrays of binary data, for which the Interpretati On isdependent in the use.

Oracle strongly recommends that's Convertlong RAW columns to binary LOB (BLOB) columns. LOB columns is subject to farfewer restrictions than LONG columns. See To_lob for more information.

RAW is a variable-lengthdata type like VARCHAR2, except this Oracle Net (whichconnects client software to a database or on e database to another) and theoracle import and export utilities does 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 Databetwe En different database character sets, if data is transported betweendatabases, or between the database character set and T He 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 Configuratio N (for example, the Nls_lang environment variable).

When Oracle implicitlyconverts raw or LONG RAW data to CHAR data, the resulting character valuecontains a hexadecimal repr Esentation of the binary input, where each character are a hexadecimal digit (0-9, a-f) representing four consecutive bits O F RAW data. For example, one byte of rawdata with bits 11001011 becomes the value CB.

When Oracle implicitly converts CHAR Datato raw or LONG raw, it interprets each consecutive input character as Ahexadecima L representation of four consecutive bits of binary data and buildsthe resulting RAW or LONG RAW value by concatenating th OSE bits. If any of theinput characters are not a hexadecimal digit (0-9, a-f, a-f) and then an error isreported. If the number of characters is odd, then the result is undefined.

The SQL functions Rawtohex and Hextorawperform Explicit conversions that is equivalent to the above implicitconversions. Other types of conversions between RAW and CHAR data is Possiblewith functions in the oracle-supplied PL/SQL Packages UTL _raw and utl_i18n

It probably means that the data type is used to store data in binary format, like images, sounds, documents, and so on, but Oracle recommends using LOB replacement RAW,LOB columns less restrictive than long

The advantage of RAW: The Oracle server does not perform character set conversions when the network is transmitting or using the Import Export tool, which improves the efficiency of the database and does not result in inconsistent data due to different character sets

The following references the user's test to illustrate Oracle implicitly converts RAW or LONG RAW data to CHAR data,the resulting character value contains a Hexadec iMAL representation of thebinary input and the use of Utl_raw

Raw, similar to VARCHAR2, declares the way Raw (L), L is the length, in bytes, as the database column Max 2000, as the variable maximum 32767 bytes.

Longraw, similar to long, stores up to 2G bytes of data as a database column, up to a maximum of 32760 bytes as a variable

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

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

54686973206973206120726177207465737421This 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 type

The VARCHAR2 character set here is generally GB2312.

Other than that:

Several other function usages of 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 corresponding 16 binary number to Raw

This concludes the introduction to Raw and Utl_raw.

Raw Data types in Oracle