Description: Restrictions on User-Defined Functions User-defined functions are subject to the following restrictions:
- User-defined functions cannot be used in situations that require an unchanging definition. Thus, you cannot use user-defined functions:
-- In a CHECK constraint clause of a CREATE TABLE or ALTER TABLE statement
-- In a DEFAULT clause of a CREATE TABLE or ALTER TABLE statement
-- In addition, when a function is called from within a query or DML statement, the function cannot:
-- Have OUT or IN OUT parameters
-- Commit or roll back the current transaction, create a savepoint or roll back to a savepoint, or alter the session or the system. DDL statements implicitly commit the current transaction, so a user-defined function cannot execute any DDL statements.
-- Write to the database, if the function is being called from a SELECT statement. However, a function called from a subquery in a DML statement can write to the database.
-- Write to the same table that is being modified by the statement from which the function is called, if the function is called from a DML statement.
Except for the restriction on OUT and IN OUT parameters, Oracle Database enforces these restrictions not only for function when called directly from the SQL statement, but also for any functions that function calls, and on any functions called from the SQL statements executed by function or any functions it calls.
Language: Highlight Mode: PLSQL Last Modified: March 13th, 2009
-- functions called from SQL have special restrictions: -- they must be stored in the database -- they must own or have EXECUTE privilege -- When used in SELECT statement they cannot contain DML -- When used in UPDATE or DELETE they cannot SELECT or perform DML on the same table
SQL University.net courses meet the most demanding needs of the business world for advanced education
in a cost-effective manner. SQL University.net courses are available immediately for IT professionals
and can be taken without disruption of your workplace schedule or processes.
Compared to traditional travel-based training, SQL University.net saves time and valuable corporate
resources, allowing companies to do more with less. That's our mission, and that's what we deliver.