Datatype Comparison Rules

Implicit and Explicit Data Conversion

Oracle recommends that you specify explicit conversions, rather than rely on implicit or automatic conversions, for these reasons:

• SQL statements are easier to understand when you use explicit datatype conversion functions.

• Implicit datatype conversion can have a negative impact on performance, especially if the datatype of a column value is converted to that of a constant rather than the other way around.

• Implicit conversion depends on the context in which it occurs and may not work the same way in every case. For example, implicit conversion from a datetime value to a VARCHAR2 value may return an unexpected year depending on the value of the NLS_DATE_FORMAT parameter.

• Algorithms for implicit conversion are subject to change across software releases and among Oracle products. Behavior of explicit conversions is more predictable.

Implicit Data Conversion

Oracle Database automatically converts a value from one datatype to another when such a conversion makes sense. Implicit conversion to character datatypes follows these rules:

Table 2-10 is a matrix of Oracle implicit conversions. The table shows all possible conversions, without regard to the direction of the conversion or the context in which it is made. The rules governing these details follow the table.

The following rules govern the direction in which Oracle Database makes implicit datatype conversions:

• During INSERT and UPDATE operations, Oracle converts the value to the datatype of the affected column.

• During SELECT FROM operations, Oracle converts the data from the column to the type of the target variable.

• When comparing a character value with a numeric value, Oracle converts the character data to a numeric value.

• Conversions between character values or NUMBER values and floating-point number values can be inexact, because the character types and NUMBER use decimal precision to represent the numeric value, and the floating-point numbers use binary precision.

• When converting a CLOB value into a character datatype such as VARCHAR2, or converting BLOB to RAW data, if the data to be converted is larger than the target datatype, then the database returns an error.

• Conversions from BINARY_FLOAT to BINARY_DOUBLE are exact.

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

• When comparing a character value with a DATE value, Oracle converts the character data to DATE.

• When you use a SQL function or operator with an argument of a datatype other than the one it accepts, Oracle converts the argument to the accepted datatype.

• When making assignments, Oracle converts the value on the right side of the equal sign (=) to the datatype of the target of the assignment on the left side.

• During concatenation operations, Oracle converts from noncharacter datatypes to CHAR or NCHAR.

• During arithmetic operations on and comparisons between character and noncharacter datatypes, Oracle converts from any character datatype to a numeric, date, or rowid, as appropriate. In arithmetic operations between CHAR/VARCHAR2 and NCHAR/NVARCHAR2, Oracle converts to a NUMBER.

• Comparisons between CHAR and VARCHAR2 and between NCHAR and NVARCHAR2 types may entail different character sets. The default direction of conversion in such cases is from the database character set to the national character set. Table 2-11 shows the direction of implicit conversions between different character types.

• Most SQL character functions are enabled to accept CLOBs as parameters, and Oracle performs implicit conversions between CLOB and character types. Therefore, functions that are not yet enabled for CLOBs can accept CLOBs through implicit conversion. In such cases, Oracle converts the CLOBs to CHAR or VARCHAR2 before the function is invoked. If the CLOB is larger than 4000 bytes, then Oracle converts only the first 4000 bytes to CHAR.

User-defined types such as collections cannot be implicitly converted, but must be explicitly converted using CAST ... MULTISET

Implicit Data Conversion Examples

Text Literal Example The text literal '10' has datatype CHAR. Oracle implicitly converts it to the NUMBER datatype if it appears in a numeric expression as in the following statement:

SELECT salary + '10'
   FROM employees;

Character and Number Values Example When a condition compares a character value and a NUMBER value, Oracle implicitly converts the character value to a NUMBER value, rather than converting the NUMBER value to a character value. In the following statement, Oracle implicitly converts '200' to 200:

SELECT last_name
    FROM employees 
    WHERE employee_id = '200';

Date Example In the following statement, Oracle implicitly converts '03-MAR-97' to a DATE value using the default date format 'DD-MON-YY':

SELECT last_name
    FROM employees 
    WHERE hire_date = '03-MAR-97';

Rowid Example In the following statement, Oracle implicitly converts the text literal 'AAAGH6AADAAAAFGAAN' to a rowid value. (Rowids are unique within a database, so to use this example you must know an actual rowid in your database.)

SELECT last_name
    FROM employees
    WHERE ROWID = 'AAAGH6AADAAAAFGAAN';

Explicit Data Conversion

You can explicitly specify datatype conversions using SQL conversion functions. Table 2-12 shows SQL functions that explicitly convert a value from one datatype to another.

You cannot specify LONG and LONG RAW values in cases in which Oracle can perform implicit datatype conversion. For example, LONG and LONG RAW values cannot appear in expressions with functions or operators. Please refer to "LONG Datatype" for information on the limitations on LONG and LONG RAW datatypes.