The RAW data type is a length-variable data type. The binary data flows can be transmitted between databases with different character sets and between database servers and clients with different character sets, without character set conversion. This topic describes the syntax, parameters, and considerations of the RAW data type.
The RAW data type stores binary data or strings such as images or audios.
The SQL syntax of the RAW data type is as follows:
RAW(length)
The length parameter specifies the length, in bytes. If you use this parameter to specify the length of a table column in a database, a maximum of 2,000 bytes can be stored in the column. If you use this parameter to specify the length of a PL variable, a maximum of 32,767 bytes can be stored in the variable.
Considerations
Implicit conversion rules
When OceanBase Database implicitly converts RAW type data to character type data such as CHAR or VARCHAR2, each character corresponds to a hexadecimal number that is represented by four consecutive bits of the RAW type, namely, 0-9 and A-F or a-f. For example, the binary bits 11001011 (one byte) of the RAW data type is converted to 'CB' (two bytes) of the character type.
When OceanBase Database implicitly converts character type data to RAW type data, the hexadecimal number represented by each character in a string is converted to four digits. For example, the string 'AB' is converted to 0XAB (10101011).
Notice
If any input character is not a hexadecimal number (0-9 and A-F or a-f), an error is returned. If the number of characters is odd, RAW type data starts with 0 until the RAW type data is filled with bytes. For example, when OceanBase Database converts the string 'ABC' to RAW type data, the string is converted to 0x0ABC, with 0x0A taking one byte and 0xBC taking one byte.
Explicit conversion mode
SQL functions RAWTOHEX() and HEXTORAW() perform an explicit conversion equivalent to the above implicit conversion. You can use functions in PL system packages UTL_RAW and UTL_I18N to convert data between RAW and character data types.
The SQL function HEXTORAW() converts data in a string to a hexadecimal number. The SQL function UTL_RAW.CAST_TO_RAW([VARCHAR2]) stores the ASCII code of each character in the string to fields of the RAW type. For example, convert 051 to 303531.
By implicitly calling the SQL function RAWTOHEX(), OceanBase Database can convert a hexadecimal string of the RAW type to a hexadecimal string of the VARCHAR type.
The following table lists functions related to the RAW data type.
Function |
Description |
|---|---|
| HEXTORAW() | Converts data in a string to a hexadecimal string. Each two characters in the string represent one byte of the RAW data. |
| RAWTOHEX(rawvalue) | Converts the RAW value rawvalue into a hexadecimal string. Each byte of rawvalue is converted into a 2-byte string. |
| UTL_RAW.CAST_TO_RAW([VARCHAR2]) | Keeps the stored data unchanged and changes only the data type from VARCHAR2 to RAW. |
| UTL_RAW.CAST_TO_VARCHAR2([RAW]) | Keeps the stored data unchanged and changes only the data type from RAW to VARCHAR2. |
| UTL_RAW.BIT_OR() UTL_RAW.BIT_AND() UTL_RAW.BIT_XOR() | Performs bitwise operations. |
Examples
Declare
RAWdata in thetest_rawtable and insert a piece of data into the table.obclient> CREATE TABLE test_raw (col1 RAW(10)); Query OK, 0 rows affected obclient> INSERT INTO test_raw VALUES (UTL_RAW.CAST_TO_RAW('1234567890')); Query OK, 1 row affected obclient> SELECT UTL_RAW.CAST_TO_VARCHAR2(col1) FROM test_raw; +--------------------------------+ | UTL_RAW.CAST_TO_VARCHAR2(COL1) | +--------------------------------+ | 1234567890 | +--------------------------------+ 1 row in setInsert two pieces of data into the
raw_test01table and use the conversion function.obclient> CREATE TABLE raw_test01 (id NUMBER, raw_date RAW(10)); Query OK, 0 rows affected obclient> INSERT INTO raw_test01 VALUES (1, HEXTORAW('ff')); Query OK, 1 row affected obclient> INSERT INTO raw_test01 VALUES (2, UTL_RAW.CAST_TO_RAW('051')); Query OK, 1 row affected obclient> SELECT * FROM raw_test01; +------+----------+ | ID | RAW_DATE | +------+----------+ | 1 | FF | | 2 | 303531 | +------+----------+ 2 rows in set