Oracle 1Z0-147 : Oracle9i program with pl/sql

Which two statements about packages are true? (Choose two)

• A. The specification and body of the package are stored separately in the database.
• B. The package specification is optional, but the package body is required.
• C. The specification and body of the package are stored together in the database.
• D. The package specification is required, but the package body is optional.
• E. Both the specification and body are required components of a package.

When creating a function, in which section will you typically find the RETURN keyword?

• A. HEADER only
• B. EXECUTABLE and HEADER
• C. DECLARATIVE
• D. DECLARATIVE,EXECUTABLE and EXCEPTION HANDLING

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. The SQL statement executes successfully, because UPDATE and DELETE statements are ignoring in stored functions called from SQL expressions.
• B. The SQL statement executes successfully and control is passed to the calling environment.
• C. The statement fails because the functions does not contain code to end the transaction.
• D. The statement fails because functions called from SQL expressions cannot perform DML.
• E. Employee 108 has his email name updated based on the return result of the function.

When creating stored procedures and functions, which construct allows you to transfer values to and from the calling environment?

• A. local variables
• B. arguments
• C. Substitution variables
• D. Boolean variables

Consider this scenario
A procedure X references a view Y that is based on a table Z .
Which two statements are true? (Choose two.)

• A. Y is an indirect dependent of X
• B. Y is a direct dependent of X
• C. Y is an indirect dependent of Z
• D. Y is a referenced object
• E. Z is an indirect dependent of Y
• F. Z is a direct dependent of X