Oracle9i program with pl/sql Sample Questions:

1. 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 and control is passed to the calling environment.
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) Employee 108 has his email name updated based on the return result of the function.
E) The SQL statement executes successfully, because UPDATE and DELETE statements are ignoring in stored functions called from SQL expressions.

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

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

3. A CALL statement inside the trigger body enables you to call ______.

A) A package.
B) A stored procedure.
C) Another database trigger.
D) A stored function.

4. What can you do with the DBMS_LOB package?

A) Use the DBMS_LOB.WRITE procedure to write data to a BFILE.
B) Use the DBMS_LOB.FILECLOSE procedure to close the file being accessed.
C) Use the DBMS_LOBFILENAME function to locate an external BFILE.
D) Use the DBMS_LOB.FILEEXISTS function to find the location of a BFILE.

5. Examine this code:
CREATE OR REPLACE PACKAGE metric_converter
IS
c_height CONSTRAINT NUMBER := 2.54;
c_weight CONSTRAINT NUMBER := .454;
FUNCTION calc_height (p_height_in_inches NUMBER)
RETURN NUMBER;
FUNCTION calc_weight (p_weight_in_pounds NUMBER)
RETURN NUMBER;
END;
/
CREATE OR REPLACE PACKAGE BODY metric_converter
IS
FUNCTION calc_height (p_height_in_inches NUMBER)
RETURN NUMBER
IS
BEGIN
RETURN p_height_in_inches * c_height;
END calc_height;
FUNCTION calc_weight (p_weight_in_pounds NUMBER)
RETURN NUMBER
IS
BEGIN
RETURN p_weight_in_pounds * c_weight
END calc_weight
END metric_converter;
/
CREATE OR REPLACE FUNCTION calc_height (p_height_in_inches NUMBER)
RETURN NUMBER
IS
BEGIN
RETURN p_height_in_inches * metric_converter.c_height;
END calc_height;
/
Which statement is true?

A) If you remove the package body, then the package specification is removed.
B) The stand alone function CALC_HEIGHT cannot be created because its name is used in a packaged function.
C) If you remove the package specification, then the package body is removed.
D) If you remove the stand alone stored function CALC_HEIGHT, then the METRIC_CONVERTER package body and the package specification are removed.
E) If you remove the package specification, then the package body and the stand alone stored function CALC_HEIGHT are removed.
F) If you remove the package body, then the package specification and the stand alone stored function CALC_HEIGHT are removed.

Solutions: