Data type conversion rules

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

Implicit data type conversion

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

Implicit data type conversions adhere to the following rules:

• When executing INSERT and UPDATE statements, OceanBase Database converts a variable value into the data type of the affected column.

• When executing a SELECT FROM statement, OceanBase Database converts data of the affected column into the type of the target variable.

• During the comparison between a character value and a numeric value, OceanBase Database converts the character value into a numeric value.

• When processing numeric values, OceanBase Database adjusts the precision and scale. As a result, the numeric data type resulting from the adjustment can be different from the numeric data type in the base tables.

• Conversions between character or NUMBER values and floating-point number values may not be exact, because the character types and the NUMBER type use decimal precision to represent the value, whereas floating-point numbers use binary precision.

• When a CLOB value is converted into a character type such as VARCHAR2, or a BLOB value is converted into RAW data, OceanBase Database returns an error if the length of the data to be converted exceeds the maximum length supported by the target data type.

• When you convert a timestamp value into a DATE value, the fractional seconds of the timestamp value are truncated and rounded.

• Conversions from BINARY_FLOAT values to BINARY_DOUBLE values are exact.

• Conversions from BINARY_DOUBLE to BINARY_FLOAT are inexact if the BINARY_DOUBLE value uses more bits of precision than supported by the BINARY_FLOAT data type.

• During the comparison between character values and DATE values, OceanBase Database converts character values into DATE values.

• During value assignment, OceanBase Database converts the value on the right side of the equal sign (=) into the data type of the target of the assignment on the left side.

• During concatenation operations, OceanBase Database converts noncharacter data types into a character type or a national character type.

Implicit data type conversion matrix

The following table lists a matrix of implicit conversions of all data types, without considering of the conversion direction or context.

¹ You cannot directly convert RAW data to an INTERVAL value, but you can use UTL_RAW.CAST_TO_VARCHAR2([RAW]) to convert RAW data into a VARCHAR2 string, and then convert the obtained VARCHAR2 string into an INTERVAL value.

Examples of implicit data type conversion

• When you execute the following statement, OceanBase Database uses implicit data type conversion to convert 'james' into a numeric value. However, the conversion fails, and an error is returned.

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

• Implicitly convert the '2' string from the CHAR data type to 2 of the NUMBER data type. 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 of different types. The SQL functions explicitly convert data of one type into another.

Explicit data type conversion matrix

Examples of explicit data type conversion

Use the TO_CHAR function to explicitly convert the current time to the specified format.

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