Quick Search:
Oracle PL/SQL Code Library
Find Or Post Oracle Jobs
Oracle Discussion & Chat
 Oracle PL/SQL: FUNCTIONS: special restrictions Jump to:  
Category: >> Oracle PL/SQL >> FUNCTIONS: special restrictions Bookmark and Share

<< lastnext >>

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: Recompile
» 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

Comment: (none)

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 

Oracle Magazine
and Oracle White Papers

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.

Click here to find out more
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 20 users online    © 2009 psoug.org

Forgot your password?