SQL Tutorial SQL Advanced SQL Database SQL References

Oracle Functions



Oracle (PL/SQL) has many built-in functions. For easy reference, a list of all Oracle (PL/SQL) functions are provided. These functions can be used in queries in Oracle (PL/SQL) and can also be used within the programming environment provided by the Oracle (PL/SQL) database, such as stored procedures, functions, triggers, etc.

Below is the list of Oracle (PL/SQL) functions, sorted by category:

Oracle String/Char Functions

FunctionDescription
ASCII()Returns the ASCII value for the specific character
ASCIISTR()
CHR()
COMPOSE()
CONCAT()
Concat with ||()
CONVERT()
DECOMPOSE()
DUMP()
INITCAP()
INSTR()
INSTR2()
INSTR4()
INSTRB()
INSTRC()
LENGTH()
LENGTH2()
LENGTH4()
LENGTHB()
LENGTHC()
LOWER()
LPAD()
LTRIM()
NCHR()
REGEXP_INSTR()
REGEXP_REPLACE()
REGEXP_SUBSTR()
REPLACE()
RPAD()
RTRIM()
SOUNDEX()
SUBSTR()
TRANSLATE()
TRIM()
UPPER()
VSIZE()

Oracle Math/Numeric Functions

FunctionDescription
ABS()
ACOS()
ASIN()
ATAN()
ATAN2()
AVG()
BITAND()
CEIL()
COS()
COSH()
COUNT()
EXP()
FLOOR()
GREATEST()
LEAST()
LN()
LOG()
MAX()
MEDIAN()
MIN()
MOD()
POWER()
REGEXP_COUNT()
REMAINDER()
ROUND (numbers)()
ROWNUM()
SIGN()
SIN()
SINH()
SQRT()
SUM()
TAN()
TANH()
TRUNC (numbers)()

Oracle Date/Time Functions

FunctionDescription
ADD_MONTHS()
CURRENT_DATE()
CURRENT_TIMESTAMP()
DBTIMEZONE()
EXTRACT()
LAST_DAY()
LOCALTIMESTAMP()
MONTHS_BETWEEN()
NEW_TIME()
NEXT_DAY()
ROUND (dates)()
SESSIONTIMEZONE()
SYSDATE()
SYSTIMESTAMP()
TRUNC (dates)()
TZ_OFFSET()

Oracle Conversion Functions

FunctionDescription
BIN_TO_NUM()
CAST()
CHARTOROWID()
FROM_TZ()
HEXTORAW()
NUMTODSINTERVAL()
NUMTOYMINTERVAL()
RAWTOHEX()
TO_CHAR()
TO_CLOB()
TO_DATE()
TO_DSINTERVAL()
TO_LOB()
TO_MULTI_BYTE()
TO_NCLOB()
TO_NUMBER()
TO_SINGLE_BYTE()
TO_TIMESTAMP()
TO_TIMESTAMP_TZ()
TO_YMINTERVAL()

Oracle Analytic Functions

FunctionDescription
CORR()
COVAR_POP()
COVAR_SAMP()
CUME_DIST()
DENSE_RANK()
FIRST_VALUE()
LAG()
LAST_VALUE()
LEAD()
LISTAGG()
NTH_VALUE()
RANK()
STDDEV()
VAR_POP()
VAR_SAMP()
VARIANCE()

Oracle Advanced Functions

FunctionDescription
BFILENAME()
CARDINALITY()
CASE()
COALESCE()
DECODE()
EMPTY_BLOB()
EMPTY_CLOB()
GROUP_ID()
LNNVL()
NANVL()
NULLIF()
NVL()
NVL2()
SYS_CONTEXT()
UID()
USER()
USERENV()

Oracle Miscellaneous Functions

FunctionDescription
SQLCODE()
SQLERRM()
Retrieve user ID from the current Oracle session()
Retrieve the session ID for the user logged in()

5