oracle中RAW数据类型

loinooo

近日在研究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 [url=]UTL_RAW[/url] 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的介绍到此结束。

陈辉煌

友情的界线要怎么分辨不能手牵手只好肩并肩…