This appendix describes collation derivation and determination rules for SQL operations. This appendix contains the following topics:
The process of determining the collation of a character result of an SQL operation is called collation derivation. Such operation may be an operator, column reference, character literal, bind variable reference, function call, CASE expression, or a query clause.
Each character value in an SQL expression has a derived collation and a derived coercibility level.
The derived collation and coercibility level of the basic expressions is described in the following table.
Table C1 Derived Collation and Derived Coercibility Level of Various Expression Types
Type of Expression  Derived Collation  Derived Coercibility Level 

Result of the 
The named collation or the pseudocollation specified in the 
0 
Data container reference such as table, view, or materialized view column reference 
The declared named collation or the pseudocollation of the data container. 
2 
Result of a PL/SQL function call or a userdefined operator 

2 
Character literal 

4 
Character bind variable reference 

4 
Note:
Coercibility level 1 corresponds to no collation assigned
Coercibility level 3 is reserved for future use
The derived collation and coercibility level of an operation’s result is based on the collations and coercibility levels of the operation's arguments. A derivationrelevant character argument of an operation is an argument used to derive the collation of the operator’s result. An operator may have zero or more derivationrelevant character arguments, and zero or more other character arguments, such as flags or other control information not directly interacting with the derivationrelevant arguments. An argument is considered derivationrelevant, if its value is included in the result, either after some transformation or without undergoing any transformation.
An argument that is a format model, a pattern, a flag string, or a key into a virtual table of system information is not considered a derivationrelevant argument. For example, the builtin function TO_CHAR(arg1,arg2,arg3)
has no derivationrelevant arguments, as the main argument arg1
is not of a character data type. The two character arguments arg2
and arg3
are not derivationrelevant arguments as they only define the format and parameters for the conversion of the main argument arg1
.
The derived collation and coercibility level of the result of an operation without derivationrelevant arguments are the same as when a character literal would have been put in that expression in the place of the operation.
The following are the collation derivation rules for operations that return character values and have derivationrelevant arguments. These rules are applied recursively in an expression tree. These rules are based on the SQL standard version ISO/IEC 90752:1999.
The derived collation of a result of an operation with derivationrelevant character arguments arg1, arg2, …, argn is:
If at least one argument has the coercibility level 0, then all the arguments with coercibility level 0 must have the same collation, which is the derived collation of the result. The coercibility level of the result is 0. If two arguments with coercibility level 0 have different collations, then an error is reported.
Otherwise, if at least one argument has the coercibility level 1, then the expression result has the coercibility level 1 and no collation is assigned to it.
Otherwise, if LCL is the numerically lowest coercibility level of the arguments, then:
If all the arguments with LCL have the same collation, then this collation is the derived collation of the result, and the coercibility level of the result is LCL.
Otherwise, the result of the expression has the coercibility level 1 and no collation is assigned to it.
Note:
Set operators have arguments that are expression lists. For set operators, collation derivation is performed separately on corresponding elements of each of the arguments of the expression list. For example, in the query:
SELECT expr1, expr2 FROM t1 UNION SELECT expr3, expr4 FROM t2
the collation is derived separately for the first and the second column of the result set. For the first column, the collation derivation rules are applied to expr1
and expr3
. For the second column, the rules are applied to expr2
and expr4
.
Collation determination is the process of selecting the right collation to apply during the execution of a collationsensitive operation. A collationsensitive operation can be an SQL operator, condition, builtin function call, CASE expression or a query clause.
For Oracle Database releases earlier to 12.2, collation to be applied by an operation is determined by only the NLS_SORT
and NLS_COMP
session parameters.
Note:
The optional second parameters to NLS_UPPER, NLS_LOWER, NLS_INITCAP,
and NLSSORT
are exceptions.
Starting from Oracle Database 12.2, collation to be applied by an operation is determined by the derived databound collations of its arguments. Once a pseudocollation is determined as the collation to use, NLS_SORT
and NLS_COMP
session parameters are checked to provide the actual named collation to apply.
Note:
The collation determination does not have to apply to the same operation to which collation derivation applies. For example, TO_CHAR
function is not collationsensitive, so it does not need collation determination. But, TO_CHAR
function returns a character result that needs a collation declaration, hence collation derivation applies to it. Conversely, INSTR
function needs to match characters and needs a collation determined for this match operation. However, the result of INSTR
function is a number, hence no collation derivation is required for it.
The determinationrelevant character argument of an operation is an argument used to determine the collation to be used by the operation. A collationsensitive operation may have one or more determinationrelevant character arguments and zero or more other character arguments, such as flags or other control information not directly interacting with the determinationrelevant arguments.
An argument is considered determinationrelevant, if its value is compared during the evaluation of an operation. An argument that is a format model, a flag string, or a key into a virtual table of system information is not considered a determinationrelevant argument. However, a pattern argument can be a determinationrelevant argument. For example, two of the three arguments of the LIKE
predicate – argument and pattern – are determinationrelevant arguments. The third argument – the escape character – is not considered determinationrelevant argument. Another example is the builtin function REGEXP_COUNT
, which has four arguments – source_char, pattern, position, and match_param. The determinationrelevant arguments are source_char and pattern, which contain the strings to be compared. The nondeterminationrelevant character argument are position, which is numeric, and match_param, which provides parameters for the matching operation.
The following are the collation determination rules to determine the collation to use for an operation with determinationrelevant character arguments arg1, arg2, …, argn. These rules are based on the SQL standard version ISO/IEC 90752:1999.
If operation is the equality condition and is used to enforce a foreign key constraint, then the collation to be used is the declared collation of the primary or unique key column being referenced. This declared collation must be the same as the declared collation of the foreign key column.
Otherwise, if at least one argument has the derived coercibility level 0, then all the arguments with coercibility level 0 must have the same collation, and this collation is used by the operation. If two arguments with coercibility level 0 have different collations, then an error is reported.
Otherwise, if at least one argument has the derived coercibility level 1, then an error is reported.
Otherwise, if LCL is the numerically lowest coercibility level of the arguments, then:
If all arguments with LCL have the same collation, then that collation is used by the operation.
Otherwise, an error is reported.
When the determined collation is a pseudocollation, then the affected operation must refer to the session or database settings NLS_SORT
or NLS_COMP
or both to determine the actual named collation to apply. The database settings are used for expressions in virtual columns, CHECK
constraints, and fine grained auditing (FGA) rules.
The collation determination rules for an operation involving a CLOB
or an NCLOB
data type value must result in the pseudocollation USING_NLS_COMP
, otherwise an error is reported.
Note:
Some conditions, set operators, and query clauses have arguments which are expression lists. In this case, collation determination is performed on the corresponding compared elements of each of the arguments in the expression list. For example, in the condition:
(expr1, expr2) IN (SELECT expr3, expr4 FROM t1)
the collation is determined separately for the pairs of compared elements. First, the collation determination rules are applied to expr1
and expr3
. Then, the rules are applied to expr2
and expr4
. When the condition is evaluated, values of expr1
are compared to values of expr3
using the first determined collation and values of expr2
are compared to values of expr4
using the second determined collation. Similarly, in the query:
SELECT expr1, expr2 FROM t1 MINUS SELECT expr3, expr4 FROM t2
the collation determination rules are first applied to expr1
and expr3
, then to expr2
and expr4
. When the MINUS
operator is evaluated, values of expr1
are compared to values of expr3
using the first determined collation and values of expr2
are compared to values of expr4
using the second determined collation.
In the query:
SELECT * FROM t1 ORDER BY expr1, expr2, expr3
rows are sorted first on values of expr1
using the derived collation expr1
, then ties are broken by sorting on values of expr2
using the derived collation expr2
, and then on values of expr3
using the derived collation expr3
. Each position in the ORDER BY
list is treated like a separate comparison operator for row values.
The following table lists all the SQL operations that return a character value or are collationsensitive or both. For each operation returning a character value, the table lists operation’s derivationrelevant arguments. If the operation has no such arguments, the fixed collation of the operation's result is shown instead. The term Literal Collation means that the collation derived for the operation's result is the collation of a character literal put in place of the operation in an expression; this is either USING_NLS_COMP
for toplevel SQL statements or the default collation of a view, materialized view, or a PL/SQL stored unit containing the expression in its source. For each collationsensitive operation, the following table lists the operation’s determinationrelevant arguments.
Table C2 Derivation and Determinationrelevant Arguments for SQL Operations
Operation Type  Operation Name  Operation Description  Derivationrelevant Arguments or Fixed Collation  Determinationrelevant Arguments 

