RAW data type

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 usage notes of the RAW data type.

The RAW data type stores binary data or strings such as images or audios.

SQL syntax of the RAW data type:

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).

Explicit conversion mode

SQL functions RAWTOHEX() and HEXTORAW() perform an explicit conversion equivalent to the above implicit conversion. You can use functions in PL/SQL 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.

Examples

• Declare RAW data in the test_raw table 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 set
  

• Insert two pieces of data into the raw_test01 table 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