PL/SQL - restrictions on calling Functions from SQL Expressions

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.

No comments:

Post a Comment