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
| Function | Description |
|---|
| ASCII() | Returns the ASCII value for the specific character |
| ASCIISTR() | |
| CHR() | |
| COMPOSE() | |
| CONCAT() | Adds two strings together |
| Concat with || | Adds two or more strings together |
| CONVERT() | |
| DECOMPOSE() | |
| DUMP() | |
| INITCAP() | Convert the first letter of each word to upper case and the rest to lower case |
| INSTR() | Returns the position of a string in another string |
| INSTR2() | Returns the position of a string in another string, using UCS2 code points |
| INSTR4() | Returns the position of a string in another string, using UCS4 code points |
| INSTRB() | Returns the position of a string in another string, using bytes instead of characters |
| INSTRC() | Returns the position of a string in another string, using Unicode complete characters |
| LENGTH() | Returns the length of the specified string |
| LENGTH2() | Returns the length of the specified string, using UCS2 code points |
| LENGTH4() | Returns the length of the specified string, using UCS4 code points |
| LENGTHB() | Returns the length of the specified string, using bytes instead of characters |
| LENGTHC() | Returns the length of the specified string, using Unicode complete characters |
| LOWER() | Converts a string to lowercase |
| LPAD() | Left-pads a string with another string, to a certain length |
| LTRIM() | Removes leading spaces (or specified characters) from a string |
| NCHR() | |
| REGEXP_INSTR() | |
| REGEXP_REPLACE() | |
| REGEXP_SUBSTR() | |
| REPLACE() | Replaces all occurrences of a substring within a string, with a new substring |
| RPAD() | Right-pads a string with another string, to a certain length |
| RTRIM() | Removes trailing spaces (or specified characters) from a string |
| SOUNDEX() | Returns a soundex string |
| SUBSTR() | Extracts a substring from a string starting at specified position and calculates length using characters |
| SUBSTR2() | Extracts a substring from a string starting at specified position and calculates length using UCS2 code points |
| SUBSTR4() | Extracts a substring from a string starting at specified position and calculates length using UCS4 code points |
| SUBSTRB() | Extracts a substring from a string starting at specified position and calculates length using bytes instead of characters |
| SUBSTRC() | Extracts a substring from a string starting at specified position and calculates length using Unicode complete characters |
| TRANSLATE() | Replaces a sequence of characters in a string with another set of characters |
| TRIM() | Removes leading and trailing spaces (or a specified character) from a string |
| UPPER() | Converts a string to uppercase |
| VSIZE() | |
Oracle Math/Numeric Functions
| Function | Description |
|---|
| ABS() | Returns the absolute value of a number |
| ACOS() | Returns the arc cosine of a number |
| ASIN() | Returns the arc sine of a number |
| ATAN() | Returns the arc tangent of a number |
| ATAN2() | Returns the arc tangent of two numbers |
| AVG() | Returns the average value of an expression |
| BITAND() | |
| CEIL() | Returns the smallest integer value that is >= a number |
| COS() | Returns the trigonometric cosine of an angle |
| COSH() | |
| COUNT() | Returns the number of records returned by a select query |
| EXP() | Returns e raised to the power of a specified number |
| FLOOR() | Returns the largest integer value that is <= to a number |
| GREATEST() | Returns the greatest value of the list of arguments |
| LEAST() | Returns the smallest value of the list of arguments |
| LN() | Returns the natural logarithm of a number |
| LOG() | Returns the natural logarithm of a number, or the logarithm of a number to a specified base |
| MAX() | Returns the maximum value in a set of values |
| MEDIAN() | |
| MIN() | Returns the minimum value in a set of values |
| MOD() | Returns the remainder of a number divided by another number |
| POWER() | Returns the value of a number raised to the power of another number |
| REGEXP_COUNT() | |
| REMAINDER() | Returns the remainder of a number divided by another number |
| ROUND (numbers)() | |
| ROWNUM() | |
| SIGN() | Returns the sign of a number |
| SIN() | Returns the trigonometric sine of an angle |
| SINH() | |
| SQRT() | Returns the square root of a number |
| SUM() | Calculates the sum of a set of values |
| TAN() | Returns the trigonometric tangent of an angle |
| TANH() | |
| TRUNC (numbers)() | |
Oracle Date/Time Functions
| Function | Description |
|---|
| ADD_MONTHS() | |
| CURRENT_DATE | Returns the current date in the time zone of the current SQL session |
| CURRENT_TIMESTAMP | Returns the current date and time in the time zone of the current SQL session |
| DBTIMEZONE() | |
| EXTRACT() | |
| LAST_DAY() | |
| LOCALTIMESTAMP | Returns the current date and time in the time zone of the current SQL session |
| MONTHS_BETWEEN() | |
| NEW_TIME() | |
| NEXT_DAY() | |
| ROUND (dates)() | |
| SESSIONTIMEZONE() | |
| SYSDATE | Returns the current date and time set for the operating system on which the database resides |
| SYSTIMESTAMP | Returns the system date, including fractional seconds and time zone, of the system on which the database resides |
| TRUNC (dates)() | |
| TZ_OFFSET() | |
Oracle Conversion Functions
| Function | Description |
|---|
| BIN_TO_NUM() | |
| CAST() | Converts a value (of any type) into a specified datatype |
| 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
| Function | Description |
|---|
| CORR() | |
| COVAR_POP() | |
| COVAR_SAMP() | |
| CUME_DIST() | |
| DENSE_RANK() | |
| FIRST_VALUE() | |
| LAG() | |
| LAST_VALUE() | |
| LEAD() | |
| LISTAGG() | |
| NTH_VALUE() | |
| RANK() | |
| STDDEV() | Returns the statistical standard deviation of all values in the specified expression |
| STDDEV_POP() | Returns the statistical standard deviation for the population for all values in the specified expression |
| STDDEV_SAMP() | Returns the statistical standard deviation of all values in the specified expression |
| VAR_POP() | |
| VAR_SAMP() | |
| VARIANCE() | |
Oracle Advanced Functions
| Function | Description |
|---|
| BFILENAME() | |
| CARDINALITY() | |
| CASE() | Goes through conditions and return a value when the first condition is met |
| COALESCE() | Returns the first non-null value in a list |
| DECODE() | |
| EMPTY_BLOB() | |
| EMPTY_CLOB() | |
| GROUP_ID() | |
| LNNVL() | |
| NANVL() | |
| NULLIF() | |
| NVL() | |
| NVL2() | |
| SYS_CONTEXT() | |
| UID() | |
| USER() | Returns the current Oracle database user name and host name |
| USERENV() | |
Oracle Miscellaneous Functions
| Function | Description |
|---|
| SQLCODE() | |
| SQLERRM() | |
| Retrieve user ID from the current Oracle session() | |
| Retrieve the session ID for the user logged in() | |
