Data type conversion rules

Generally, an expression cannot contain values of different data types. To enable calculations between expressions, OceanBase Database supports implicit and explicit conversions from one data type to another. This topic describes the implicit and explicit conversion rules.

Implicit data type conversion

OceanBase Database automatically converts a value from one data type to another when the conversion makes sense.

The rules for implicit data type conversion are as follows:

• When you execute INSERT or UPDATE operations, OceanBase Database converts variable values to the data types of the affected columns.

• When you execute SELECT FROM operations, OceanBase Database converts column data types to the data types of the target variables.

• When you compare a character value with a numeric value, OceanBase Database converts the character value to a numeric value.

• When you process numeric values, OceanBase Database adjusts the precision and scale. The resulting numeric data type may be different from the numeric data type in the base table.

• Converting a character value or a NUMBER value to a floating-point value can be imprecise. This is because character types and NUMBER values use decimal precision to represent numeric values, whereas floating-point values use binary precision.

• When you convert a CLOB value to a character type (such as VARCHAR2) or a BLOB value to a RAW value, OceanBase Database returns an error if the data to be converted exceeds the size of the target data type.

• When you convert a timestamp value to a DATE value, the fractional seconds part of the timestamp value is truncated and rounded.

• Converting a BINARY_FLOAT value to a BINARY_DOUBLE value is precise.

• Converting a BINARY_DOUBLE value to a BINARY_FLOAT value is imprecise if the precision of the BINARY_DOUBLE value exceeds the precision supported by the BINARY_FLOAT data type.

• When you compare a character value with a DATE value, OceanBase Database converts the character value to a DATE value.

• During assignment operations, OceanBase Database converts the value on the right side of the equal sign to the data type of the target variable on the left side.

• During concatenation operations, OceanBase Database converts non-character values to character or national character values.

Implicit conversion matrix

The following table shows the implicit conversion matrix for all data types. The conversion direction and context are not considered.

¹ You cannot directly convert RAW to INTERVAL, but you can use UTL_RAW.CAST_TO_VARCHAR2([RAW]) to convert RAW to VARCHAR2, and then convert the resulting VARCHAR2 value to INTERVAL.

Implicit data type conversion examples

• When you execute the following example statement, OceanBase Database implicitly converts the string 'james' to a numeric type. However, the conversion fails and an error is returned.

  obclient> SELECT 5 * 10 + 'james' FROM DUAL;
  ORA-01722: invalid number
  

• The string '2' is implicitly converted from the CHAR data type to the numeric data type 2. The calculation result is 52.

  obclient> SELECT 5 * 10 + '2' FROM  DUAL;
  +----------+
  | 5*10+'2' |
  +----------+
  |       52 |
  +----------+
  1 row in set
  

Explicit data type conversion

You can use SQL conversion functions to convert data types. SQL functions explicitly convert one data type to another.

Explicit type conversion matrix

Explicit data type conversion example

The current time is explicitly converted to the specified format using the TO_CHAR function.

obclient> SELECT TO_CHAR(SYSDATE, 'YYYY_MM_DD') FROM DUAL;
+-------------------------------+
| TO_CHAR(SYSDATE,'YYYY_MM_DD') |
+-------------------------------+
| 2021_11_30                    |
+-------------------------------+
1 row in set