>>Why the stored functions can not be called from DML Statements? We can call a stored function from dml statement.
Here is simple example
CREATE OR REPLACE FUNCTION TEST_1 RETURN NUMBER IS
BEGIN
RETURN(10);
END;
SELECT TEST_1 FROM DUAL;
update emp
set sal = sal+ sal*test_1();
To be called from an sql statement a function should follow the following purity rules. 1) When called from a SELECT statement , the function should not modify database objects. 2) When called from INSERT , UPDATE, DELETE statements , the function can not query or modify the tables that was modified by the statement. 3)When called from SELECT , DELETE, UPDATE, INSERT Statements , a function should not contain TCL, DDL commands.
>>Why cant we modify the database by function
The main purpose of a function is to compute a value. Generally we use procedures to make modifications in database. If you want you can do modifications using functions also. But you can't call such functions from DML statements.
Above answer was rated as good by the following members: ibrarx, sandeep549
RE: Why the stored functions can not be called from DML Statements? Why cant we modify the database by function?
>>Why the stored functions can not be called from DML Statements? We can call a stored function from dml statement.
Here is simple example
CREATE OR REPLACE FUNCTION TEST_1 RETURN NUMBER IS
BEGIN
RETURN(10);
END;
SELECT TEST_1 FROM DUAL;
update emp
set sal sal+ sal*test_1();
To be called from an sql statement a function should follow the following purity rules. 1) When called from a SELECT statement the function should not modify database objects. 2) When called from INSERT UPDATE DELETE statements the function can not query or modify the tables that was modified by the statement. 3)When called from SELECT DELETE UPDATE INSERT Statements a function should not contain TCL DDL commands.
>>Why cant we modify the database by function
The main purpose of a function is to compute a value. Generally we use procedures to make modifications in database. If you want you can do modifications using functions also. But you can't call such functions from DML statements.
0 
1