Functions

functions
- deterministic
- parallel_enable
- authid
- pipelined
- result_cache
- dml inside functions

good examples here http://blogs.oracle.com/sysdba/2009/07/workaround_the_dmls_plsql_func.html

SQL> desc test
Name Null? Type
----------------------------------------- -------- ----------------------------
A NOT NULL NUMBER
NAME VARCHAR2(10)


SQL>
1 create or replace function test_fn(a number)
2 return number
3 is
4 begin
5 insert into test(a) values (a);
6 return 100;
7* end;
SQL> /

Function created.

SQL> select test_fn(20) from dual;
select test_fn(20) from dual
*
ERROR at line 1:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "NEETU.TEST_FN", line 5


SQL> insert into test(a) values (20);

1 row created.

SQL> commit;

Commit complete.

SQL> update test set a = test_fn(2000)
2 where a = 100;

0 rows updated.

SQL> select * from test;

A NAME
---------- ----------
20

SQL> update test set a = test_fn(2000)
2 where a = 20;
update test set a = test_fn(2000)
*
ERROR at line 1:
ORA-04091: table NEETU.TEST is mutating, trigger/function may not see it
ORA-06512: at "NEETU.TEST_FN", line 5


Also cannot perform a commit / rollback inside a function.




- function can be used in
pl/sql expression, sql query in where,having, connect by , start with , order by
values of insert and set of update

Restrictions When Calling Functions
from SQL Expressions
• User-defined functions that are callable from SQL
expressions must:
– Be stored in the database
– Accept only IN parameters with valid SQL data types, not
PL/SQL-specific types
– Return valid SQL data types, not PL/SQL-specific types
• When calling functions in SQL statements:
– Parameters must be specified with positional notation
– You must own the function or have the EXECUTE privilege

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

• Parameters must use positional notation. Named notation is not supported. (This is not true in 11g. In 11g can use mixed notation)
• 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.


Controlling Side Effects When
Calling Functions from SQL Expressions
Functions called from:
• A SELECT statement cannot contain DML statements
• An UPDATE or DELETE statement on a table T cannot
query or contain DML on the same table T
• SQL statements cannot end transactions (that is,
cannot execute COMMIT or ROLLBACK operations)
- A session control statement (such as SET ROLE)
- A system control statement (such as ALTER SYSTEM)