[Chapter 1] 1.4 Built-in Functions

1.4 Built-in Functions

Built-in functions, implemented by Oracle in the STANDARD built-in package, are predefined functions that give you convenient ways to manipulate your data. There are six basic types of built-in functions, each described here in its own section:

Character functions analyze and modify the contents of CHAR and VARCHAR2 string variables.
Numeric functions are a full range of operations that manipulate numbers, including trigonometric, logarithmic, and exponential functions.
Date functions are utilities that allow programmers to perform high-level actions on date variables, including date arithmetic.
Conversion functions convert from one datatype to another, often formatting the output data at the same time.
LOB functions allow operations on LOB (large object) data.
Miscellaneous functions perform operations that don't fall into any of the other categories.

STANDARD contains definitions and functions for the PL/SQL language. These definitions include all of the PL/SQL datatypes, the named exceptions, and the functions and operators (which are defined as functions). Note that almost all STANDARD functions have corresponding SQL native functions.

1.4.1 Character Functions

Character functions parse names, concatenate strings, and perform other character operations.

FUNCTION ASCII
    (ch IN VARCHAR2 CHARACTER SET ANY_CS)
RETURN BINARY_INTEGER;

Returns the numeric ASCII code for ch .

FUNCTION CHR (n BINARY_INTEGER) RETURN VARCHAR2;

Returns the character associated with the numeric collating sequence n , according to the database's character set.

FUNCTION CONCAT 
    (left IN VARCHAR2 CHARACTER SET ANY_CS
    ,right IN VARCHAR2 CHARACTER SET left%CHARSET)
RETURN VARCHAR2;

Returns the string right appended to string left .

FUNCTION INITCAP
    (ch IN VARCHAR2 CHARACTER SET ANY_CS)
RETURN VARCHAR2;

Returns the string ch with the first letter of each word in uppercase and all other letters in lowercase.

FUNCTION INSTR
    (str1 IN VARCHAR2 CHARACTER SET ANY_CS
    ,str2 IN VARCHAR2 CHARACTER SET str1%CHARSET
    ,pos BINARY_INTEGER := 1
    ,nth IN POSITIVE := 1)
RETURN BINARY_INTEGER;

Returns the character position of the nth appearance of str2 in the string str1 . The search begins pos characters into str1 and continues for the length of str1 . A negative pos forces a right to left (backwards) search.

FUNCTION INSTRB
    (str1 IN VARCHAR2 CHARACTER SET ANY_CS
    ,str2 IN VARCHAR2 CHARACTER SET str1%CHARSET
    ,pos BINARY_INTEGER := 1
    ,nth IN POSITIVE := 1)
RETURN BINARY_INTEGER;

Same as INSTR except pos and len are expressed in bytes (for multi-byte character sets).

FUNCTION LENGTH
    (ch IN VARCHAR2 CHARACTER SET ANY_CS)
RETURN NATURAL;

Returns the length of string ch .

FUNCTION LENGTHB
    (ch IN VARCHAR2 CHARACTER SET ANY_CS)
RETURN NUMBER;

Returns the length in bytes of ch .

FUNCTION LOWER
    (ch IN VARCHAR2 CHARACTER SET ANY_CS)
RETURN VARCHAR2;

Returns ch with all characters in lowercase.

FUNCTION LPAD
    (str1 IN VARCHAR2 CHARACTER SET ANY_CS
    ,len BINARY_INTEGER
   [,pad IN VARCHAR2 CHARACTER SET str1%CHARSET])
RETURN VARCHAR2;

Returns str1 padded on the left to a length of len with pad character pad .

FUNCTION LTRIM
    (str1 IN VARCHAR2 CHARACTER SET ANY_CS := ` `
    [,tset IN VARCHAR2 CHARACTER SET str1%CHARSET])
RETURN VARCHAR2;

Returns str1 stripped of any leading characters that appear in tset .

