Oracle / PLSQL: Functions - Listed by Category

For easy reference, we have provided a list of all Oracle/PLSQL functions. The list of Oracle/PLSQL functions is sorted into the type of function based on categories such as string/character, conversion, advanced, numeric/mathematical, and date/time.

These functions can be used in SQL statements or queries in Oracle. Or, they can be used within the programming environment provided by the Oracle/PLSQL database, such as stored procedures, functions, triggers, etc.

Below is the list of Oracle/PLSQL functions, sorted by category (ie: type of function).

String/Char Functions

ASCIIASCIISTRCHRCOMPOSECONCATConcat with ||CONVERTDECOMPOSEDUMPINITCAPINSTRINSTR2

INSTR4INSTRBINSTRCLENGTHLENGTH2LENGTH4LENGTHBLENGTHCLOWERLPADLTRIMNCHR

REGEXP_INSTRREGEXP_REPLACEREGEXP_SUBSTRREPLACERPADRTRIMSOUNDEXSUBSTRTRANSLATETRIMUPPERVSIZE

Numeric/Math Functions

ABSACOSASINATANATAN2AVGBITANDCEILCOSCOSHCOUNTEXP

FLOORGREATESTLEASTLNLOGMAXMEDIANMINMODPOWERREGEXP_COUNTREMAINDER

ROUND (numbers)ROWNUMSIGNSINSINHSQRTSUMTANTANHTRUNC (numbers)

Date/Time Functions

ADD_MONTHSCURRENT_DATECURRENT_TIMESTAMPDBTIMEZONEEXTRACTLAST_DAY

LOCALTIMESTAMPMONTHS_BETWEENNEW_TIMENEXT_DAYROUND (dates)SESSIONTIMEZONE

SYSDATESYSTIMESTAMPTRUNC (dates)TZ_OFFSET

Conversion Functions

BIN_TO_NUMCASTCHARTOROWIDFROM_TZHEXTORAWNUMTODSINTERVALNUMTOYMINTERVAL

RAWTOHEXTO_CHARTO_CLOBTO_DATETO_DSINTERVALTO_LOBTO_MULTI_BYTE

TO_NCLOBTO_NUMBERTO_SINGLE_BYTETO_TIMESTAMPTO_TIMESTAMP_TZTO_YMINTERVAL

Analytic Functions

CORRCOVAR_POPCOVAR_SAMPCUME_DISTDENSE_RANKFIRST_VALUE

LAGLAST_VALUELEADLISTAGGNTH_VALUERANK

STDDEVVAR_POPVAR_SAMPVARIANCE

Advanced Functions

BFILENAMECARDINALITYCASECOALESCEDECODEEMPTY_BLOB

EMPTY_CLOBGROUP_IDLNNVLNANVLNULLIFNVL

NVL2SYS_CONTEXTUIDUSERUSERENV

Miscellaneous Functions

SQLCODESQLERRMRetrieve user ID from the current Oracle sessionRetrieve the session ID for the user logged in