RAW data type

The RAW data type is a variable-length data type that can be used to transfer binary data streams between databases with different character sets or between servers and clients with different character sets without requiring character set conversion. This topic describes the syntax, parameters, and usage of the RAW data type.

The RAW data type can be used to store binary data or byte strings, such as images and audio.

The SQL syntax of the RAW data type is as follows:

RAW(length)

In the preceding statement, the length parameter specifies the length in bytes. If the RAW data type is used as a column in a table, the maximum value of the length parameter is 2000 bytes. If the RAW data type is used as a PL/SQL variable, the maximum value of the length parameter is 32767 bytes.

Considerations

Implicit conversion rules

When the database needs to implicitly convert RAW data to character data (such as CHAR or VARCHAR2), each character in the resulting character data corresponds to four consecutive bits in the RAW data, which are represented as a hexadecimal number (0~9 and A~F or a~f). For example, if the binary data stored in the RAW data is 11001011 (8 bits, or one byte), the converted character data is 'CB' (two bytes).

When the database needs to implicitly convert character data to RAW data, it converts each character in the string to a hexadecimal number and then converts the hexadecimal number to four bits. For example, the string 'AB' is converted to 0XAB (10101011).

Explicit conversion

The SQL functions RAWTOHEX() and HEXTORAW() perform explicit conversions equivalent to the implicit conversions described earlier. You can also use the functions in the UTL_RAW and UTL_I18N packages to convert data between the RAW data type and character data.

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 in a field of the RAW data type. For example, 051 is converted to 303531.

By implicitly calling the SQL function RAWTOHEX(), you can convert a RAW data type represented by a hexadecimal string to a string type (such as VARCHAR), which is the string representation of the hexadecimal data.

The following table describes the functions related to the RAW data type.

Examples

• Declare a RAW data column in the test_raw table and insert one row 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 rows of data into the raw_test01 table and use functions to convert the data.

  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