FUNCTION REPLACE
    (srcstr IN VARCHAR2 CHARACTER SET ANY_CS
    ,oldsub IN VARCHAR2 CHARACTER SET srcstr%CHARSET
    ,newsub IN VARCHAR2 CHARACTER SET
        srcstr%CHARSET:= NULL)
RETURN VARCHAR2;

Returns scrstr with all occurrences of oldsub replaced with newsub .

FUNCTION RPAD
    (str1 IN VARCHAR2 CHARACTER SET ANY_CS
    ,len BINARY_INTEGER
   [,pad IN VARCHAR2 CHARACTER SET str1%CHARSET])
RETURN VARCHAR2;

Returns str1 padded on the right to a length of len using pad character pad .

FUNCTION RTRIM
    (str1 IN VARCHAR2 CHARACTER SET ANY_CS := ` `
   [,tset IN VARCHAR2 CHARACTER SET str1%CHARSET])
RETURN VARCHAR2;

Returns str1 stripped of any trailing characters that appear in tset .

FUNCTION SOUNDEX
    (ch IN VARCHAR2 CHARACTER SET ANY_CS)
RETURN VARCHAR2;

Returns the soundex (phonetic) encoding of ch .

FUNCTION SUBSTR
    (str1 IN VARCHAR2 CHARACTER SET ANY_CS
    ,pos BINARY_INTEGER
    ,len BINARY_INTEGER := NULL)
RETURN VARCHAR2;

Returns the portion of str1 beginning pos characters into str1 and extending for len characters. Negatives in pos or len cause the positions to be counted from right to left (backwards).

FUNCTION SUBSTRB
    (str1 IN VARCHAR2 CHARACTER SET ANY_CS
    ,pos BINARY_INTEGER
    ,len BINARY_INTEGER := NULL)
RETURN VARCHAR2;

Same as SUBSTR except pos and len are expressed in bytes (for multi-byte character sets).

FUNCTION TRANSLATE
    (str1 IN VARCHAR2 CHARACTER SET ANY_CS
    ,src IN VARCHAR2 CHARACTER SET str1%CHARSET
    ,dest IN VARCHAR2 CHARACTER SET str1%CHARSET)
RETURN VARCHAR2;

Returns str1 with all occurrences of characters in src replaced by positionally corresponding characters in dest .

FUNCTION UPPER
    (ch IN VARCHAR2 CHARACTER SET ANY_CS)
RETURN VARCHAR2;

Returns the string ch in all uppercase.

1.4.2 Numeric Functions

Numeric functions manipulate numbers.

FUNCTION ABS (n IN NUMBER) RETURN NUMBER;

Returns the absolute value of n .

FUNCTION ACOS (n IN NUMBER) RETURN NUMBER;

Returns the arc cosine of n where -1 < n < 1.

FUNCTION ASIN (n IN NUMBER) RETURN NUMBER;

Returns the arc sine of n where -1 < n < 1.

FUNCTION ATAN (n IN NUMBER) RETURN NUMBER;

Returns the inverse tangent of n .

FUNCTION ATAN2
    (x IN NUMBER, y IN NUMBER)
RETURN NUMBER;

Returns the arc tangent of x and y .

FUNCTION CEIL (n IN NUMBER) RETURN NUMBER;

Returns the nearest integer greater than or equal to n .

FUNCTION COS (n IN NUMBER) RETURN NUMBER;

Returns the trigonometric cosine of n .

FUNCTION COSH (n IN NUMBER) RETURN NUMBER;

Returns the hyperbolic cosine of n .

FUNCTION EXP (n IN NUMBER) RETURN NUMBER;

Returns the value of e raised to the n th power where e is the base of the natural logarithms.

FUNCTION FLOOR (n IN NUMBER) RETURN NUMBER;

Returns the nearest integer less than or equal to n .

FUNCTION LN (n IN NUMBER) RETURN NUMBER;

Returns the natural logarithm of n .

FUNCTION LOG
    (left IN NUMBER, right IN NUMBER)
RETURN NUMBER;

Returns the logarithm of right in base left where left >1 and right > 0.

FUNCTION MOD
    (n1 IN NUMBER, n2 IN NUMBER)
