Vaibhav Doshi

• Sep 2nd, 2007

 

While Creating the stored functions, It check the Purity Level ( WNDS, RNDS , WNPS , RNPS ). so functions can not modify any database by using DML.

sampra

 Profile Answers by sampra 

• Mar 10th, 2008

 

give me sme simple answer

krishnaindia2007

 Profile Answers by krishnaindia2007 

• May 26th, 2008

 

>>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.



 

ushanaina

 Profile Answers by ushanaina 

• Nov 24th, 2009

 

Would like to add one more point for your answer,

Calling a function from select stament is not recomended as it will degrade the performance.