This section describes how Oracle Database compares values of each data type.
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 semanticsA later date 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 '05-JAN-2006 1:35pm' is greater than '05-JAN-2005 10:09am'.
Character values are compared on the basis of two measures:
Binary or linguistic sorting
Blank-padded or nonpadded comparison semantics
The following subsections describe the two measures.
Binary and Linguistic Comparisons
In binary comparison, which is the default, Oracle compares character strings according to the concatenated value of the numeric codes of the characters in the database character set. One character is greater than another if it has a greater numeric value than the other in the character set. Oracle considers blanks to be less than any character, which is true in most character sets.
These are some common character sets:
7-bit ASCII (American Standard Code for Information Interchange)
EBCDIC Code (Extended Binary Coded Decimal Interchange Code)
ISO 8859/1 (International Organization for Standardization)
JEUC Japan Extended UNIX
Linguistic comparison is useful if the binary sequence of numeric codes does not match the linguistic sequence of the characters you are comparing. Linguistic comparison is used if the NLS_SORT parameter has a setting other than BINARY and the NLS_COMP parameter is set to LINGUISTIC. In linguistic sorting, all SQL sorting and comparison are based on the linguistic rule specified by NLS_SORT.
See Also:
Oracle Database Globalization Support Guide for more information about linguistic sortingBlank-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 |
|---|---|
'ac' > 'ab' |
'ac' > 'ab' |
'ab' > 'a ' |
'ab' > 'a ' |
'ab' > 'a' |
'ab' > 'a' |
'ab' = 'ab' |
'ab' = 'ab' |
'a ' = 'a' |
'a ' > 'a' |
Portions of the ASCII and EBCDIC character sets appear in Table 2-8 and Table 2-9. Uppercase and lowercase letters are not equivalent. The numeric values for the characters of a character set may not match the linguistic sequence for a particular language.
| Symbol | Decimal value | Symbol | Decimal value |
|---|---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
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.
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
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.
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 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.
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.
Oracle Database automatically converts a value from one data type to another when such a conversion makes sense.
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.
Table 2-10 Implicit Type Conversion Matrix
| CHAR | VARCHAR2 | NCHAR | NVARCHAR2 | DATE | DATETIME/INTERVAL | NUMBER | BINARY_FLOAT | BINARY_DOUBLE | LONG | RAW | ROWID | CLOB | BLOB | NCLOB | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
CHAR |
-- |
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 |
|
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 |
-- |
-- |
-- |
-- |
-- |
-- |
|
BINARY_FLOAT |
X |
X |
X |
X |
-- |
-- |
X |
-- |
X |
-- |
-- |
-- |
-- |
-- |
-- |
|
BINARY_DOUBLE |
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 |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
|
CLOB |
X |
X |
X |
X |
-- |
-- |
-- |
-- |
-- |
X |
-- |
-- |
-- |
-- |
X |
|
BLOB |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
X |
-- |
-- |
-- |
-- |
|
NCLOB |
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.
The following rules govern implicit data type conversions:
During INSERT and UPDATE 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 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 data type such as VARCHAR2, or converting BLOB to RAW 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 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 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 or NCHAR.
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 and NCHAR/NVARCHAR2, Oracle converts to a NUMBER.
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.
When converting RAW or LONG RAW data to or from character data, the binary data is represented in hexadecimal form, with one hexadecimal character representing every four bits of RAW data. Refer to "RAW and LONG RAW Data Types" for more information.
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.
Table 2-11 Conversion Direction of Different Character Types
| 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.
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';
You can explicitly specify data type conversions using SQL conversion functions. Table 2-12 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-12 Explicit Type Conversions
| 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 | |
|---|---|---|---|---|---|---|---|---|---|
|
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 |
|
|
|
|
|
|
|
|
|
See Also:
"Conversion Functions" for details on all of the explicit conversion functionsWhen 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 riskNote:
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