PL/SQL - restrictions on calling Functions from SQL Expressions

1. What are the restrictions on calling Functions from SQL Expressions?

To be callable from SQL expressions, a user-defined PL/SQL function must meet certain requirements.

• Parameters to a PL/SQL function called from a SQL statement must use positional notation.

    Named notation is not supported.

• Stored PL/SQL functions cannot be called from the CHECK constraint clause of a CREATE or

ALTER TABLE command or be used to specify a default value for a column.

• User must have the EXECUTE privilege on the function to call it from a SQL statement.

• The functions must return data types that are valid SQL data types. They cannot be PL/SQL specific

data types such as BOOLEAN, RECORD, or TABLE. The same restriction applies to parameters of the function.

Note: Only stored functions are callable from SQL statements. Stored procedures cannot be called.

The ability to use a user-defined PL/SQL function in a SQL expression is available with PL/SQL 2.1

and later. Tools using earlier versions of PL/SQL do not support this functionality. Prior to Oracle9i,

user-defined functions can be only single-row functions. Starting with Oracle9i, user-defined functions

can also be defined as aggregate functions.

Note: Functions that  are callable from SQL expressions cannot contain OUT and IN OUT parameters.

Other functions can contain parameters with these modes, but it is not recommended.