|Oracle8i SQL Reference
Release 2 (8.1.6)
Functions, 121 of 121
You can write user-defined functions in PL/SQL or Java to provide functionality that is not available in SQL or SQL functions. User functions can appear in a SQL statement anywhere SQL functions can appear, that is, wherever an expression can occur.
For example, user functions can be used in the following:
VALUESclause of an
SETclause of an
User functions must be created as top-level functions or declared with a package specification before they can be named within a SQL statement. Create user functions as top-level functions by using the
FUNCTION statement described in "CREATE FUNCTION". To specify packaged functions, see "CREATE PACKAGE".
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.
Within a SQL statement, the names of database columns take precedence over the names of functions with no parameters. For example, if user
SCOTT creates the following two objects in his own schema:
then in the following two statements, the reference to
NEW_SAL refers to the column
To access the function
NEW_SAL, you would enter:
Here are some sample calls to user functions that are allowed in SQL expressions:
To call the
TAX_RATE user function from schema
SCOTT, execute it against the
SAL columns in
TAX_TABLE, and place the results in the variable
INCOME_TAX, specify the following:
If only one of the optional schema or package names is given, the first identifier can be either a schema name or a package name. For example, to determine whether
PAYROLL in the reference
PAYROLL.TAX_RATE is a schema or package name, Oracle proceeds as follows:
PAYROLLpackage in the current schema.
PAYROLLpackage is not found, look for a schema name
PAYROLLthat contains a top-level
TAX_RATEfunction. If no such function is found, return an error.
PAYROLLpackage is found in the current schema, look for a
TAX_RATEfunction in the
PAYROLLpackage. If no such function is found, return an error.
You can also refer to a stored top-level function using any synonym that you have defined for it.