Pseudocolumn 
VERSIONS_OPERATION 
Operation type in a flashback version query 
Literal collation 
— 
Pseudocolumn 
COLUMN_VALUE 
Value of nested table element of character data type 
USING_NLS_COMP 
— 
Operator 
a_{1}  a_{2} 
Character Value Concatenation 
a_{1}, a_{2} 
— 
Operator 
PRIOR a_{1}

Hierarchical query parent value 
a_{1} 
— 
Operator 
CONNECT_BY_ROOT a_{1}

Hierarchical query root value 
a_{1} 
— 
Operator 
SELECT a_{11}, a_{21},...a_{m1} FROM ... UNION ALL SELECT a_{12}, a_{22},...a_{m2} FROM ... 
Nondistinct union of two row sets 
a_{11}, a_{12}, a_{21}, a_{22}, ...a_{m1}, a_{m2} Collation for each column of the resulting row set is derived separately by combining collations of columns from each of the two argument row sets. Special case: if an argument 
— 
Operator 
SELECT a_{11}, a_{21},...a_{m1} FROM ... UNION SELECT a_{12}, a_{22},...a_{m2} FROM ... 
Distinct union of two row sets 
a_{11}, a_{12}, a_{21}, a_{22}, ...a_{m1}, a_{m2} Collation for each column of the resulting row set is derived separately by combining collations of columns from each of the two argument row sets. 
a_{11}, a_{12}, a_{21}, a_{22}, ...a_{m1}, a_{m2} Collation for comparison of each column of the argument row set is determined separately by combining collations of columns from each of the two argument row sets. 
Operator 
SELECT a_{11}, a_{21},...a_{m1} FROM ... INTERSECT SELECT a_{12}, a_{22},...a_{m2} FROM ... 
Distinct intersection of two row sets 
a_{11}, a_{12}, a_{21}, a_{22}, ...a_{m1}, a_{m2} Collation for each column of the resulting row set is derived separately by combining collations of columns from each of the two argument row sets. 
a_{11}, a_{12}, a_{21}, a_{22}, ...a_{m1}, a_{m2} Collation for comparison of each column of the argument row set is determined separately by combining collations of columns from each of the two argument row sets. 
Operator 
SELECT a_{11}, a_{21},...a_{m1} FROM ... MINUS SELECT a_{12}, a_{22},...a_{m2} FROM ... 
Distinct subtraction of row sets 
a_{11}, a_{12}, a_{21}, a_{22}, ...a_{m1}, a_{m2} Collation for each column of the resulting row set is derived separately by combining collations of columns from each of the two argument row sets. 
a_{11}, a_{12}, a_{21}, a_{22}, ...a_{m1}, a_{m2} Collation for comparison of each column of the argument row set is determined separately by combining collations of columns from each of the two argument row sets. 
Expression 
CASE WHEN c_{1} THEN r_{1} WHEN c_{2} THEN r_{2} ... WHEN c_{n} THEN r_{n} ELSE r_{n+1} END 
Searched case expression 
r_{1},r_{2},...r_{n},r_{n+1} 
Each condition 
Expression 
CASE v WHEN s_{1} THEN r_{1} WHEN s_{2} THEN r_{2} ... WHEN s_{n} THEN r_{n} ELSE r_{n+1} END 
Simple case expression; equivalent to: CASE WHEN v=s_{1} THEN r_{1} WHEN v=s_{2} THEN r_{2} ... WHEN v=s_{n} THEN r_{n} ELSE r_{n+1} END 
r_{1},r_{2},...r_{n},r_{n+1} 
v, s_{1}, s_{2}, ...s_{n} If collation of
v does not dominate over collations of:
s_{1}, s_{2}, ...s_{n}then simple case is transformed to searched case internally. 
Expression 
Object Access Expression 
Reference to an object method 
USING_NLS_COMP 
— 
Expression 
:name 
Bind variable reference 
Literal collation 
— 
Expression 
(a_{1},...a_{n}) 
Expression list 
Each list element has its collation derived separately and independently. 
When two lists are compared, the collation determination is performed separately and independently for each of the two character data type elements at the same index in both the lists. 
Condition 
a_{1} = a_{2} a_{1} <> a_{2} a_{1} < a_{2 }a_{1} > a_{2} a_{1} >= a_{2} a_{1} <= a_{2} 
Simple comparison conditions 
— 
a_{1}, a_{2} If 
Condition 
a_{1} = ANY (a_{2}, ...a_{n}) a_{1} <> ANY (a_{2,} ...a_{n}) a_{1} < ANY (a_{2}, ...a_{n}) a_{1} > ANY (a_{2}, ...a_{n}) a_{1} >= ANY (a_{2}, ...a_{n}) a_{1} <= ANY (a_{2}, ...a_{n}) ( 
List comparison condition; equivalent to: a_{1} <op> a_{2} ANDOR a_{1} <op> a_{3} ANDOR ... a_{1} <op> a_{n} 
— 
a_{1}, a_{2} a_{1}, a_{3} ... a_{1}, a_{n} Collations are determined separately for each pair. If 
Condition 
a_{1} = ANY (SELECT a_{2} FROM ...) a_{1} <> ANY (SELECT a_{2} FROM ...) a_{1} < ANY (SELECT a_{2} FROM ...) a_{1} > ANY (SELECT a_{2} FROM ...) a_{1} >= ANY (SELECT a_{2} FROM ...) a_{1} <= ANY (SELECT a_{2} FROM ...) ( 
Query comparison conditions 
— 
a_{1}, a_{2}
If 
Condition 
a_{1} [NOT] LIKE [24C] a_{2} ESCAPE a_{3} 
Check if pattern 
— 
a_{1}, a_{2} 
Condition 
REGEXP_LIKE(a_{1},a_{2},[a_{3}]) 
Check if regular expression 
— 
a_{1}, a_{2} 
Condition 
a_{1} [NOT] BETWEEN a_{2} AND a_{3} 
Range comparison; equivalent to: a_{1} >= a_{2} AND a_{1} <= a_{3} 
— 
a_{1}, a_{2} a_{1}, a_{3} Collation is determined separately for each comparison. 
Condition 
a_{1} [NOT] IN (a_{2},a_{3},...a_{n}) 
Membership comparison; equivalent to: a_{1} = ANY(a_{2},a_{3},...a_{n}) 
— 
See =ANY above 
Function 
APPROX_COUNT_DISTINCT(a_{1})

Approximate count of distinct values of 
— 
a_{1} 
Function 
ASCIISTR(a_{1})

Escape nonASCII characters in 
a_{1} 
— 
Function 
CAST(a_{1} AS <character data type>) 
Cast value 

— 
Function 
CHR(a_{1})

Convert numeric code 
Literal collation 
— 
Function 
COALESCE(a_{1},a_{2},...a_{n}) 
First nonnull value among:
CASE WHEN a_{1} IS NOT NULL THEN a_{1} ELSE a_{2} END;
CASE WHEN a_{1} IS NOT NULL THEN a_{1} ELSE COALESCE (a_{2},...a_{n}) END; 
a_{1}, a_{2}, ...a_{n} 
— 
Function 
COLLATION(a_{1})

Return name of derived collation of 
Literal collation 
— 
Function 
COLLECT( [DISTINCT] a_{1} ORDER BY a_{2}) 
Aggregate into a nested table 
— 

Function 
COMPOSE(a_{1})

Normalize 
a_{1} 
— 
Function 
CONCAT(a_{1},a_{2}) 
Concatenate strings 
a1, a2 
— 
Function 
CONVERT(a_{1}[,a_{2}[,a_{3}]]) 
Convert character set of 
a_{1} 
— 
Function 
COUNT(DISTINCT a_{1})

Count distinct values of 
— 
a_{1} 
Function 
CORR_K(a_{1},a_{2},a_{3}) 
Kendall's taub correlation coefficient 
— 
a_{1}, a_{2} Collation is determined independently for each argument. 
Function 
CORR_S(a_{1},a_{2},a_{3}) 
Spearman's rho correlation coefficient 
— 
a_{1}, a_{2} Collation is determined independently for each argument. 
Function 
CUBE_TABLE(...) 
OLAP cube or hierarchy to relational table 
Literal collation (for each character data type column in the generated table) 
— 
Function 
CV([a_{1}])

Current dimension value in a model clause 
Collation of the dimension column to which CV() call corresponds, 
— 
Function 
DBTIMEZONE 
Database time zone as string 
Literal collation 
— 
Function 
DECODE(v_{1},s_{1},r_{1},s_{2},r_{2},...,s_{n},r_{n},r_{n+1}) 
Value selection 
r_{1},r_{2},...r_{n},r_{n+1} 
v_{1}, s_{1}, s_{2}, ...s_{n} 
Function 
DECOMPOSE(a_{1},a_{2}) 
Unicode normalization (NFD, NFKD); 
a1 
— 
Function 
DENSE_RANK([a_{1},a_{2},...a_{n}]) 
Dense rank of a value in a group of values 
— 
Ranking is based on collation of the elements in function’s 
Function 
DUMP(a_{1}[,a_{2}[,a_{3}[,a_{4}]]]) 
Debugging dump of 
Literal collation 
— 
Function 
EMPTY_CLOB 
Empty 
USING_NLS_COMP 
— 
Function 
EXTRACT(
TIMEZONE_REGION 
TIMEZONE_ABBR
FROM a_{1})

Extract time zone information from the datetime value 
Literal collation 
— 
Function 
EXTRACTVALUE(a_{1},a_{2}[,a_{3}]) 
Extract element value from 
Literal collation 
— 
Function 
FIRST_VALUE(a_{1})

First value of 
a_{1} 
— 
Function 
GREATEST(a_{1},a_{2},...a_{n}) 
Largest value among 
a_{1}, a_{2}, ...a_{n} 
a_{1}, a_{2}, ...a_{n} 
Function 
INITCAP(a_{1})

Capitalize initial letters of 
a_{1} 
— 
Function 
INSTR[B24C](a_{1},a_{2}[,a_{3}[,a_{4}]]) 
Position of 
— 
a_{1}, a_{2} 
Function 
JSON_QUERY(a_{1},a_{2},...) 
Retrieve fragment of the JSON object 
Literal collation 
— 
Function 
JSON_TABLE(a_{1},a_{2},...) 
Present fragment of the JSON object 
Literal collation (for each character data type column in the generated table) 
— 
Function 
JSON_VALUE(a_{1},a_{2},...) 
Retrieve a scalar value from the JSON object 
Literal collation 
— 
Function 
LAG(a_{1}[,a_{2}[,a_{3}]]) 
Value of 
a_{1} 
— 
Function 
LAST_VALUE(a_{1})

Last value of 
a_{1} 
— 
Function 
LEAD(a_{1}[,a_{2}[,a_{3}]]) 
Value of 
a_{1} 
— 
Function 
LEAST(a_{1},a_{2},...a_{n}) 
Smallest value among 
a_{1}, a_{2}, ...a_{n} 
a_{1}, a_{2},...a_{n} 
Function 
LISTAGG(a_{1}[,a_{2}]) 
Aggregate values of 

— 
Function 
LOWER(a_{1})

Lowercase 
a_{1} 
— 
Function 
LPAD(a_{1},a_{2}[,a_{3}]) 
Pad string 
a_{1} 
— 
Function 
LTRIM(a_{1}[,a_{2}]) 
Remove characters from the beginning of 
a_{1} 
a_{1} 
Function 
MAX(a_{1})

Maximum value of 
a_{1} 
a_{1} 
Function 
MIN(a_{1})

Minimum value of 
a_{1} 
a_{1} 
Function 
NCHR(a_{1})

Convert numeric code 
Literal collation 
— 
Function 
NLS_CHARSET_NAME(a_{1})

Name of the character set with ID 
Literal collation 
— 
Function 
NLS_COLLATION_NAME(a_{1})

Name of the collation with ID 
Literal collation 
— 
Function 
NLS_INITCAP(a_{1}[,a_{2}]) 
Capitalize initial letters of 
a_{1} 
Collation specified with

Function 
NLS_LOWER(a_{1}[,a_{2}]) 
Lowercase 
a_{1} 
Collation specified with

Function 
NLS_UPPER(a_{1}[,a_{2}]) 
Capitalize 
a_{1} 
Collation specified with

Function 
NLSSORT(a_{1}[,a_{2}]) 
Generate collation key for 
— 
Collation specified with 
Function 
NTH_VALUE(a_{1},n) 
The nth value of 
a_{1} 
— 
Function 
NULLIF(a_{1},a_{2}) 
This is equivalent to: CASE WHEN a_{1}=a_{2} THEN NULL ELSE a_{1} END; 
a_{1} 
a_{1}, a_{2} 
Function 
NVL(a_{1},a_{2}) 

a_{1}, a_{2} 
— 
Function 
NVL2(a_{1},a_{2},a_{3}) 

a_{2}, a_{3} 
— 
Function 
ORA_INVOKING_USER 
Invoking user name 
Literal collation 
— 
Function 
PATH(a_{1})

Path to a resource 
Literal collation 
— 
Function 
PERCENT_RANK([a_{1},a_{2},...a_{n}])... 
Percent rank of a value in a group of values 
— 
Ranking is based on collation of the elements in function’s 
Function 
PREDICTION 
Data mining prediction 
Literal collation 
— 
Function 
PRESENTNNV(a_{1},a_{2},a_{3}) 
If the cell reference 
a_{2}, a_{3} 
— 
Function 
PRESENTV(a_{1},a_{2},a_{3}) 
If the cell reference 
a_{2}, a_{3} 
— 
Function 
PREVIOUS(a_{1})

Value of the cell reference a1 at the beginning of an iteration in a model clause 
a_{1} 
— 
Function 
RANK([a_{1},a_{2},...a_{n}]) 
Rank of a value in a group of values 
— 
Ranking is based on collation of the elements in function’s 
Function 
RAWTOHEX(a_{1})

Convert the RAW value 
Literal collation 
— 
Function 
RAWTONHEX(a_{1})

Convert the 
Literal collation 
— 
Function 
REGEXP_COUNT(a_{1},a_{2}[, a_{3}[,a_{4}]]) 
Number of times regular expression 
— 
a_{1}, a_{2} 
Function 
REGEXP_INSTR(a_{1},a_{2}[, a_{3}[,a_{4}[,a_{5}[,a_{6}[,a_{7}]]]]]) 
Minimal position in 
— 
a_{1}, a_{2} 
Function 
REGEXP_REPLACE(a_{1},a_{2}[, a_{3}[,a_{4}[,a_{5}[,a_{6}]]]]) 
Replace with string 
a_{1} 
a_{1}, a_{2} 
Function 
REGEXP_SUBSTR(a_{1},a_{2}[, a_{3}[,a_{4}[,a_{5}[,a_{6}]]]]) 
Return the 
a_{1} 
a_{1}, a_{2} 
Function 
REPLACE(a_{1},a_{2}[,a_{3}]) 

a_{1} 
a_{1}, a_{2} 
Function 
ROWIDTOCHAR(a_{1})

Convert the rowid 
Literal collation 
— 
Function 
ROWIDTONCHAR(a_{1})

Convert the rowid 
Literal collation 
— 
Function 
RPAD(a_{1},a_{2}[,a_{3}]) 
Pad string 
a_{1} 
— 
Function 
RTRIM(a_{1}[,a_{2}]) 
Remove characters from the end of 
a_{1} 
a_{1} 
Function 
SESSIONTIMEZONE 
Database time zone as string 
Literal collation 
— 
Function 
SOUNDEX(a_{1})

Soundex representation of 
a_{1} 
— 
Function 
STATS_BINOMIAL_TEST(a_{1},a_{2},a_{3}[,a_{4}]) 
Exact probability test of dichotomous variables 
— 
a_{1} 
Function 
STATS_CROSSTAB(a_{1},a_{2}[, a_{3}]) 
Crosstab analysis of 
— 
a_{1}, a_{2} Collation is determined independently for each argument. 
Function 
STATS_F_TEST(a_{1},a_{2}[, a_{3}[,a_{4}]]) 
Variance analysis of 
— 
a_{1} 
Function 
STATS_KS_TEST(a_{1},a_{2}[, a_{3}]) 
KolmogorovSmirnov function 
— 
a_{1}, a_{2} Collation is determined independently for each argument. 
Function 
STATS_MODE(a_{1})

Most frequent value of 
a_{1} 
a_{1} 
Function 
STATS_MW_TEST(a_{1},a_{2}[, a_{3}]) 
Mann Whitney test 
— 
a_{1}, a_{2} Collation is determined independently for each argument. 
Function 
STATS_ONE_WAY_ANOVA(a_{1},a_{2}[,a_{3}]) 
Oneway analysis of variance 
— 
a_{1} 
Function 
STATS_T_TEST_INDEP(a_{1},a_{2}[,a_{3}[,a_{4}]]) 
Ttest of independent groups with same variance 
— 
a_{1} 
Function 
STATS_T_TEST_INDEPU(a_{1},a_{2}[,a_{3}[,a_{4}]]) 
Ttest of independent groups with unequal variance 
— 
a_{1} 
Function 
SUBSTR[B24C](a_{1},a_{2}[,a_{3}]) 
Substring of 
a_{1} 
— 
Function 
SYS_CONNECT_BY_PATH(a_{1},a_{2}) 
Path of value 
a_{1} 
— 
Function 
SYS_CONTEXT(a_{1},a_{2}[,a_{3}]) 
Context parameter 
Literal collation 
— 
Function 
TO_CHAR(a_{1})
/*character*/ 
Convert 
a_{1} 
— 
Function 
TO_CHAR(a_{1}[,a_{2}[,a_{3}]]) /*datetime*/ 
Convert 
Literal collation 
— 
Function 
TO_CHAR(a_{1}[,a_{2}[,a_{3}]]) /*number*/ 
Convert 
Literal collation 
— 
Function 
TO_CLOB(a_{1})

Convert 
(must yield 
— 
Function 
TO_LOB(a_{1})
/*long*/ 
Convert 
(must yield 
— 
Function 
TO_MULTI_BYTE(a_{1})

Map normalwidth characters in 
a_{1} 
— 
Function 
TO_NCHAR(a_{1})
/*character*/ 
Convert 
a_{1} 
— 
Function 
TO_NCHAR(a_{1}[,a_{2}[,a_{3}]]) /*datetime*/ 
Convert 
Literal collation 
— 
Function 
TO_NCHAR(a_{1}[,a_{2}[,a_{3}]]) /*number*/ 
Convert 
Literal collation 
— 
Function 
TO_NCLOB(a_{1})

Convert 
a_{1}
(must yield 
— 
Function 
TO_SINGLE_BYTE(a_{1})

Map fullwidth characters in 
a_{1} 
— 
Function 
TRANSLATE(a_{1},a_{2},a_{3}) 
Transform 
a1 
a_{1} 
Function 
TRANSLATE(a_{1} USING CHAR_CSNCHAR_CS)

Convert (roughly equivalent to: 
a_{1} 
— 
Function 
TRIM([[LEADINGTRAILINGBOTH] [a_{1}] FROM] a_{2}) 
Remove all occurrences of character 
a_{2} 
a_{2} 
Function 
TZ_OFFSET(a_{1})

Offset for the time zone 
Literal collation 
— 
Function 
UNISTR 
Transform string 
a_{1} 
— 
Function 
UPPER(a_{1})

Capitalize string 
a_{1} 
— 
Function 
USER 
Login user name 
Literal collation 
— 
Function 
USERENV(a_{1})


Literal collation 
— 
Function 
XMLCAST(a_{1} AS <data type>) 
Cast result of 
Literal collation 
— 
Function 
XMLSERIALIZE(... a_{1}
[AS VARCHAR2  CLOB]...)

Serialize XML document 
Literal collation 
— 
Function 
XMLTABLE(... COLUMNS col_{1} <data type> ... col_{n} <data type>...) 
Present content of an XML object as a virtual relational table 
Literal collation (for each character data type column in the generated table) 
— 
Clause 
OVER(PARTITION BY a_{1}, a_{2}, ...a_{n}) 
Analytic clause partitioning 
— 
a_{1} a_{2} ... a_{n} Collation is determined separately for each character argument in the clause. 
Clause 
OVER(ORDER BY a_{1}, a_{2}, ...a_{n}) 
Analytic clause ordering 
— 
a_{1} a_{2} ... a_{n} Collation is determined separately for each character argument in the clause. 
Clause 
ORDER BY a_{1}, a_{2}, ...a_{n} 
Aggregate function ordering 
— 
a_{1} a_{2} ... a_{n} Collation is determined separately for each character argument in the clause. 
Clause 
ORDER BY a_{1}, a_{2}, ...a_{n} 
Query result ordering 
— 
a_{1} a_{2} ... a_{n} Collation is determined separately for each character argument in the clause. 
Clause 
GROUP BY a_{1}, a_{2}, ...a_{n} 
Query row grouping 
— 
a_{1} a_{2} ... a_{n} Collation is determined separately for each character argument in the clause. 