Data Type Comparison Rules
This section describes how Oracle Database compares values of each data type.
Numeric Values
A larger value is considered greater than a smaller one. All negative numbers are less than zero and all positive numbers. Thus, -1 is less than 100; -100 is less than -1.
The floating-point value NaN
(not a number) is greater than any other numeric value and is equal to itself.
See Also:
Numeric Precedence and Floating-Point Numbers for more information on comparison semantics
Datetime Values
A later date or timestamp is considered greater than an earlier one. For example, the date equivalent of '29-MAR-2005' is less than that of '05-JAN-2006' and the timestamp equivalent of '05-JAN-2006 1:35pm' is greater than that of '05-JAN-2005 10:09am'.
When two timestamps with time zone are compared, they are first normalized to UTC, that is, to the timezone offset '+00:00'. For example, the timestamp with time zone equivalent of '16-OCT-2016 05:59am Europe/Warsaw' is equal to that of '15-OCT-2016 08:59pm US/Pacific'. Both represent the same absolute point in time, which represented in UTC is October 16th, 2016, 03:59am.
Binary Values
A binary value of the data type RAW
or BLOB
is a sequence of bytes. When two binary values are compared, the corresponding, consecutive bytes of the two byte sequences are compared in turn. If the first bytes of both compared values are different, the binary value that contains the byte with the lower numeric value is considered smaller. If the first bytes are equal, second bytes are compared analogously, and so on, until either the compared bytes differ or the comparison process reaches the end of one of the values. In the latter case, the value that is shorter is considered smaller.
Binary values of the data type BLOB
cannot be compared directly in comparison conditions. However, they can be compared with the PL/SQL function DBMS_LOB
.COMPARE
.
See Also:
Oracle Database PL/SQL
Packages and Types Reference for more information on the DBMS_LOB
.COMPARE
function
Character Values
Character values are compared on the basis of two measures:
-
Binary or linguistic collation
-
Blank-padded or nonpadded comparison semantics
The following subsections describe the two measures.
Binary and Linguistic Collation
In binary collation, which is the default, Oracle compares character values like binary values. Two sequences of bytes that form the encodings of two character values in their storage character set are treated as binary values and compared as described in Binary Values. The result of this comparison is returned as the result of the binary comparison of the source character values.
See Also:
Oracle Database Globalization Support Guide for more information on character sets
For many languages, the binary collation can yield a linguistically incorrect ordering of character values. For example, in most common character sets, all the uppercase Latin letters have character codes with lower values than all the lowercase Latin letters. Hence, the binary collation yields the following order:
MacDonald
MacIntosh
Macdonald
Macintosh
However, most users expect these four values to be presented in the order:
MacDonald
Macdonald
MacIntosh
Macintosh
This shows that binary collation may not be suitable even for English character values.
Oracle Database supports linguistic collations that order strings according to rules of various spoken languages. It also supports collation variants that match character values case- and accent-insensitively. Linguistic collations are more expensive but they provide superior user experience.
See Also:
Oracle Database Globalization Support Guide for more information about linguistic sorting
Restrictions for Linguistic Collations
Comparison conditions, ORDER
BY
, GROUP
BY
and MATCH_RECOGNIZE
query clauses, COUNT(DISTINCT)
and statistical aggregate functions, LIKE
conditions, and ORDER
BY
and PARTITION
BY
analytic clauses generate collation keys when using linguistic collations. The collation keys are the same values that are returned by the function NLSSORT
and are subject to the same restrictions that are described in NLSSORT.
Blank-Padded and Nonpadded Comparison Semantics
With blank-padded semantics, if the two values have different lengths, then Oracle first adds blanks to the end of the shorter one so their lengths are equal. Oracle then compares the values character by character up to the first character that differs. The value with the greater character in the first differing position is considered greater. If two values have no differing characters, then they are considered equal. This rule means that two values are equal if they differ only in the number of trailing blanks. Oracle uses blank-padded comparison semantics only when both values in the comparison are either expressions of data type CHAR
, NCHAR
, text literals, or values returned by the USER
function.
With nonpadded semantics, Oracle compares two values character by character up to the first character that differs. The value with the greater character in that position is considered greater. If two values of different length are identical up to the end of the shorter one, then the longer value is considered greater. If two values of equal length have no differing characters, then the values are considered equal. Oracle uses nonpadded comparison semantics whenever one or both values in the comparison have the data type VARCHAR2
or NVARCHAR2
.
The results of comparing two character values using different comparison semantics may vary. The table that follows shows the results of comparing five pairs of character values using each comparison semantic. Usually, the results of blank-padded and nonpadded comparisons are the same. The last comparison in the table illustrates the differences between the blank-padded and nonpadded comparison semantics.
Blank-Padded | Nonpadded |
---|---|
|
|
|
|
|
|
|
|
|
|
Data-Bound Collation
Starting with Oracle Database 12c Release 2 (12.2), the collation to use when comparing or matching a given character value is associated with the value itself. It is called the data-bound collation. The data-bound collation can be viewed as an attribute of the data type of the value.
In previous Oracle Database releases, the session parameters NLS_COMP
and NLS_SORT
coarsely determined the collation for all collation-sensitive SQL operations in a database session. The data-bound collation architecture enables applications to consistently apply language-specific comparison rules to exactly the data that needs these rules.
Oracle Database 12c Release 2 (12.2) allows you to declare a collation for a table column. When a column is passed as an argument to a collation-sensitive SQL operation, the SQL operation uses the column's declared collation to process the column's values. If the SQL operation has multiple character arguments that are compared to each other, the collation determination rules determine the collation to use.
There are two types of data-bound collations:
-
Named Collation: This collation is a particular set of collating rules specified by a collation name. Named collations are the same collations that are specified as values for the
NLS_SORT
parameter. A named collation can be either a binary collation or a linguistic collation. -
Pseudo-collation: This collation does not directly specify the collating rules for a SQL operation. Instead, it instructs the operation to check the values of the session parameters
NLS_SORT
andNLS_COMP
for the actual named collation to use. Pseudo-collations are the bridge between the new declarative method of specifying collations and the old method that uses session parameters. In particular, the pseudo-collationUSING_NLS_COMP
directs a SQL operation to behave exactly as it used to behave before Oracle Database 12c Release 2.
When you declare a named collation for a column, you statically determine how the column values are compared. When you declare a pseudo-collation, you can dynamically control comparison behavior with the session parameter NLS_COMP
and NLS_SORT
. However, static objects, such as indexes and constraints, defined on a column declared with a pseudo-collation, fall back to using a binary collation. Dynamically settable collating rules cannot be used to compare values for a static object.
The collation for a character literal or bind variable that is used in an expression is derived from the default collation of the database object containing the expression, such as a view or materialized view query, a PL/SQL stored unit code, a user-defined type method code, or a standalone DML or query statement. In Oracle Database 12c Release 2, the default collation of PL/SQL stored units, user-defined type methods, and standalone SQL statements is always the pseudo-collation USING_NLS_COMP
. The default collation of views and materialized views can be specified in the DEFAULT
COLLATION
clause of the CREATE
VIEW
and CREATE
MATERIALIZED
VIEW
statements.
If a SQL operation returns character values, the collation derivation rules determine the derived collation for the result, so that its collation is known, when the result is passed as an argument to another collation-sensitive SQL operation in the expression tree or to a top-level consumer, such as an SQL statement clause in a SELECT
statement. If a SQL operation operates on character argument values, then the derived collation of its character result is based on the collations of the arguments. Otherwise, the derivation rules are the same as for a character literal.
You can override the derived collation of an expression node, such as a simple expression or an operator result, by using the COLLATE
operator.
Oracle Database allows you to declare a case-insensitive collation for a column, table or schema, so that the column or all character columns in a table or a schema can be always compared in a case-insensitive way.
See Also:
-
Oracle Database Globalization Support Guide for more information on data-bound collation architecture, including the detailed collation derivation and determination rules
Object Values
Object values are compared using one of two comparison functions: MAP
and ORDER
. Both functions compare object type instances, but they are quite different from one another. These functions must be specified as part of any object type that will be compared with other object types.
See Also:
CREATE TYPE for a description of MAP
and ORDER
methods and the values they return
Data Type Precedence
Oracle uses data type precedence to determine implicit data type conversion, which is discussed in the section that follows. Oracle data types take the following precedence:
-
Datetime and interval data types
-
BINARY_DOUBLE
-
BINARY_FLOAT
-
NUMBER
-
Character data types
-
All other built-in data types
Data Conversion
Generally an expression cannot contain values of different data types. For example, an expression cannot multiply 5 by 10 and then add 'JAMES'. However, Oracle supports both implicit and explicit conversion of values from one data type to another.
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 data type conversion functions.
-
Implicit data type conversion can have a negative impact on performance, especially if the data type 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 theNLS_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.
-
If implicit data type conversion occurs in an index expression, then Oracle Database might not use the index because it is defined for the pre-conversion data type. This can have a negative impact on performance.
Implicit Data Conversion
Oracle Database automatically converts a value from one data type to another when such a conversion makes sense.
Table 2-9 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 cells with an 'X' indicate the possible implicit conversions from source to destination data type.
Table 2-9 Implicit Type Conversion Matrix
DataType | CHAR | VARCHAR2 | NCHAR | NVARCHAR2 | DATE | DATETIME/INTERVAL | NUMBER | BINARY_FLOAT | BINARY_DOUBLE | LONG | RAW | ROWID | CLOB | BLOB | NCLOB | BOOLEAN |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
CHAR |
-- |
X |
X |
X |
X |
X |
X |
X |
X |
X |
X |
X |
X |
X |
X |
X |
VARCHAR2 |
X |
-- |
X |
X |
X |
X |
X |
X |
X |
X |
X |
X |
X |
-- |
X |
-- |
NCHAR |
X |
X |
-- |
X |
X |
X |
X |
X |
X |
X |
X |
X |
X |
-- |
X |
X |
NVARCHAR2 |
X |
X |
X |
-- |
X |
X |
X |
X |
X |
X |
X |
X |
X |
-- |
X |
-- |
DATE |
X |
X |
X |
X |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
DATETIME/ INTERVAL |
X |
X |
X |
X |
-- |
-- |
-- |
-- |
-- |
X |
-- |
-- |
-- |
-- |
-- |
-- |
NUMBER |
X |
X |
X |
X |
-- |
-- |
-- |
X |
X |
-- |
-- |
-- |
-- |
-- |
-- |
X |
BINARY_FLOAT |
X |
X |
X |
X |
-- |
-- |
X |
-- |
X |
-- |
-- |
-- |
-- |
-- |
-- |
X |
BINARY_DOUBLE |
X |
X |
X |
X |
-- |
-- |
X |
X |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
X |
LONG |
X |
X |
X |
X |
-- |
-- |
-- |
-- |
-- |
X |
-- |
X |
-- |
X |
-- |
|
RAW |
X |
X |
X |
X |
-- |
-- |
-- |
-- |
-- |
X |
-- |
-- |
-- |
X |
-- |
-- |
ROWID |
X |
X |
X |
X |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
CLOB |
X |
X |
X |
X |
-- |
-- |
-- |
-- |
- |
X |
-- |
-- |
-- |
-- |
X |
-- |
BLOB |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
- |
-- |
X |
-- |
-- |
-- |
-- |
-- |
NCLOB |
X |
X |
X |
X |
-- |
-- |
-- |
-- |
- |
X |
-- |
-- |
X |
-- |
-- |
-- |
JSON |
-- |
X |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
X |
X |
-- |
-- |
BOOLEAN |
X |
X |
X |
X |
-- |
-- |
X |
X |
X |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
Footnote 1
You cannot convert LONG
to INTERVAL
directly, but you can convert LONG
to VARCHAR2
using TO_CHAR
(interval
), and then convert the resulting VARCHAR2
value to INTERVAL
.
Implicit Data Type Conversion Rules
-
During
INSERT
andUPDATE
operations, Oracle converts the value to the data type of the affected column. -
During
SELECT
FROM
operations, Oracle converts the data from the column to the type of the target variable. -
When manipulating numeric values, Oracle usually adjusts precision and scale to allow for maximum capacity. In such cases, the numeric data type resulting from such operations can differ from the numeric data type found in the underlying tables.
-
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 andNUMBER
use decimal precision to represent the numeric value, and the floating-point numbers use binary precision. -
When converting a
CLOB
value into a character data type such asVARCHAR2
, or convertingBLOB
toRAW
data, if the data to be converted is larger than the target data type, then the database returns an error. -
During conversion from a timestamp value to a
DATE
value, the fractional seconds portion of the timestamp value is truncated. This behavior differs from earlier releases of Oracle Database, when the fractional seconds portion of the timestamp value was rounded. -
Conversions from
BINARY_FLOAT
toBINARY_DOUBLE
are exact. -
Conversions from
BINARY_DOUBLE
toBINARY_FLOAT
are inexact if theBINARY_DOUBLE
value uses more bits of precision that supported by theBINARY_FLOAT
. -
When comparing a character value with a
DATE
value, Oracle converts the character data toDATE
. -
When you use a SQL function or operator with an argument of a data type other than the one it accepts, Oracle converts the argument to the accepted data type.
-
When making assignments, Oracle converts the value on the right side of the equal sign (=) to the data type of the target of the assignment on the left side.
-
During concatenation operations, Oracle converts from noncharacter data types to
CHAR
orNCHAR
. -
During arithmetic operations on and comparisons between character and noncharacter data types, Oracle converts from any character data type to a numeric, date, or rowid, as appropriate. In arithmetic operations between
CHAR
/VARCHAR2
andNCHAR
/NVARCHAR2
, Oracle converts to aNUMBER
. -
Most SQL character functions are enabled to accept
CLOB
s as parameters, and Oracle performs implicit conversions betweenCLOB
and character types. Therefore, functions that are not yet enabled forCLOB
s can acceptCLOB
s through implicit conversion. In such cases, Oracle converts theCLOB
s toCHAR
orVARCHAR2
before the function is invoked. If theCLOB
is larger than 4000 bytes, then Oracle converts only the first 4000 bytes toCHAR
. -
When converting
RAW
orLONG
RAW
data to or from character data, the binary data is represented in hexadecimal form, with one hexadecimal character representing every four bits ofRAW
data. Refer to "RAW and LONG RAW Data Types" for more information. -
Comparisons between
CHAR
andVARCHAR2
and betweenNCHAR
andNVARCHAR2
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-10 shows the direction of implicit conversions between different character types.
Table 2-10 Conversion Direction of Different Character Types
SourceData Type | to CHAR | to VARCHAR2 | to NCHAR | to NVARCHAR2 |
---|---|---|---|---|
from CHAR |
- |
|
|
|
from VARCHAR2 |
|
- |
|
|
from NCHAR |
|
|
- |
|
from NVARCHAR2 |
|
|
|
- |
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 data type CHAR
. Oracle implicitly converts it to the NUMBER
data type 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 '24-JUN-06
' to a DATE
value using the default date format 'DD-MON-YY
':
SELECT last_name FROM employees WHERE hire_date = '24-JUN-06';
Explicit Data Conversion
You can explicitly specify data type conversions using SQL conversion functions. Table 2-11 shows SQL functions that explicitly convert a value from one data type to another.
You cannot specify LONG
and LONG
RAW
values in cases in which Oracle can perform implicit data type conversion. For example, LONG
and LONG
RAW
values cannot appear in expressions with functions or operators. Refer to LONG Data Type for information on the limitations on LONG
and LONG
RAW
data types.
Table 2-11 Explicit Type Conversions
SourceData Type | to CHAR,VARCHAR2,NCHAR,NVARCHAR2 | to NUMBER | to Datetime/Interval | to RAW | to ROWID | to LONG,LONG RAW | to CLOB, NCLOB,BLOB | to BINARY_FLOAT | to BINARY_DOUBLE | to BOOLEAN |
---|---|---|---|---|---|---|---|---|---|---|
from CHAR, VARCHAR2, NCHAR, NVARCHAR2 |
|
|
|
|
|
|
|
|
|
|
from NUMBER |
|
|
|
|
|
|
|
|
|
|
from Datetime/ Interval |
|
|
|
|
|
|
|
|
|
|
from RAW |
|
|
|
|
|
|
|
|
|
|
from ROWID |
|
|
|
|
|
|
|
|
|
|
from LONG / LONG RAW |
|
|
|
|
|
|
|
|
|
|
from CLOB, NCLOB, BLOB |
|
|
|
|
|
|
|
|
|
|
from CLOB, NCLOB, BLOB |
|
|
|
|
|
|
|
|
|
|
from BINARY_FLOAT |
|
|
|
|
|
|
|
|
|
|
from BINARY_DOUBLE |
|
|
|
|
|
|
|
|
|
|
from BOOLEAN |
|
|
|
|
|
|
|
|
|
|
See Also:
Conversion Functions for details on all of the explicit conversion functions
Security Considerations for Data Conversion
When a datetime value is converted to text, either by implicit conversion or by explicit conversion that does not specify a format model, the format model is defined by one of the globalization session parameters. Depending on the source data type, the parameter name is NLS_DATE_FORMAT
, NLS_TIMESTAMP_FORMAT
, or NLS_TIMESTAMP_TZ_FORMAT
. The values of these parameters can be specified in the client environment or in an ALTER
SESSION
statement.
The dependency of format models on session parameters can have a negative impact on database security when conversion without an explicit format model is applied to a datetime value that is being concatenated to text of a dynamic SQL statement. Dynamic SQL statements are those statements whose text is concatenated from fragments before being passed to a database for execution. Dynamic SQL is frequently associated with the built-in PL/SQL package DBMS_SQL
or with the PL/SQL statement EXECUTE
IMMEDIATE
, but these are not the only places where dynamically constructed SQL text may be passed as argument. For example:
EXECUTE IMMEDIATE
'SELECT last_name FROM employees WHERE hire_date > ''' || start_date || '''';
where start_date
has the data type DATE
.
In the above example, the value of start_date
is converted to text using a format model specified in the session parameter NLS_DATE_FORMAT
. The result is concatenated into SQL text. A datetime format model can consist simply of literal text enclosed in double quotation marks. Therefore, any user who can explicitly set globalization parameters for a session can decide what text is produced by the above conversion. If the SQL statement is executed by a PL/SQL procedure, the procedure becomes vulnerable to SQL injection through the session parameter. If the procedure runs with definer's rights, with higher privileges than the session itself, the user can gain unauthorized access to sensitive data.
See Also:
Oracle Database PL/SQL Language Reference for further examples and for recommendations on avoiding this security risk
Note:
This security risk also applies to middle-tier applications that construct SQL text from datetime values converted to text by the database or by OCI datetime functions. Those applications are vulnerable if session globalization parameters are obtained from a user preference.
Implicit and explicit conversion for numeric values may also suffer from the analogous problem, as the conversion result may depend on the session parameter NLS_NUMERIC_CHARACTERS
. This parameter defines the decimal and group separator characters. If the decimal separator is defined to be the quotation mark or the double quotation mark, some potential for SQL injection emerges.
See Also:
-
Oracle Database Globalization Support Guide for detailed descriptions of the session globalization parameters
-
Format Models for information on the format models