Requirements for calling functions in SQL

There are several requirements that a programmer-defined PL/SQL function must meet in order to be callable from within a SQL statement:

· The function must be stored in the database. A function defined in a client-side PL/SQL environment cannot be called from within SQL; there would be no way for SQL to resolve the reference to the function.

· All of the function's parameters must use the IN mode. Neither IN OUT nor OUT parameters are allowed in SQL-embedded stored functions—you should never have IN OUT and OUT parameters in functions, period. Whether or not you are going to use that function inside a SQL statement, such parameters constitute side effects of the main purpose of the function, which is to return a single value.

· The datatypes of the function's parameters, as well as the datatype of the RETURN clause of the function, must be recognized within the Oracle Server. While all of the Oracle Server datatypes are valid within PL/SQL, PL/SQL has added new datatypes that are not (yet) supported in the database. These datatypes include BOOLEAN, BINARY_INTEGER, associative arrays, PL/SQL records, and programmer-defined subtypes.

· Prior to Oracle8i, functions defined in packages must have an associated RESTRICT_REFERENCES pragma defined for them. If you want to call from SQL a function defined in a package, you will need to add a pragma to the package specification asserting explicitly that this function is valid for SQL execution. See the later section Section 16.8.1.4 for more details on this step.

By default, user-defined functions that execute in SQL operate on a single row of data, not on an entire column of data that crosses rows, as the group functions SUM, MIN, and AVG do. It is possible to write aggregate functions to be called inside SQL, but this requires taking advantage of the ODCIAggregate interface, which is part of Oracle's Extensibility Framework. See the Oracle documentation for more details on this functionality.