Library Note
Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com . Request a Workshop for
your organization today.
Note
This page lists a large percentage of the functions, documented and undocumented inside the Oracle Database version 19c.
Missing, by intent, are the 391 OPT... functions which, should I ever begin documenting them, will be done on a separate page. Also not on this page are functions such as CONTAINERS that can be found on the "Undocumented Oracle" page.
List Built-In Functions
This query does not capture all functions as many are stand-alone or built into packages
col usage format a30
SELECT name, minargs, maxargs, datatype, version, analytic, aggregate
FROM v$sqlfn_metadata vsm
WHERE name IN (
SELECT name
FROM v$sqlfn_metadata
MINUS
SELECT DISTINCT procedure_name
FROM dba_procedures)
ORDER BY 1;
Function Name
Analytic
Collection
Conversion
Date & Timestamp
Data Mining
Misc.
Model Clause
Null Handling
Numeric
Object
String
XML
ABS
ACOS
ADD_MONTHS
ADJ_DATE
ANY_VALUE
APPROX_COUNT
APPROX_COUNT_DISTINCT
APPROX_COUNT_DISTINCT_AGG
APPROX_COUNT_DISTINCT_DETAIL
APPROX_MEDIAN
APPROX_PERCENTILE
APPROX_PERCENTILE_AGG
APPROX_PERCENTILE_DETAIL
APPROX_RANK
APPROX_SUM
ASCII
ASCIISTR
ASIN
AS_JSON
ATAN
ATAN2
AVG
AVGERAGE_RANK
BFILENAME
BIN_TO_NUM
BINARY2VARCHAR
BITMAP_BIT_POSITION
BITMAP_BUCKET_NUMBER
BITMAP_CONSTRUCT_AGG
BITMAP_COUNT
BITMAP_OR_AGG
BIT_AND_AGG
BIT_COMPLEMENT
BIT_OR
BIT_OR_AGG
BIT_XOR
BIT_XOR_AGG
BITAND
BOOLTOSTR
BOOL_TO_INT
BSON
CARDINALITY
CASE
CAST
CAST_FROM_BINARY_DOUBLE
CAST_FROM_BINARY_FLOAT
CAST_FROM_BINARY_INTEGER
CAST_FROM_NUMBER
CAST_TO_BINARY_DOUBLE
CAST_TO_BINARY_FLOAT
CAST_TO_BINARY_INTEGER
CAST_TO_NUMBER
CAST_TO_NVARCHAR2
CAST_TO_RAW
CAST_TO_VARCHAR
CEIL
CHARTOROWID
CHECKSUM
CHR
CLASSIFIER
CLUSTER_DETAILS
CLUSTER_DISTANCE
CLUSTER_ID
CLUSTER_PROBABILITY
CLUSTER_SET
COALESCE
COLLATION
COLLECT
COLUMNS
COLUMN_AUTH_INDICATOR
COMPOSE
CONCAT
CONTAINERS
CONVERT
CON_DBID_TO_ID
CON_GUID_TO_ID
CON_ID_TO_CON_NAME
CON_ID_TO_DBID
CON_NAME_TO_ID
CON_UID_TO_ID
CORR
CORR_K
CORR_S
COS
COSH
COUNT
COVAR_POP
COVAR_SAMP
CO_AUTH_IND
CSCONVERT
CUBE_TABLE
CUME_DIST
CUME_DISTM
CURRENTV
CURRENT_DATE
CURRENT_TIMESTAMP
CV
DATAOBJ_TO_MAT_PARTITION
DATAOBJ_TO_PARTITION
DBSTR2UTF8
DBTIMEZONE
DECODE
DECOMPOSE
DENSE_RANK
DENSE_RANKM
DEPTH
DEREF
DUMP
EMPTY_BLOB
EMPTY_CLOB
ENCODE_SQL_XML (UTL_I18N)
ESCAPE_REFERENCE
ESTIMATE_CPU_UNITS
EXISTSNODE
EXP
EXTRACT
EXTRACTVALUE
FEATURE_COMPARE
FEATURE_DETAILS
FEATURE_ID
FEATURE_SET
FEATURE_VALUE
FIRST
FIRSTM
FIRST_VALUE
FLOOR
FROM_TZ
GET_DDL
GET_DEPENDENT_DDL
GET_DEPENDENT_XML
GET_GRANTED_DDL
GET_GRANTED_XDL
GET_HASH
GET_REBUILD_COMMAND
GET_XML
GREATEST
GROUPING
GROUPING_ID
GROUP_ID
HEXTORAW
HEXTOREF
HIER_CAPTION
HIER_CHILD_COUNT
HIER_CHILD_COUNT
HIER_COLUMN
HIER_DEPTH
HIER_DESCRIPTION
HIER_HAS_CHILDREN
HIER_LEVEL
HIER_MEMBER_NAME
HIER_UNIQUE_MEMBER_NAME
HIER_ORDER
INITCAP
INSTR
INSTRB
INSTRC
INSTR2
INSTR4
INT_TO_BOOL
INTERVAL
IS_BIT_SET
IS_NCHAR
ITERATE
ITERATE UNTIL
ITERATION_NUMBER
JSONTOXML
JSON_ARRAY
JSON_ARRAYAGG
JSON_DATAGUIDE
JSON_EQUAL
JSON_EXIST
JSON_HASH
JSON_MERGEPATCH
JSON_OBJECT
JSON_OBJECTAGG
JSON_QUERY
JSON_SERIALIZE
JSON_TABLE
JSON_TEXTCONTAINS
JSON_TEXTCONTAINS2
JSON_VALUE
KURTOSIS_POP
KURTOSIS_SAMP
LAG
LAG_DIFF
LAG_DIFF_PERCENT
LEAD_DIFF
LEAD_DIFF_PERCENT
LAST
LAST_DAY
LAST_VALUE
LATERAL
LEAD
LEAST
LENGTH
LENGTHB
LENGTHC
LENGTH2
LENGTH4
LISTAGG
LN
LNNVL
LOBNVL
LOCALTIMESTAMP
LOG
LONG2CLOB
LONG2VARCHAR
LOWER
LPAD
LTRIM
MAKE_REF
MATCH_NUMBER
MATCH_RECOGNIZE
MAX
MEDIAN
MERGE$ACTIONS
MIN
MOD
MONTHS_BETWEEN
MULTISET
MULTISET EXCEPT
MULTISET INTERSECT
MULTISET UNION
NANVL
NCHR
NEW_TIME
NEXT
NEXT_DAY
NHEXTORAW
NLSSORT
NLS_CHARSET_DECL_LEN
NLS_CHARSET_ID
NLS_CHARSET_NAME
NLS_COLLATION_ID
NLS_COLLATION_NAME
NLS_INITCAP
NLS_LOWER
NLS_UPPER
NTH_VALUE
NTILE
NULLFN
NULLIF
NUMTODSINTERVAL
NUMTOHEX
NUMTOHEX2
NUMTOYMINTERVAL
NVL
NVL2
OBJECT2XML
OBJ_ID
OLAP_CONDITION
OLAP_EXPRESSION
OLAP_EXPRESSION_BOOL
OLAP_EXPRESSION_DATE
OLAP_EXPRESSION_TEXT
OLAP_TABLE
ORA_CHECK_ACL
ORA_CHECK_PRIVILEGE
ORA_CHECK_SYS_PRIVILEGE
ORA_CLUSTERING
ORA_CONCAT_RWKEY
ORA_DM_PARTITION_NAME
ORA_DST_AFFECTED
ORA_DST_CONVERT
ORA_DST_ERROR
ORA_GET_ACLIDS
ORA_HASH
ORA_INVOKING_USER
ORA_INVOKING_USERID
ORA_PARTITION_VALIDATION
ORA_RAWCOMPARE
ORA_RAWCONCAT
ORA_SEARCH_RWKEY
OSON
PART$NUM$INST
PATH
PERCENTILE_CONT
PERCENTILE_DISC
PERCENT_RANK
PERCENT_RANKM
POWER
POWERMULTISET
POWERMULTISET_BY_CARDINALITY
PREDICTION
PREDICTION_BOUNDS
PREDICTION_COST
PREDICTION_DETAILS
PREDICTION_PROBABILITY
PREDICTION_SET
PRESENTNNV
PRESENTV
PREV
PREVIOUS
PRINTBLOBTOCLOB
QUALIFY
QUOTE DELIMITERS
RANDOMBYTES
RANDOMINTEGER
RANDOMNUMBER
RANK
RANKM
RATIO_TO_REPORT
RAWTOHEX
RAWTONHEX
RAWTONUM
RAWTONUM2
RAWTOREF
RAW_TO_CHAR
RAW_TO_NCHAR
RAW_TO_VARCHAR2
REF
REFTOHEX
REFTORAW
REGEXP_COUNT
REGEXP_INSTR
REGEXP_REPLACE
REGEXP_SUBSTR
REGR_AVGX
REGR_AVGY
REGR_COUNT
REGR_INTERCEPT
REGR_R2
REGR_SLOPE
REGR_SXX
REGR_SXY
REGR_SYY
REMAINDER
REMOVE_DOUBLE_QUOTE
REPLACE
REVERSE
ROUND
ROUND_TIES_TO_EVEN
ROWIDTOCHAR
ROWIDTONCHAR
ROW_NUMBER
RPAD
RTRIM
SCN_TO_TIMESTAMP
SDO_GEOM_MAX_X
SDO_GEOM_MAX_Y
SDO_GEOM_MAX_Z
SDO_GEOM_MMB
SDO_GEOM_MBR
SDO_GEOM_MIN_X
SDO_GEOM_MIN_Y
SDO_GEOM_MIN_Z
SDO_TOLERANCE
SEC2DISPLAYTIME
SESSIONTIMEZONE
SET
SHARE_OF
SHRINK_STRING
SIGN
SIN
SINH
SKEWNESS_POP
SKEWNESS_SAMP
SOUNDEX
SQRT
STANDARD_HASH
SQLCODE (exception)
SQLERRM (exception)
STATS_BINOMIAL_TEST
STATS_CROSSTAB
STATS_F_TEST
STATS_KS_TEST
STATS_MODE
STATS_MW_TEST
STATS_ONE_WAY_ANOVA
STATS_T_TEST
STATS_T_TEST_INDEP
STATS_T_TEST_INDEPU
STATS_T_TEST_ONE
STATS_T_TEST_PAIRED
STATS_WSR_TEST
STDDEV
STDDEV_POP
STDDEV_SAMP
STRING_TO_RAW
SUBMULTISET
SUBSTR
SUBSTRB
SUBSTRC
SUBSTR2
SUBSTR4
SUM
SYSDATE
SYSTIMESTAMP
SYS_AT_TIME_ZONE
SYS_AUDIT
SYS_CHECKACL
SYS_CHECK_PRIVILEGE
SYS_CONNECT_BY_PATH (hierarchical)
SYS_CONTEXT (environment)
SYS_CTXINFOPK
SYS_CTX_CONTAINS2
SYS_CTX_MKIVIDX
SYS_DBURIGEN
SYS_DM_RXFORM_CHR
SYS_DM_RXFORM_LAB
SYS_DM_RXFORM_NUM
SYS_DOM_COMPARE
SYS_ET_BFILE_TO_RAW
SYS_ET_BLOB_TO_IMAGE
SYS_ET_IMAGE_TO_BLOB
SYS_ET_RAW_TO_BFILE
SYS_EXTPDTXT
SYS_EXTRACT_UTC
SYS_FILTER_ACLS
SYS_FNMATCHES
SYS_FNREPLACE
SYS_GETTOKENID
SYS_GETXTIVAL
SYS_GET_ACLIDS
SYS_GET_COL_ACLIDS
SYS_GET_PRIVILEGES
SYS_GUID (identifier)
SYS_MAKEXML
SYS_MAKE_XMLNODEID
SYS_MKXMLATTR
SYS_MKXTI
SYS_OPTLOBPRBSC
SYS_OPTXICMP
SYS_OPTXQCASTASNQ
SYS_OP_ADT2BIN
SYS_OP_ADTCONS
SYS_OP_ALSCRVAL
SYS_OP_ATG
SYS_OP_BIN2ADT
SYS_OP_BL2R
SYS_OP_BLOOM_FILTER
SYS_OP_BLOOM_FILTER_LIST
SYS_OP_C2C
SYS_OP_CEG
SYS_OP_CL2C
SYS_OP_COMBINED_HASH
SYS_OP_COMP
SYS_OP_CONVERT
SYS_OP_COUNTCHG
SYS_OP_CSCONV
SYS_OP_CSCONVTEST
SYS_OP_CSR
SYS_OP_CSX_PATCH
SYS_OP_CYCLED_SEQ
SYS_OP_DECOMP
SYS_OP_DESCEND
SYS_OP_DISTINCT
SYS_OP_DRA
SYS_OP_DSB_DESERIALIZE
SYS_OP_DSB_SERIALIZE
SYS_OP_DUMP
SYS_OP_DV_CHECK
SYS_OP_GROUPING
SYS_OP_GUID
SYS_OP_HASH
SYS_OP_IIX
SYS_OP_ITR
SYS_OP_KEY_VECTOR_CREATE
SYS_OP_VECTOR_PAYLOAD
SYS_OP_KEY_VECTOR_SUCCEEDED
SYS_OP_KEY_VECTOR_USE
SYS_OP_LBID
SYS_OP_LOBLOC2BLOB
SYS_OP_LOBLOC2CLOB
SYS_OP_LOBLOC2ID
SYS_OP_LOBLOC2NCLOB
SYS_OP_LOBLOC2TYP
SYS_OP_LSVI
SYS_OP_LVL
SYS_OP_MAKEOID
SYS_OP_MAP_NONNULL
SYS_OP_MSR
SYS_OP_NICOMBINE
SYS_OP_NIEXTRACT
SYS_OP_NII
SYS_OP_NIX
SYS_OP_NUMTORAW
SYS_OP_OIDVALUE
SYS_OP_OPNSIZE
SYS_OP_PAR
SYS_OP_PARGID
SYS_OP_PART_ID
SYS_OP_PAR_1
SYS_OP_PIVOT
SYS_PLSQL_COUNT
SYS_PLSQL_CPU
SYS_PLSQL_IO
SYS_OP_R2O
SYS_OP_RAWTONUM
SYS_OP_RDTM
SYS_OP_REF
SYS_OP_RMTD
SYS_OP_ROWIDTOOBJ
SYS_OP_RPB
SYS_OP_TOSETID
SYS_OP_TPR
SYS_OP_TRTB
SYS_OP_UNDESCEND
SYS_OP_VECAND
SYS_OP_VECBIT
SYS_OP_VECOR
SYS_OP_VECXOR
SYS_OP_VERSION
SYS_OP_VREF
SYS_OP_VVD
SYS_OP_XPTHATG
SYS_OP_XPTHIDX
SYS_OP_XPTHOP
SYS_OP_XTXT2SQLT
SYS_OP_ZONE_ID
SYS_ORDERKEY_DEPTH
SYS_ORDERKEY_MAXCHILD
SYS_ORDERKEY_PARENT
SYS_PATHID_IS_ATTR
SYS_PATHID_IS_NMSPC
SYS_PATHID_LASTNAME
SYS_PATHID_LASTNMSPC
SYS_PATH_REVERSE
SYS_PXQEXTRACT
SYS_RAW_TO_XSID
SYS_REMAP_XMLTYPE
SYS_ROW_DELTA
SYS_SC_2_XMLT
SYS_SYNRCIREDO
SYS_TYPEID
SYS_UMAKEXML
SYS_XMLAGG
SYS_XMLANALYZE
SYS_XMLCONTAINS
SYS_XMLCONV
SYS_XMLEXNSURI
SYS_XMLGEN
SYS_XMLINSTR
SYS_XMLI_LOC_ISNODE
SYS_XMLI_LOC_ISTEXT
SYS_XMLLOCATOR_GETSVAL
SYS_XMLNODEID
SYS_XMLNODEID_GETLOCATOR
SYS_XMLNODEID_GETOKEY
SYS_XMLNODEID_GETPATHID
SYS_XMLNODEID_GETPTRID
SYS_XMLNODEID_GETRID
SYS_XMLNODEID_GETSVAL
SYS_XMLTRANSLATE
SYS_XMLTYPE2SQL
SYS_XMLT_2_SC
SYS_XQBASEURI
SYS_XQCASTABLEERRH
SYS_XQCODEP2STR
SYS_XQCODEPEQ
SYS_XQCON2SEQ
SYS_XQCONCAT
SYS_XQDELETE
SYS_XQDFLTCOLATION
SYS_XQDOC
SYS_XQDOCURI
SYS_XQDURDIV
SYS_XQED4URI
SYS_XQENDSWITH
SYS_XQERR
SYS_XQERRH
SYS_XQESHTMLURI
SYS_XQEXLOBVAL
SYS_XQEXSTWRP
SYS_XQEXTRACT
SYS_XQEXTRREF
SYS_XQEXVAL
SYS_XQFB2STR
SYS_XQFNBOOL
SYS_XQFNCMP
SYS_XQFNDATIM
SYS_XQFNLNAME
SYS_XQFNNM
SYS_XQFNNSURI
SYS_XQFNPREDTRUTH
SYS_XQFNQNM
SYS_XQFNROOT
SYS_XQFORMATNUM
SYS_XQFTCONTAIN
SYS_XQFUNCR
SYS_XQGETCONTENT
SYS_XQINDXOF
SYS_XQINSERT
SYS_XQINSPFX
SYS_XQIRI2URI
SYS_XQLANG
SYS_XQLLNMFRMQNM
SYS_XQMKNODEREF
SYS_XQNILLED
SYS_XQNODENAME
SYS_XQNORMSPACE
SYS_XQNORMUCODE
SYS_XQNSP4PFX
SYS_XQNSPFRMQNM
SYS_XQPFXFRMQNM
SYS_XQPOLYABS
SYS_XQPOLYADD
SYS_XQPOLYCEL
SYS_XQPOLYCST
SYS_XQPOLYCSTBL
SYS_XQPOLYDIV
SYS_XQPOLYFLR
SYS_XQPOLYMOD
SYS_XQPOLYMUL
SYS_XQPOLYRND
SYS_XQPOLYSQRT
SYS_XQPOLYSUB
SYS_XQPOLYUMUS
SYS_XQPOLYUPLS
SYS_XQPOLYVEQ
SYS_XQPOLYVGE
SYS_XQPOLYVGT
SYS_XQPOLYVLE
SYS_XQPOLYVLT
SYS_XQPOLYVNE
SYS_XQREF2VAL
SYS_XQRENAME
SYS_XQREPLACE
SYS_XQRESVURI
SYS_XQRNDHALF2EVN
SYS_XQRSLVQNM
SYS_XQRYENVPGET
SYS_XQRYVARGET
SYS_XQRYWRP
SYS_XQSEQ2CON
SYS_XQSEQ2CON4XC
SYS_XQSEQDEEPEQ
SYS_XQSEQINSB
SYS_XQSEQRM
SYS_XQSEQRVS
SYS_XQSEQSUB
SYS_XQSEQTYPMATCH
SYS_XQSTARTSWITH
SYS_XQSTATBURI
SYS_XQSTR2CODEP
SYS_XQSTRJOIN
SYS_XQSUBSTRAFT
SYS_XQSUBSTRBEF
SYS_XQTOKENIZE
SYS_XQTREATAS
SYS_XQXFORM
SYS_XQ_ASQLCNV
SYS_XQ_ATOMCNVCHK
SYS_XQ_NRNG
SYS_XQ_PKSQL2XML
SYS_XQ_UPKXML2SQL
SYS_XSID_TO_RAW
SYS_ZMAP_FILTER
SYS_ZMAP_REFRESH
SYS$RAWTOANY
TABLE
TAN
TANH
TBL$OR$IDX$PART$NUM
TIMESTAMP_TO_SCN
TO_ACLID
TO_ANYLOB
TO_APPROX_COUNT_DISTINCT
TO_APPROX_PERCENTILE
TO_BINARYDOUBLE
TO_BINARYFLOAT
TO_BLOB
TO_CHAR
TO_CLOB
TO_DATE
TO_DSINTERVAL
TO_LOB
TO_MULTI_BYTE
TO_NCHAR
TO_NCLOB
TO_NUMBER
TO_RAW
TO_SINGLE_BYTE
TO_TIME
TO_TIMESTAMP
TO_TIMESTAMP_TZ
TO_TIME_TZ
TO_UTC_TIMEZONE_TZ
TO_YMINTERVAL
TRANSLATE
TRANSLATE USING
TRANSLITERATE
TREAT
TRIM
TRUNC
TZ_OFFSET
UID (environment)
UNISTR
UPPER
USER (environment)
USERENV (environment)
VALIDATE_CONVERSION
VALUE (object)
VARIANCE
VAR_POP
VAR_SAMP
VECTOR_ENCODE
VERIFY_OWNER
VERIFY_TABLE
VERTICAL BARS
VSIZE
WIDTH_BUCKET
WITH_EXPRESSION
WM_CONTAINS
WM_EQUALS
WM_GREATERTHAN
WM_INTERSECTION
WM_LDIFF
WM_LESSTHAN
WM_MEETS
WM_OVERLAPS
WM_RDIFF
XML2OBJECT
XMLAGG
XMLCAST
XMLCDATA
XMLCOLLATVAL
XMLCOMMENT
XMLCONCAT
XMLDIFF
XMLELEMENT
XMLEXISTS
XMLEXISTS2
XMLFOREST
XMLISNODE
XMLISVALID
XMLPARSE
XMLPATCH
XMLPI
XMLQUERY
XMLQUERYVAL
XMLROOT (deprecated)
XMLSEQUENCE (deprecated)
XMLSERIALIZE
XMLTABLE
XMLTOJSON
XMLTRANSFORM
XMLTRANSFORMBLOB
XMLTYPE
XML2OBJECT
XOR
XS_SYS_CONTEXT