what is the difference between stored procedures and stored functions.
this question has been asked in an interview
one answer i gave that functions can return one value and procedures can return more than one value and second is a function can be called within a sql select statement and can be incorporated within a dml statement. but they said some thing else that functions are non_deterministic. what exactly does it mean can u plz clarify my doubt and explain me any more differences are there between a stored procedure and stored function
The words "deterministic" and "non-deterministic" refer only to functions - they do not apply to procedures at all.
A deterministic function is one that will always return the same result given the same inputs - e.g. UPPER, SUBSTR, TO_NUMBER,...
A non-deterministic function is one that may return different results at different times for the same inputs. This would be a function that selects from the database, or refers to package global variables, etc. For example, a function "GET_EMP_NAME( p_id IN emp.id%TYPE ) RETURN VARCHAR2" could return different results for the same ID if you updated the EMP table in between.
Oracle requires that you declare functions to be DETERMINISTIC before you can use them to create function-based indexes.
Apart from that, your list of differences between procedures and functions is correct.