Oracle PL/SQL Programming, Third Edition

Advanced Topics

The following sections are most appropriate for experienced PL/SQL programmers. Here, I’ll touch on a number of advanced modularization topics, including calling functions in SQL, using table functions, and using deterministic functions.

Calling Your Function Inside SQL

Oracle allows you to call your own custom-built functions from within SQL. In essence, this flexibility allows you to customize the SQL language to adapt to application-specific requirements.

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 ...