Restrictions on Calling Functions from SQL Expressions

Restrictions on Calling Functions from SQL Expressions:

The user-defined PL/SQL functions that are callable from SQL expressions must meet the following
requirements:

• The function must be stored in the database.
• The function parameters must be input parameters and should be valid SQL data types.
• The functions must return data types that are valid SQL data types. They cannot be PL/SQLspecific
data types such as BOOLEAN, RECORD, or TABLE. The same restriction applies to the
parameters of the function.



The following restrictions apply when calling a function in a SQL statement:

• Parameters must use positional notation. Named notation is not supported.
• You must own or have the EXECUTE privilege on the function.
Other restrictions on a user-defined function include the following:
• It cannot be called from the CHECK constraint clause of a CREATE TABLE or ALTER TABLE
statement.
• It cannot be used to specify a default value for a column.
Note: Only stored functions are callable from SQL statements. Stored procedures cannot be called
unless invoked from a function that meets the preceding requirements.

To execute a SQL statement that calls a stored function, the Oracle server must know whether the
function is free of specific side effects. Side effects are unacceptable changes to database tables.
Additional restrictions also apply when a function is called in expressions of SQL statements. In
particular, when a function is called from:
• A SELECT statement or a parallel UPDATE or DELETE statement, the function cannot modify a
database table, unless the modification occurs in an autonomous transaction
• An INSERT... SELECT (but not an INSERT... VALUES), an UPDATE, or a DELETE statement,
the function cannot query or modify a database table that was modified by that statement
• A SELECT, INSERT, UPDATE, or DELETE statement, the function cannot execute directly or
indirectly through another subprogram or through a SQL transaction control statement such as:
- A COMMIT or ROLLBACK statement
- A session control statement (such as SET ROLE)
- A system control statement (such as ALTER SYSTEM)
- Any data definition language (DDL) statements (such as CREATE), because they are
followed by an automatic commit
Note: The function can execute a transaction control statement if the transaction being controlled is
autonomous.