Restrictions on calling functions from SQL statements

• Purity level are WNDS (write no database state) , RNDS (read no database state),WNPS (writes no package state),RNPS (reads no package state).
• The PL/SQL engine can determine the purity level of stand-alone functions.
• When the function is called from a SQL statement the purity level is checked. Not like the package functions.
• Any function called from a SQL statement cannot modify any database tables (WNDS).
• In order to be executed remotely function must have WNPS, RNPS.
• Functions called from the SELECT, VALUES, SET can write packages variables.
• A function is only as pure as the subprograms it calls.
• Stored function cannot be called in CHECK constraint clause of CREATE TABLE or ALTER TABLE.
• You have to use positional notation and not named notation and all parameters must be specified.
• User defined function must also meet the following requirements.
• The function has to be stored in the database either stand-alone or as part of a package.
• The function can take only IN parameter not IN OUT or OUT
• The formal Parameter must use only database types.
• The return type must be a database type.
• The function must not end the current transaction with COMMIT or ROLLBACK.
• It must not issue any ALTER SESSION or ALTER SYSTEM command