About SQL Functions

SQL functions are built into Oracle Database and are available for use in various appropriate SQL statements. Do not confuse SQL functions with user-defined functions written in PL/SQL.

If you call a SQL function with an argument of a data type other than the data type expected by the SQL function, then Oracle attempts to convert the argument to the expected data type before performing the SQL function.

Note:

The combined values of the NLS_COMP and NLS_SORT settings determine the rules by which characters are sorted and compared. If NLS_COMP is set to LINGUISTIC for your database, then all entities in this chapter will be interpreted according to the rules specified by the NLS_SORT parameter. If NLS_COMP is not set to LINGUISTIC, then the functions are interpreted without regard to the NLS_SORT setting. NLS_SORT can be explicitly set. If it is not set explicitly, it is derived from NLS_LANGUAGE. Please refer to Oracle Database Globalization Support Guide for more information on these settings.

Nulls in SQL Functions

Most scalar functions return null when given a null argument. You can use the NVL function to return a value when a null occurs. For example, the expression NVL(commission_pct,0) returns 0 if commission_pct is null or the value of commission_pct if it is not null.

For information on how aggregate functions handle nulls, see "Aggregate Functions".

Syntax for SQL Functions In the syntax diagrams for SQL functions, arguments are indicated by their data types. When the parameter function appears in SQL syntax, replace it with one of the functions described in this section. Functions are grouped by the data types of their arguments and their return values.

Note:

When you apply SQL functions to LOB columns, Oracle Database creates temporary LOBs during SQL and PL/SQL processing. You should ensure that temporary tablespace quota is sufficient for storing these temporary LOBs for your application.

See Also:

"About User-Defined Functions" for information on user functions and "Data Conversion" for implicit conversion of data types
Oracle Text Reference for information on functions used with Oracle Text
Oracle Data Mining Application Developer's Guide for information on frequent itemset functions used with Oracle Data Mining

The syntax showing the categories of functions follows:

function::=

Description of the illustration function.gif

single_row_function::=

Description of the illustration single_row_function.gif

The sections that follow list the built-in SQL functions in each of the groups illustrated in the preceding diagrams except user-defined functions. All of the built-in SQL functions are then described in alphabetical order.