RETURN NUMBER;

Returns the remainder of n1 after division by n2 .

FUNCTION POWER
    (n IN NUMBER, e IN NUMBER)
RETURN NUMBER;

Returns n raised to the e th power.

FUNCTION ROUND 
   (left IN NUMBER
   ,right BINARY_INTEGER := 0)
RETURN NUMBER;

Returns left rounded to right decimal places.

FUNCTION SIGN (n IN NUMBER) RETURN SIGNTYPE;

Returns -1, 0, or 1, depending on the sign of n .

FUNCTION SIN (n IN NUMBER) RETURN NUMBER;

Returns the trigonometric sine function of n .

FUNCTION SINH (n IN NUMBER) RETURN NUMBER;

Returns the hyperbolic sine of n .

FUNCTION SQRT (n IN NUMBER) RETURN NUMBER;

Returns the square root of n .

FUNCTION TAN (n IN NUMBER) RETURN NUMBER;

Returns the trigonometric tangent function of n .

FUNCTION TANH (n IN NUMBER) RETURN NUMBER;

Returns the hyperbolic tangent of n .

FUNCTION TRUNC 
   (n IN NUMBER
   ,places BINARY_INTEGER := 0)
RETURN NUMBER;

Truncates n to places decimal places.

1.4.3 Date Functions

Date functions manipulate date information.

FUNCTION ADD_MONTHS 
   (left IN DATE | NUMBER
   ,right IN NUMBER | DATE)
RETURN DATE;

Returns the date resulting from adding right months to the left date.

FUNCTION LAST_DAY (right IN DATE) RETURN DATE;

Returns the last day of the month containing date right .

FUNCTION MONTHS_BETWEEN
    (left IN DATE, right IN DATE)
RETURN NUMBER;

Returns the number of months between left and right .

FUNCTION NEW_TIME 
   (right IN DATE
   ,middle IN VARCHAR2
   ,left IN VARCHAR2)
RETURN DATE;

Returns the date when date right is converted from time zone middle to time zone left .

FUNCTION NEXT_DAY
    (left IN DATE, right IN VARCHAR2)
RETURN DATE;

Returns the next occurrence of day of the week right ("Monday", "Tuesday", etc.) after date left .

FUNCTION ROUND
    (left IN DATE [,right IN VARCHAR2])
RETURN DATE;

Returns date left rounded according to format mask right (or the default format DD).

FUNCTION SYSDATE RETURN DATE;

Returns the current system date from the database.

FUNCTION TRUNC
    (left IN DATE [,right IN VARCHAR2])
RETURN DATE;

Returns the date left truncated using format mask right (or the default format DD).

1.4.4 Conversion Functions

Conversion functions convert data to the right datatype for an operation.

FUNCTION CHARTOROWID (str IN VARCHAR2) RETURN ROWID;

Returns str converted from VARCHAR2 to the ROWID datatype. See also DBMS_ROWID.

FUNCTION CONVERT 
    (src IN VARCHAR2
    ,destcset IN VARCHAR2
   [,srccset IN VARCHAR2])
RETURN VARCHAR2;

Returns src converted from character set srcset to destcset . The default for srccset is the database's default character set.

FUNCTION HEXTORAW (c IN VARCHAR2) RETURN RAW;

Returns the hexadecimal encoded VARCHAR c as a RAW string.

FUNCTION RAWTOHEX (r IN RAW) RETURN VARCHAR2;

Returns the RAW string r as a hexadecimal encoded VARCHAR.

FUNCTION ROWIDTOCHAR (str IN ROWID) RETURN VARCHAR2;

Returns the ROWID data in str converted to a VARCHAR.

FUNCTION TO_CHAR
    (left IN DATE | NUMBER
    [,right IN VARCHAR2])
    [,parms IN VARCHAR2])
RETURN VARCHAR2;

Returns left converted from a NUMBER or DATE to a VARCHAR, using format mask right if specified. The optional parms can specify NLS language parameters.

