2 SQL Functions
This chapter presents the syntax for SQL functions.
This chapter includes the following section:
Syntax for SQL Functions
A function is a command that manipulates data items and returns a single value.
The sections that follow show each SQL function and its related syntax. Refer to Subclauses for the syntax of the subclauses.
See Also:
Oracle Database SQL Language Reference for detailed information about SQL functions
ABS(n)
ACOS(n)
ADD_MONTHS(date, integer)
Aggregate functions return a single result row based on groups of rows, rather than on single rows.
analytic_function([ arguments ]) OVER (analytic_clause)
ANY_VALUE ( [ DISTINCT | ALL ] expr )
APPROX_COUNT ( expr [ , expr 'MAX_ERROR' ]... )
APPROX_COUNT_DISTINCT(expr)
APPROX_COUNT_DISTINCT_AGG(detail)
APPROX_COUNT_DISTINCT_DETAIL(expr)
APPROX_MEDIAN( expr [ DETERMINISTIC ] [, { 'ERROR_RATE' | 'CONFIDENCE' } ] )APPROX_PERCENTILE( expr [ DETERMINISTIC ] [, { 'ERROR_RATE' | 'CONFIDENCE' } ] )
WITHIN GROUP ( ORDER BY expr [ DESC | ASC ] )APPROX_PERCENTILE_AGG(expr)
APPROX_PERCENTILE_DETAIL( expr [ DETERMINISTIC ] )
APPROX_RANK ( expr [ PARTITION BY partition_by_clause ] [ ORDER BY order_by_clause DESC] )
APPROX_SUM ( expr [ , expr 'MAX_ERROR' ] ...)
ASCII(char)
ASCIISTR(char)
ASIN(n)
ATAN(n)
ATAN2(n1 , n2)
AVG([ DISTINCT | ALL ] expr) [ OVER(analytic_clause) ]
BFILENAME('directory', 'filename')BIN_TO_NUM(expr [, expr ]... )
BITAND(expr1, expr2)
BITMAP_BIT_POSITION ( expr )
BITMAP_BUCKET_NUMBER ( expr )
BITMAP_CONSTRUCT_AGG ( expr )
BITMAP_COUNT ( expr )
BITMAP_OR_AGG ( expr )
CARDINALITY(nested_table)
CAST({ expr | MULTISET (subquery) } AS type_name
[ DEFAULT return_value ON CONVERSION ERROR ]
[, fmt [, 'nlsparam' ] ])CEIL(n)
CHARTOROWID(char)
CHR(n [ USING NCHAR_CS ])
CLUSTER_DETAILS ( [ schema . ] model
[ , cluster_id [ , topN ] ] [ DESC | ASC | ABS ]
mining_attribute_clause )CLUSTER_DETAILS ( INTO n
[ , cluster_id [ , topN ] ] [ DESC | ASC | ABS ]
mining_attribute_clause )
OVER ( mining_analytic_clause )CLUSTER_DISTANCE ( [ schema . ] model [ , cluster_id ] mining_attribute_clause )
CLUSTER_DISTANCE ( INTO n [, cluster_id] mining_attribute_clause )
OVER ( mining_analytic_clause )CLUSTER_ID ( [ schema . ] model mining_attribute_clause )
CLUSTER_ID ( INTO n mining_attribute_clause )
OVER ( mining_analytic_clause )CLUSTER_PROBABILITY (aggregate)
CLUSTER_PROBABILITY ( [ schema . ] model [, cluster_id ] mining_attribute_clause )
CLUSTER_PROBABILITY (analytic)
CLUSTER_PROBABILITY ( INTO n [, cluster_id] mining_attribute_clause )
OVER ( mining_analytic_clause )CLUSTER_SET ( [ schema . ] model [ , topN [ , cutoff ] ] mining_attribute_clause )
CLUSTER_SET ( INTO n [, topN [, cutoff]] mining_attribute_clause )
OVER ( mining_analytic_clause )COALESCE(expr [, expr ]...)
COLLATION(expr)
COLLECT( [ DISTINCT | UNIQUE ] column [ ORDER BY expr ] )
COMPOSE(char)
CON_DBID_TO_ID(container_dbid)
CON_GUID_TO_ID(container_guid)
CON_ID_TO_CON_NAME(container_guid)
CON_ID_TO_DBID(container_guid)
CON_NAME_TO_ID(container_name)
CON_UID_TO_ID(container_uid)
CONCAT(char1, char2)
CONVERT(char, dest_char_set[, source_char_set ])
CORR(expr1, expr2) [ OVER (analytic_clause) ]
{ CORR_K | CORR_S }
(expr1, expr2
[, { COEFFICIENT
| ONE_SIDED_SIG
| ONE_SIDED_SIG_POS
| ONE_SIDED_SIG_NEG
| TWO_SIDED_SIG
}
]
)COS(n)
COSH(n)
COUNT({ * | [ DISTINCT | ALL ] expr }) [ OVER (analytic_clause) ]COVAR_POP(expr1, expr2) [ OVER (analytic_clause) ]
COVAR_SAMP(expr1, expr2) [ OVER (analytic_clause) ]
CUBE_TABLE
( ' { schema.cube [ {HIERARCHY | HRR} dimension hierarchy ]...
| schema.dimension [ {HIERARCHY | HRR} [dimension] hierarchy ]
}
'
)CUME_DIST(expr[,expr ]...) WITHIN GROUP
(ORDER BY expr [ DESC | ASC ]
[ NULLS { FIRST | LAST } ]
[, expr [ DESC | ASC ]
[ NULLS { FIRST | LAST } ]
]...
)CUME_DIST() OVER ([ query_partition_clause ] order_by_clause)
CURRENT_DATE
CURRENT_TIMESTAMP [ (precision) ]
CV([ dimension_column ])
DATAOBJ_TO_MAT_PARTITION( table, partition_id )
DATAOBJ_TO_PARTITION( table, partition_id )
DBTIMEZONE
DECODE(expr, search, result [, search, result ]... [, default ])
DECOMPOSE( string [, { 'CANONICAL' | 'COMPATIBILITY' } ] )DENSE_RANK(expr [, expr ]...) WITHIN GROUP
(ORDER BY expr [ DESC | ASC ]
[ NULLS { FIRST | LAST } ]
[,expr [ DESC | ASC ]
[ NULLS { FIRST | LAST } ]
]...
)DENSE_RANK( ) OVER([ query_partition_clause ] order_by_clause)
DEPTH(correlation_integer)
DEREF(expr)
DUMP(expr[, return_fmt [, start_position [, length ] ]])
{ EMPTY_BLOB | EMPTY_CLOB }( )EXISTSNODE(XMLType_instance, XPath_string [, namespace_string ])
EXP(n)
EXTRACT( { YEAR
| MONTH
| DAY
| HOUR
| MINUTE
| SECOND
| TIMEZONE_HOUR
| TIMEZONE_MINUTE
| TIMEZONE_REGION
| TIMEZONE_ABBR
}
FROM { expr }
)EXTRACT(XMLType_instance, XPath_string [, namespace_string ])
EXTRACTVALUE(XMLType_instance, XPath_string [, namespace_string ])
FEATURE_COMPARE ( [ schema . ] model mining_attribute_clause AND mining_attribute_clause )
FEATURE_DETAILS ( [ schema . ] model
[ , feature_id [ , topN ] ] [ DESC | ASC | ABS ]
mining_attribute_clause )FEATURE_DETAILS ( INTO n
[ , feature_id [ , topN ] ] [ DESC | ASC | ABS ]
mining_attribute_clause )
OVER ( mining_analytic_clause )FEATURE_ID( [ schema . ] model mining_attribute_clause )
FEATURE_ID ( INTO n mining_attribute_clause )
OVER ( mining_analytic_clause )FEATURE_SET ( [ schema . ] model [, topN [, cutoff ]] mining_attribute_clause )
FEATURE_SET ( INTO n [, topN [, cutoff ] ] mining_attribute_clause )
OVER ( mining_analytic_clause )FEATURE_VALUE ( [ schema . ] model [, feature_id ] mining_attribute_clause )
FEATURE_VALUE ( INTO n [ , feature_id ] mining_attribute_clause )
OVER ( mining_analytic_clause )aggregate_function
KEEP
(DENSE_RANK FIRST ORDER BY
expr [ DESC | ASC ]
[ NULLS { FIRST | LAST } ]
[, expr [ DESC | ASC ]
[ NULLS { FIRST | LAST } ]
]...
)
[ OVER ( [query_partition_clause] ) ]FIRST_VALUE
{ (expr) [ {RESPECT | IGNORE} NULLS ]
| (expr [ {RESPECT | IGNORE} NULLS ])
}
OVER (analytic_clause)FLOOR(n)
FROM_TZ (timestamp_value, time_zone_value)
GREATEST(expr [, expr ]...)
GROUP_ID( )
GROUPING(expr)
GROUPING_ID(expr [, expr ]...)
HEXTORAW(char)
INITCAP(char)
{ INSTR
| INSTRB
| INSTRC
| INSTR2
| INSTR4
}
(string , substring [, position [, occurrence ] ])ITERATION_NUMBER
JSON_ARRAY ( JSON_ARRAY_content ) | JSON [ JSON_ARRAY_content ]
JSON_ARRAYAGG
( expr [ FORMAT JSON ] [ order_by_clause ]
[ JSON_on_null_clause ] [ JSON_agg_returning_clause ]
[ STRICT ] )JSON_DATAGUIDE (expr [ , format [ , flag ] ] )
JSON_MERGEPATCH
( target_expr , patch_expr [ returning_clause ] [ PRETTY ] [ ASCII ]
[ TRUNCATE ] [ on_error_clause ] )JSON_OBJECT ( JSON_OBJECT_content ) JSON | { JSON_OBJECT_content }JSON_OBJECTAGG
( [ KEY ] key_expr VALUE val_expr [ FORMAT JSON ]
[ JSON_on_null_clause ] [ JSON_agg_returning_clause ]
[ STRICT ] [ WITH UNIQUE KEYS ] )JSON_QUERY
( expr [ FORMAT JSON ], JSON_basic_path_expression
[ JSON_query_returning_clause ] [ JSON_query_wrapper_clause ]
[ JSON_query_on_error_clause ] [ JSON_query_on_empty_clause ]
)JSON_SERIALIZE
( expr [ JSON_returning_clause ] [ PRETTY ] [ASCII ] [ TRUNCATE ]
[ { NULL | ERROR | ( EMPTY {ARRAY | OBJECT} ) } ON ERROR ]
)JSON_TABLE
( expr [ FORMAT JSON ] [ , JSON_basic_path_expression ]
[ JSON_table_on_error_clause ] [ JSON_table_on_empty_clause ]
JSON_columns_clause )JSON_VALUE
( expr [ FORMAT JSON ] , [ JSON_basic_path_expression ]
[ JSON_value_returning_clause ] [ JSON_value_on_error_clause ]
[ JSON_value_on_empty_clause ] [ JSON_value_on_mismatch_clause ]
)JSON_TRANSFORM ( input_expr , operation [ , operation ]... [ JSON_TRANSFORM_returning_clause ] [ JSON_passing_clause ] )
LAG
{ ( value_expr [, offset [, default]]) [ { RESPECT | IGNORE } NULLS ]
| ( value_expr [ { RESPECT | IGNORE } NULLS ] [, offset [, default]] )
}
OVER ([ query_partition_clause ] order_by_clause)aggregate_function KEEP
(DENSE_RANK LAST ORDER BY
expr [ DESC | ASC ]
[ NULLS { FIRST | LAST } ]
[, expr [ DESC | ASC ]
[ NULLS { FIRST | LAST } ]
]...
)
[ OVER ( [query_partition_clause] ) ]LAST_DAY(date)
LAST_VALUE
{ (expr) [ { RESPECT | IGNORE } NULLS ]
| (expr [ { RESPECT | IGNORE } NULLS ])
OVER (analytic_clause)LEAD
{ ( value_expr [, offset [, default]] ) [ { RESPECT | IGNORE } NULLS ]
| ( value_expr [ { RESPECT | IGNORE } NULLS ] [, offset [, default]] )
}
OVER ([ query_partition_clause ] order_by_clause)LEAST(expr [, expr ]...)
{ LENGTH
| LENGTHB
| LENGTHC
| LENGTH2
| LENGTH4
}
(char)LISTAGG( [ ALL | DISTINCT ] measure_expr
[, 'delimiter'] [listagg_overflow_clause] )
[ WITHIN GROUP order_by_clause ]
[OVER query_partition_clause]LN(n)
LNNVL(condition)
LOCALTIMESTAMP [ (timestamp_precision) ]
LOG(n2, n1)
LOWER(char)
LPAD(expr1, n [, expr2 ])
LTRIM(char [, set ])
MAKE_REF({ table | view } , key [, key ]...)MAX([ DISTINCT | ALL ] expr) [ OVER (analytic_clause) ]
MEDIAN(expr) [ OVER (query_partition_clause) ]
MIN([ DISTINCT | ALL ] expr) [ OVER (analytic_clause) ]
MOD(n2, n1)
MONTHS_BETWEEN(date1, date2)
NANVL(n2, n1)
NCHR(number)
NEW_TIME(date, timezone1, timezone2)
NEXT_DAY(date, char)
NLS_CHARSET_DECL_LEN(byte_count, char_set_id)
NLS_CHARSET_ID(string)
NLS_CHARSET_NAME(number)
NLS_COLLATION_ID(expr)
NLS_COLLATION_NAME(expr [, flag ])
NLS_INITCAP(char [, 'nlsparam' ])
NLS_LOWER(char [, 'nlsparam' ])
NLS_UPPER(char [, 'nlsparam' ])
NLSSORT(char [, 'nlsparam' ])
NTH_VALUE(measure_expr, n)
[ FROM { FIRST | LAST } ][ { RESPECT | IGNORE } NULLS ]
OVER (analytic_clause)NTILE(expr) OVER ([ query_partition_clause ] order_by_clause)
NULLIF(expr1, expr2)
NUMTODSINTERVAL(n, 'interval_unit')
NUMTOYMINTERVAL(n, 'interval_unit')
NVL(expr1, expr2)
NVL2(expr1, expr2, expr3)
ORA_DM_PARTITION_NAME ( [ schema . ] model mining_attribute_clause )
ORA_DST_AFFECTED(datetime_expr)
ORA_DST_CONVERT(datetime_expr [, integer [, integer ]])
ORA_DST_ERROR(datetime_expr)
ORA_HASH(expr [, max_bucket [, seed_value ] ])
ORA_INVOKING_USER
ORA_INVOKING_USERID
PATH(correlation_integer)
PERCENT_RANK(expr [, expr ]...) WITHIN GROUP
(ORDER BY
expr [ DESC | ASC ]
[NULLS { FIRST | LAST } ]
[, expr [ DESC | ASC ]
[NULLS { FIRST | LAST } ]
]...
)PERCENT_RANK( ) OVER ([ query_partition_clause ] order_by_clause)
PERCENTILE_CONT(expr) WITHIN GROUP (ORDER BY expr [ DESC | ASC ]) [ OVER (query_partition_clause) ]
PERCENTILE_DISC(expr) WITHIN GROUP (ORDER BY expr [ DESC | ASC ]) [ OVER (query_partition_clause) ]
POWER(n2, n1)
POWERMULTISET(expr)
POWERMULTISET_BY_CARDINALITY(expr, cardinality)
PREDICTION ( [ grouping_hint ] [ schema . ] model [ cost_matrix_clause ] mining_attribute_clause )
PREDICTION ( ( OF ANOMALY | FOR expr ) [ cost_matrix_clause ] mining_attribute_clause )
OVER ( mining_analytic_clause )PREDICTION_BOUNDS ( [schema.] model [, confidence_level [, class_value]]
mining_attribute_clause )PREDICTION_COST ( [ schema . ] model [ , class ] cost_matrix_clause mining_attribute_clause )
PREDICTION_COST ( ( OF ANOMALY | FOR expr ) [ , class ]
cost_matrix_clause mining_attribute_clause )
OVER (mining_analytic_clause)PREDICTION_DETAILS (aggregate)
PREDICTION_DETAILS ( [ schema . ] model
[ , class_value [ , topN ] ] [ DESC | ASC | ABS ]
mining_attribute_clause )PREDICTION_DETAILS ( ( OF ANOMALY | FOR expr ) [ , class_value [ , topN ] ]
[ DESC | ASC | ABS ] mining_attribute_clause )
OVER ( mining_analytic_clause )PREDICTION_PROBABILITY (aggregate)
PREDICTION_PROBABILITY ( [ schema . ] model [ , class ] mining_attribute_clause )
PREDICTION_PROBABILITY (analytic)
PREDICTION_PROBABILITY ( ( OF ANOMALY | FOR expr ) [ , class ]
mining_attribute_clause )
OVER (mining_analytic_clause )PREDICTION_SET ( [ schema . ] model [ , bestN [ , cutoff ] ]
[ cost_matrix_clause ] mining_attribute_clause )PREDICTION_SET ( ( OF ANOMALY | FOR "expr" ) [ , bestN [ , cutoff ] ]
[ cost_matrix_clause ] mining_attribute_clause )
OVER ( mining_analytic_clause )PRESENTNNV(cell_reference, expr1, expr2)
PRESENTV(cell_reference, expr1, expr2)
PREVIOUS(cell_reference)
RANK(expr [, expr ]...) WITHIN GROUP
(ORDER BY
expr [ DESC | ASC ]
[ NULLS { FIRST | LAST } ]
[, expr [ DESC | ASC ]
[ NULLS { FIRST | LAST } ]
]...
)RANK( ) OVER ([ query_partition_clause ] order_by_clause)
RATIO_TO_REPORT(expr) OVER ([ query_partition_clause ])
RAWTOHEX(raw)
RAWTONHEX(raw)
REF (correlation_variable)
REFTOHEX (expr)
REGEXP_COUNT (source_char, pattern [, position [, match_param]])
REGEXP_INSTR ( source_char, pattern
[, position
[, occurrence
[, return_opt
[, match_param
[, subexpr ]
]
]
]
]
)REGEXP_REPLACE ( source_char, pattern
[, replace_string
[, position
[, occurrence
[, match_param ]
]
]
]
)REGEXP_SUBSTR ( source_char, pattern
[, position
[, occurrence
[, match_param
[, subexpr ]
]
]
]
)REGR_AVGX, REGR_AVGY, REGR_COUNT, REGR_INTERCEPT, REGR_R2, REGR_SLOPE, REGR_SXX, REGR_SXY, REGR_SYY
{ REGR_SLOPE
| REGR_INTERCEPT
| REGR_COUNT
| REGR_R2
| REGR_AVGX
| REGR_AVGY
| REGR_SXX
| REGR_SYY
| REGR_SXY
}
(expr1 , expr2)
[ OVER (analytic_clause) ]REMAINDER(n2, n1)
REPLACE(char, search_string
[, replacement_string ]
)ROUND(date [, fmt ])
ROUND(n [, integer ])
ROUND_TIES_TO_EVEN ( n [, integer ] )
ROW_NUMBER( ) OVER ([ query_partition_clause ] order_by_clause)
ROWIDTOCHAR(rowid)
ROWIDTONCHAR(rowid)
RPAD(expr1 , n [, expr2 ])
RTRIM(char [, set ])
SCN_TO_TIMESTAMP(number)
SESSIONTIMEZONE
SET (nested_table)
SIGN(n)
SIN(n)
SINH(n)
SOUNDEX(char)
SQRT(n)
STANDARD_HASH(expr [, 'method' ])
STATS_BINOMIAL_TEST(expr1, expr2, p
[, { TWO_SIDED_PROB
| EXACT_PROB
| ONE_SIDED_PROB_OR_MORE
| ONE_SIDED_PROB_OR_LESS
}
]
)STATS_CROSSTAB(expr1, expr2
[, { CHISQ_OBS
| CHISQ_SIG
| CHISQ_DF
| PHI_COEFFICIENT
| CRAMERS_V
| CONT_COEFFICIENT
| COHENS_K
}
]
)STATS_F_TEST(expr1, expr2
[, { { STATISTIC
| DF_NUM
| DF_DEN
| ONE_SIDED_SIG
} , expr3
| TWO_SIDED_SIG
}
]
)STATS_KS_TEST(expr1, expr2
[, { STATISTIC | SIG } ]
)STATS_MODE(expr)
STATS_MW_TEST(expr1, expr2
[, { STATISTIC
| U_STATISTIC
| ONE_SIDED_SIG , expr3
| TWO_SIDED_SIG
}
]
)STATS_ONE_WAY_ANOVA(expr1, expr2
[, { SUM_SQUARES_BETWEEN
| SUM_SQUARES_WITHIN
| DF_BETWEEN
| DF_WITHIN
| MEAN_SQUARES_BETWEEN
| MEAN_SQUARES_WITHIN
| F_RATIO
| SIG
}
]
)STATS_T_TEST_INDEP, STATS_T_TEST_INDEPU, STATS_T_TEST_ONE, STATS_T_TEST_PAIRED
{
STATS_T_TEST_ONE ( expr1 [, expr2 ]
|
{ { STATS_T_TEST_PAIRED
| STATS_T_TEST_INDEP
| STATS_T_TEST_INDEPU
} ( expr1, expr2
}
}
[, { { STATISTIC | ONE_SIDED_SIG } , expr3 | TWO_SIDED_SIG | DF } ] )STATS_WSR_TEST(expr1, expr2
[, { STATISTIC
| ONE_SIDED_SIG
| TWO_SIDED_SIG
}
]
)STDDEV([ DISTINCT | ALL ] expr) [ OVER (analytic_clause) ]
STDDEV_POP(expr) [ OVER (analytic_clause) ]
STDDEV_SAMP(expr) [ OVER (analytic_clause) ]
{ SUBSTR
| SUBSTRB
| SUBSTRC
| SUBSTR2
| SUBSTR4
}
(char, position [, substring_length ])SUM([ DISTINCT | ALL ] expr) [ OVER (analytic_clause) ]
SYS_CONNECT_BY_PATH(column, char)
SYS_CONTEXT('namespace', 'parameter' [, length ])SYS_DBURIGEN({ column | attribute }
[ rowid ]
[, { column | attribute }
[ rowid ]
]...
[, 'text ( )' ]
)SYS_EXTRACT_UTC(datetime_with_timezone)
SYS_GUID( )
SYS_OP_ZONE_ID( [ [ schema. ] table. | t_alias. ] rowid [, scale ] )
SYS_TYPEID(object_type_value)
SYS_XMLAGG(expr [, fmt ])
SYS_XMLGEN(expr [, fmt ])
SYSDATE
SYSTIMESTAMP
TAN(n)
TANH(n)
TIMESTAMP_TO_SCN(timestamp)
TO_APPROX_COUNT_DISTINCT(detail)
TO_APPROX_PERCENTILE(detail, expr, 'datatype'
[, { 'DESC' | 'ASC' | 'ERROR_RATE' | 'CONFIDENCE' } ])TO_BINARY_DOUBLE(expr [ DEFAULT return_value ON CONVERSION ERROR ] [, fmt [, 'nlsparam' ] ])
TO_BINARY_FLOAT(expr [ DEFAULT return_value ON CONVERSION ERROR ] [, fmt [, 'nlsparam' ] ])
TO_BLOB( bfile [, mime_type] )
TO_BLOB( raw_value )
TO_CHAR( { bfile | blob } [, csid] )TO_CHAR(nchar | clob | nclob)
TO_CHAR({ datetime | interval } [, fmt [, 'nlsparam' ] ])TO_CHAR(n [, fmt [, 'nlsparam' ] ])
TO_CLOB( { bfile | blob } [, csid] [, mime_type] )TO_CLOB(lob_column | char)
TO_DATE(char [ DEFAULT return_value ON CONVERSION ERROR ] [, fmt [, 'nlsparam' ] ])
TO_DSINTERVAL ( ' { sql_format | ds_iso_format } '
[ DEFAULT return_value ON CONVERSION ERROR ] )TO_LOB(long_column)
TO_MULTI_BYTE(char)
TO_NCHAR({char | clob | nclob})TO_NCHAR({ datetime | interval }
[, fmt [, 'nlsparam' ] ]
)TO_NCHAR(n [, fmt [, 'nlsparam' ] ])
TO_NCLOB(lob_column | char)
TO_NUMBER(expr [ DEFAULT return_value ON CONVERSION ERROR ] [, fmt [, 'nlsparam' ] ])
TO_SINGLE_BYTE(char)
TO_TIMESTAMP(char [ DEFAULT return_value ON CONVERSION ERROR ] [, fmt [, 'nlsparam' ] ])
TO_TIMESTAMP_TZ(char [ DEFAULT return_value ON CONVERSION ERROR ] [, fmt [, 'nlsparam' ] ])
TO_UTC_TIMESTAMP_TZ ( varchar ) )
TO_YMINTERVAL
( ' { [+|-] years - months
| ym_iso_format
} '
[ DEFAULT return_value ON CONVERSION ERROR ]
)TRANSLATE(expr, from_string, to_string)
TRANSLATE ( char USING
{ CHAR_CS | NCHAR_CS }
)TREAT(expr AS [ REF ] [ schema. ]type)
TRIM([ { { LEADING | TRAILING | BOTH }
[ trim_character ]
| trim_character
}
FROM
]
trim_source
)TRUNC(date [, fmt ])
TRUNC(n1 [, n2 ])
TZ_OFFSET({ 'time_zone_name'
| '{ + | - } hh : mi'
| SESSIONTIMEZONE
| DBTIMEZONE
}
)UID
UNISTR( string )
UPPER(char)
USER
[ schema. ]
{ [ package. ]function | user_defined_operator }
[ @ dblink. ]
[ ( [ [ DISTINCT | ALL ] expr [, expr ]... ] ) ]USERENV('parameter')VALIDATE_CONVERSION(expr AS type_name [, fmt [, 'nlsparam' ] ])
VALUE(correlation_variable)
VAR_POP(expr) [ OVER (analytic_clause) ]
VAR_SAMP(expr) [ OVER (analytic_clause) ]
VARIANCE([ DISTINCT | ALL ] expr)
[ OVER (analytic_clause) ]VSIZE(expr)
WIDTH_BUCKET (expr, min_value, max_value, num_buckets)
XMLAGG(XMLType_instance [ order_by_clause ])
XMLCAST ( value_expression AS datatype )
XMLCDATA ( value_expr )
XMLCOLATTVAL
(value_expr [ AS { c_alias | EVALNAME value_expr } ]
[, value_expr [ AS { c_alias | EVALNAME value_expr } ]
]...
)XMLCOMMENT ( value_expr )
XMLCONCAT(XMLType_instance [, XMLType_instance ]...)
XMLDIFF ( XMLType_document, XMLType_document [ , integer, string ] )
XMLELEMENT ( [ ENTITYESCAPING | NOENTITYESCAPING ]
{ ( [ NAME ] identifier ) | ( EVALNAME value_expr ) }
[ , XML_attributes_clause ]
[ , value_expr [ [ AS ] c_alias ]]...XMLEXISTS ( XQuery_string [ XML_passing_clause ] )
XMLFOREST
( value_expr [ AS { c_alias | EVALNAME value_expr } ]
[, value_expr [ AS { c_alias | EVALNAME value_expr } ]
]...
)XMLISVALID ( XMLType_instance [, XMLSchema_URL [, element ]] )
XMLPARSE
({ DOCUMENT | CONTENT } value_expr [ WELLFORMED ]
)XMLPATCH ( XMLType_document, XMLType_document )
XMLPI
( { ( [ NAME ] identifier ) | ( EVALNAME value_expr ) }
[ , value_expr ]
)XMLQUERY ( XQuery_string [ XML_passing_clause ] RETURNING CONTENT [NULL ON EMPTY] )
XMLROOT
( value_expr, VERSION
{ value_expr | NO VALUE }
[, STANDALONE { YES | NO | NO VALUE } ]
)XMLSEQUENCE( XMLType_instance
| sys_refcursor_instance [, fmt ]
)XMLSERIALIZE
( { DOCUMENT | CONTENT } value_expr [ AS datatype ]
[ ENCODING xml_encoding_spec ]
[ VERSION string_literal ]
[ NO INDENT | { INDENT [SIZE = number] } ]
[ { HIDE | SHOW } DEFAULTS ]
)XMLTABLE ( [ XMLnamespaces_clause , ] XQuery_string XMLTABLE_options )
XMLTRANSFORM(XMLType_instance, { XMLType_instance
| string
}
)