oracle中RAW數據類型

近日在研究v$latch視圖時，發現一個從未見過的數據類型。v$latch 中ADDR屬性的數據類型為RAW(4|8)  同時也發現v$process中的ADDR屬性的數據類型也為RAW(4|8)。於是查了一下oracle 的SQL Language Reference文檔，文檔如下描述：

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 strongly 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, except 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 implicitly converts CHAR datato RAW or LONG RAW, it interprets each consecutive input character as ahexadecimal 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 HEXTORAWperform explicit conversions that are equivalent to the above implicitconversions. Other types of conversions between RAW and CHAR data are possiblewith functions in the Oracle-supplied PL/SQL packages UTL_RAW and UTL_I18N

大概意思是該數據類型用於存儲二進位格式的數據，像圖像，聲音，文檔等等，但是oracle建議使用lob替代raw，LOB列比LONG受到更少的限制

Raw的優勢: 在網路傳輸，或者使用導入導出工具時，oracle伺服器不執行字元集轉換，這樣在資料庫的效率上會有所提高，而且不會因為字元集不同而導致數據的不一致性

以下引用網友的測試，來說明Oracle implicitly converts RAW or LONG RAW data to CHAR data,the resulting character value contains a hexadecimal representation of thebinary input以及UTL_RAW的使用

RAW，類似於VARCHAR2，聲明方式RAW(L)，L為長度，以位元組為單位，作為資料庫列最大2000，作為變數最大32767位元組。

LONGRAW，類似於LONG，作為資料庫列最大存儲2G位元組的數據，作為變數最大32760位元組

測試：

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('中文測試'));

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!

中文測試

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中文測試

這裡用到了兩個函數：

utl_raw.cast_to_raw([varchar2]);--將varchar2轉換為raw類型

utl_raw.cast_to_varchar2([raw]);--將raw轉換為varchar2類型

這裡varchar2的字元集一般是GB2312。

另外：

utl_raw包的幾個其他的函數用法：

utl_raw.cast_from_number([number]);

utl_raw.cast_to_number([number]);

位操作：

utl_raw.bit_or();

utl_raw.bit_and();

utl_raw.bit_xor();

另外還有轉換函數：

hextoraw();--將對應16進位數轉換為raw

關於raw和utl_raw的介紹到此結束。