Built-In Functions in Oracle PL/SQL

PL/SQL provides many powerful functions to help you manipulate data. These built-in functions fall into the following categories:

 

error reporting

 

number

 

character

 

datatype conversion

 

date

 

object reference

 

miscellaneous

 

The below Table shows the functions in each category.
Except for the error-reporting functions SQLCODE and SQLERRM, you can use all the functions in SQL statements. Also, except for the object-reference functions DEREF, REF, and VALUE and the miscellaneous functions DECODE, DUMP, and VSIZE, you can use all the functions in procedural statements.

 

Although the SQL aggregate functions (such as AVG and COUNT) and the SQL analytic functions (such as CORR and LAG) are not built into PL/SQL, you can use them in SQL statements (but not in procedural statements).
Error Reporting Functions

• SQLCODE
• SQLERRM

  Number Funcions

 

 

        

• ABS
• ACOS
• ASIN
• ATAN
• ATAN2
• BITAND
• CEIL
• COS
• COSH
• EXP
• FLOOR
• LN
• LOG
• MOD
• POWER
• ROUND
• SIGN
• SIN
• SINH
• SQRT
• TAN
• TANH
• TRUNC

 

 Character Functions

 

 

• ASCII
• CHR
• CONCAT
• INITCAP
• INSTR
• INSTRB
• LENGTH
• LENGTHB
• LOWER
• LPAD
• LTRIM
• NLS_INITCAP
• NLS_LOWER
• NLSSORT
• NLS_UPPER
• REPLACE
• RPAD
• RTRIM
• SOUNDEX
• SUBSTR
• SUBSTRB
• TRANSLATE
• TRIM
• UPPER

 

 Conversion Functions

 

• CHARTOROWID
• CONVERT
• HEXTORAW
• RAWTOHEX
• ROWIDTOCHAR
• TO_BLOB
• TO_CHAR
• TO_CLOB
• TO_DATE
• TO_MULTI_BYTE
• TO_NCLOB
• TO_NUMBER
• TO_SINGLE_BYTE

    Date Functions

 

 

• ADD_MONTHS
• CURRENT_DATE
• CURRENT_TIMESTAMP
• DBTIMEZONE
• EXTRACT
• FROM_TZ
• LAST_DAY
• LOCALTIMESTAMP
• MONTHS_BETWEEN
• NEW_TIME
• NEXT_DAY
• NUMTODSINTERVAL
• NUMTOYMINTERVAL
• ROUND
• SESSIONTIMEZONE
• SYSDATE
• SYSTIMESTAMP
• TO_DSINTERVAL
• TO_TIMESTAMP
• TO_TIMESTAMP_LTZ
• TO_TIMESTAMP_TZ
• TO_YMINTERVAL
• TZ_OFFSET
• TRUNC

Obj Reference Funcions

 

• DEREF
• REF
• VALUE
• TREAT

 

Miscellanious Functions

 

• BFILENAME
• DECODE
• DUMP
• EMPTY_BLOB
• EMPTY_CLOB
• GREATEST
• LEAST
• NLS_CHARSET_DECL_LEN
• NLS_CHARSET_ID
• NLS_CHARSET_NAME
• NVL
• SYS_CONTEXT
• SYS_GUID
• UID
• USER
• USERENV
• VSIZE