17.3 Requirements for Stored Functions in SQL

There are several requirements 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 an Oracle Developer/2000 PL/SQL library or in an individual form cannot be called from within SQL. There would be no way for SQL to resolve the reference to the function.

• The function must be a row-specific function, not a column or group function. The function can apply only to a single row of data, not an entire column of data that crosses rows.

• 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 not (yet) supported in the database. These datatypes include BOOLEAN, BINARY_INTEGER, PL/SQL tables, PL/SQL records, and programmer-defined subtypes.

• Functions defined in packages must have a RESTRICT_REFEFRENCES pragma. 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 Section 17.5, "Calling Packaged Functions in SQL" for more details on this step.

All of the following function specifications would be rejected if used within a SQL statement:

/* SQL doesn't know about PL/SQL tables. */
TYPE string_tabtype IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
FUNCTION temp_table RETURN string_tabtype;

/* SQL doesn't know about Booleans. */
FUNCTION call_is_open (call_id_in IN call.call_id%TYPE) RETURN BOOLEAN;

FUNCTION calc_sales
   (company_id_in IN NUMBER, use_closed_orders_in IN BOOLEAN)
RETURN NUMBER;