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