Snippet Name: FUNCTIONS: special restrictions
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.
Also see:
» FUNCTIONS: Deterministic
» FUNCTIONS: Nested Functions
» FUNCTIONS: IF statement
» FUNCTIONS: date/time
» FUNCTIONS: Sample functions
» FUNCTIONS: drop
» FUNCTIONS: Recompile
» FUNCTIONS: DEBUG mode
» FUNCTIONS: IN OUT parameter
» FUNCTIONS: with output parameters
» FUNCTIONS: with parameters
» FUNCTIONS: without parameters
» FUNCTIONS: Create function
» FUNCTIONS: special restrictions
» FUNCTIONS: System Privileges
» IN Function
» Built-In Functions: CASE
» Built-In Functions: DECODE
» SUBST and INSTR together
» INSTR (InString)
» SUBSTR (SubString)
» String Functions: REVERSE
» String Functions: LENGTH
» String Functions: INSTR
» String Functions: CONCAT
» String Functions: CHAR
» String Functions: INITCAP
» String Functions: LOWER
» String Functions: UPPER
» Date Functions: NUMTOYMINTERVAL
Comment: (none)
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 |
