Examine this code:
CREATE OR REPLACE FUNCTION gen_email_name
(p_first_name VARCHAR2, p_last_name VARCHAR2, p_id NUMBER)
RETURN VARCHAR2
is
v_email_name VARCHAR2(19);
BEGIN
v_email_home := SUBSTR(p_first_name, 1, 1) ||
SUBSTR(p_last_name, 1, 7) ||
‘@Oracle.com’;
UPDATE employees
SET email = v_email_name
WHERE employee_id = p_id;
RETURN v_email_name;
END;
You run this SELECT statement:
SELECT first_name, last_name
gen_email_name(first_name, last_name, 108) EMAIL
FROM employees;
What occurs?
A.
Employee 108 has his email name updated based on the return result of the function.
B.
The statement fails because functions called from SQL expressions cannot perform DML.
C.
The statement fails because the functions does not contain code to end the transaction.
D.
The SQL statement executes successfully, because UPDATE and DELETE statements are
ignoring in stored functions called from SQL expressions.
E.
The SQL statement executes successfully and control is passed to the calling environment.
Explanation:
When called from a SELECT statement or a parallelized UPDATE or DELETE statement, the
function cannot modify any database tables
When called from an UPDATE, or DELETE statement, the function cannot query or modify any
database tables modified by that statement.
When called from a SELECT, INSERT, UPDATE, or DELETE statement, the function cannot
execute SQL transaction control statements (such as COMMIT), session control statements (such
as SET ROLE), or system control statements (such as ALTER SYSTEM). Also, it cannot execute
DDL statements (such as CREATE) because they are followed by an automatic commit.
The function cannot call another subprogram that breaks one of the above restrictions.