Oracle PL/SQL Programming, 5th 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 From Inside SQL

The Oracle database 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.

Note

Whenever the SQL runtime engine calls a PL/SQL function, it must “switch” to the PL/SQL runtime engine. The overhead of this context switch can be substantial if the function is called many times.

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:

• All of the function’s parameters must use the IN mode. Neither IN OUT nor OUT parameters are allowed in SQL-embedded stored functions.

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

• The function must be stored in the database. A function defined in a client-side PL/SQL ...