FUNCTION TO_DATE
    (left IN VARCHAR2 | NUMBER
    [,right IN VARCHAR2]
    [,parms IN VARCHAR2])
RETURN DATE;

Returns left converted from a NUMBER or VARCHAR to a DATE, using format mask right if specified. The optional parms can specify NLS language parameters.

FUNCTION TO_NUMBER
    (left IN VARCHAR2 | NUMBER
    [,right IN VARCHAR2]
    [,parms IN VARCHAR2])
RETURN NUMBER;

Returns left converted from a VARCHAR or NUMBER to a DATE, using format mask right if specified. The optional parms can specify NLS language parameters.

1.4.5 LOB Functions

LOB functions initialize large object (LOB) values.

FUNCTION BFILENAME
    (directory IN VARCHAR2
    ,filename IN VARCHAR2)
RETURN BFILE;

Returns a BFILE locator (handle) to filename in directory directory . See also DBMS_LOB.

FUNCTION EMPTY_BLOB RETURN BLOB;

Returns an empty locator of type BLOB.

FUNCTION EMPTY_CLOB RETURN CLOB;

Returns an empty locator of type CLOB.

1.4.6 Miscellaneous Functions

Miscellaneous functions return a variety of useful information.

FUNCTION BITAND
    (left BINARY_INTEGER
    ,right BINARY_INTEGER)
RETURN BINARY_INTEGER;

Returns the bitwise AND of left and right .

FUNCTION DUMP
    (e IN NUMBER | DATE | VARCHAR2
    ,df BINARY_INTEGER := NULL
    ,sp BINARY_INTEGER := NULL
    ,len BINARY_INTEGER := NULL)
RETURN VARCHAR2;

Returns the internal representation of the portion of e starting at sp of length len and using dump format df (8 = octal; 10 = decimal; 16 = hex; 17 = char).

FUNCTION GREATEST
    (pattern IN NUMBER | VARCHAR | DATE)
RETURN NUMBER | VARCHAR | DATE;

Returns the greatest value of the (two or more) expressions listed in pattern . pattern expressions must all have the same datatype.

FUNCTION LEAST
    (pattern IN NUMBER | VARCHAR | DATE)
RETURN NUMBER | VARCHAR | DATE;

Returns the smallest value of the (two or more) expressions listed in pattern . pattern is a comma-delimited list of expressions that all have the same datatype.

FUNCTION NVL
    (s1 | n1 | d1 | b1  IN VARCHAR2 | NUMBER | DATE |
       BOOLEAN
    ,s2 | n2 | d2 | b2 IN VARCHAR2 CHARACTER SET
       s1%CHARSET)
RETURN VARCHAR2 | NUMBER | DATE | BOOLEAN;

NULL value function; returns s2 | n2 | d2 | b2 when s1 | n1 | d1 | b1 is NULL, otherwise s1 | n1 | d1 | b1 .

FUNCTION SQLCODE RETURN NUMBER;

Returns the numeric code associated with the current execution status.

FUNCTION SQLERRM [(code IN NUMBER)] RETURN VARCHAR2;

Returns the error message associated with code (or the current SQLCODE by default).

FUNCTION UID RETURN NUMBER;

Returns the numeric user id of the current user.

FUNCTION USER RETURN VARCHAR2;

Returns the character username of the current user.

FUNCTION USERENV (envstr IN VARCHAR2) RETURN VARCHAR2;

Returns the user session environment information specified by envstr , which can have the following values:

ENTRYID: To return an auditing identifier.
INSTANCE: To return the instance identifier for an OPS database.
LANGUAGE: To return NLS settings (language, territory, and character set) for the session.
TERMINAL: To return the operating system terminal identifier.

FUNCTION VSIZE
    (e IN NUMBER | DATE | VARCHAR2)
RETURN NUMBER;

Returns the number of bytes used to store e internally.

FUNCTION XOR
    (left IN BOOLEAN, right IN BOOLEAN)
RETURN BOOLEAN;

Returns TRUE when either left or right is TRUE but not both. See also UTL_RAW.