SQL Functions v/s User-defined Functions

SQL functions are built-in Database functions and are available for use in various SQL statements.

For a SQL function call with an argument of a datatype other than the datatype expected by the SQL function, Oracle attempts to convert the argument to the expected datatype before performing the SQL function.

If you call a SQL function with a null argument, then the SQL function automatically returns null. The only SQL functions that do not necessarily follow this behavior are CONCAT, NVL, REPLACE, and REGEXP_REPLACE.


Use-defined functions are the functions that are written in PL/SQL or Java to provide functionality that is not available in SQL or SQL built-in functions. User-defined functions can appear in a SQL statement anywhere SQL functions can appear, that is, wherever an expression can occur.

To use a user function in a SQL expression, you must own or have EXECUTE privilege on the user function. To query a view defined with a user function, you must have SELECT privileges on the view. No separate EXECUTE privileges are needed to select from the view.

User-defined functions must be created as top-level functions or declared with a package specification before they can be named within a SQL statement.


And hence one should not confuse with SQL functions and User-defined functions.