Test: Semester 1 Final Exam
Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.
Section 6
(Answer all questions in this section)
1. Examine the following code. To create a row trigger, what code should be included
at Line A?
CREATE TRIGGER dept_trigg
AFTER UPDATE OR DELETE ON departments
-- Line A
BEGIN ...
Mark for Review
(1) Points
AFTER EACH ROW
FOR EVERY ROW
FOR EACH ROW (*)
ON EACH ROW
ON EVERY ROW
Correct
2. Examine this code:
CREATE TRIGGER de_trigg
-- Line A
BEGIN ...
Which of the following are NOT valid at Line A ? (Choose two.)
Mark for Review
(1) Points
(Choose all correct answers)
AFTER LOGOFF ON SCHEMA (*)
AFTER LOGON ON SCHEMA
BEFORE LOGOFF ON SCHEMA
BEFORE DISCONNECT ON SCHEMA (*)
AFTER SERVERERROR ON SCHEMA
Correct
Section 7
(Answer all questions in this section)
3. No employees exist in department 75. What will be displayed when this code is
executed?
DECLARE
v_last_name employees.last_name%TYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE('A');
BEGIN
SELECT last_name INTO v_last_name
FROM employees WHERE department_id = 75;
DBMS_OUTPUT.PUT_LINE('B');
END;
DBMS_OUTPUT.PUT_LINE('C');
EXCEPTION
WHEN OTHERS THEN
Mark for Review
(1) Points
DBMS_OUTPUT.PUT_LINE('D');
END;
A
C
D
A
D
(*)
A
A
B
D
None of the above
Correct
4. Exceptions declared in a block are considered local to that block, and global to all
its sub-blocks. True or False?
Mark for Review
(1) Points
True (*)
False
Correct
5. What will happen when the following code is executed?
DECLARE
e_excep1 EXCEPTION;
e_excep2 EXCEPTION;
BEGIN
RAISE e_excep1;
EXCEPTION
WHEN e_excep1 THEN BEGIN
RAISE e_excep2; END;
END;
Mark for Review
(1) Points
It will fail to compile because you cannot have a subblock inside an exception
section.
It will fail to compile because e_excep1 is out of scope in the subblock.
It will fail to compile because you cannot declare more than one exception in
the same block.
It will compile successfully and return an unhandled e_excep2 to the calling
environment. (*)
Correct
6. Using two nested blocks, a TOO_MANY_ROWS exception is raised within the inner
block. Which of the following exception handlers will successfully handle the
exception?
Mark for Review
(1) Points
WHEN TOO_MANY_ROWS in the inner block
WHEN TOO_MANY_ROWS in either block
WHEN OTHERS in either block
WHEN OTHERS in the inner block
All of the above (*)
Correct
7. Examine the followiing code. Which exception handlers would successfully trap the
exception which will be raised when this code is executed? (Choose two.)
DECLARE
CURSOR emp_curs IS SELECT * FROM employees;
v_emp_rec emp_curs%ROWTYPE;
BEGIN
FETCH emp_curs INTO v_emp_rec;
OPEN emp_curs;
CLOSE emp_curs;
EXCEPTION ...
END;
Mark for Review
(1) Points
(Choose all correct answers)
WHEN CURSOR_NOT_OPEN
WHEN INVALID_CURSOR (*)
WHEN OTHERS (*)
WHEN NO_DATA_FOUND
WHEN INVALID_FETCH
Incorrect. Refer to Section 7 Lesson 2.
8. How can you retrieve the error code and error message of any Oracle Server
exception?
Mark for Review
(1) Points
By using the functions SQLCODE and SQLERRM (*)
By using the functions SQLCODE and SQLERR
By using RAISE_APPLICATION_ERROR
By defining an EXCEPTION variable and using PRAGMA EXCEPTION_INIT
Correct
9. An attempt to update an employee's salary to a negative value will violate a check
constraint and raise an ORA-02290 exception. Which of the following is a correct
definition of a handler for this exception?
Mark for Review
(1) Points
DECLARE
e_sal_excep EXCEPTION;
PRAGMA EXCEPTION_INIT(-02290,e_sal_excep);
DECLARE
PRAGMA EXCEPTION_INIT(e_sal_excep,-02290);
e_sal_excep EXCEPTION;
DECLARE
e_sal_excep EXCEPTION;
PRAGMA EXCEPTION_INIT(e_sal_excep,-02290);
(*)
DECLARE
e_sal_excep EXCEPTION;
PRAGMA_EXCEPTION_INIT(e_sal_exception,-02290);
DECLARE
e_sal_excep EXCEPTION;
PRAGMA EXCEPTION_INIT(e_sal_excep,02290);
Correct
10. An attempt to insert a null value into a NOT NULL table column raises an ORA-
01400 exception. How can you code an exception handler to trap this exception?
Mark for Review
(1) Points
Test for WHEN ORA-1400 in the exception section.
Declare a variable e_null_excep of type EXCEPTION, associate it with ORA-
01400 using a PRAGMA directive, and test for WHEN e_null_excep in the
exception section. (*)
Declare a variable e_null_excep of type VARCHAR2, associate it with ORA-
01400 using a PRAGMA directive, and test for WHEN e_null_excep in the
exception section.
Declare a variable as follows: e_null_excep EXCEPTION := -01400; Then test
for WHEN e_null_excep in the exception section.
Correct
Section 7
(Answer all questions in this section)
11. Examine the following code. What message or messages will be displayed when
this code is executed?
DECLARE
v_last_name employees.last_name%TYPE;
v_number NUMBER := 27;
BEGIN
v_number := v_number / 0;
SELECT last_name INTO v_last_name FROM employees
WHERE employee_id = 999;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No rows were found');
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('Attempt to divide by zero');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An error occurred');
END;
Mark for Review
(1) Points
No rows were found
Attempt to divide by zero (*)
Attempt to divide by zero No rows were found
An error occurred
No message will be displayed
Correct
12. Examine the following code fragment. At Line A, you want to raise an exception if
the fetched salary value is greater than 30000. How can you do this?
DECLARE
v_salary employees.salary%TYPE;
BEGIN
SELECT salary INTO v_salary FROM employees
WHERE employee_id = 100;
IF v_salary > 30000 THEN
-- Line A
END IF;
...
Mark for Review
(1) Points
Test for WHEN VALUE_TOO_HIGH in the exception section.
Use RAISE_APPLICATION_ERROR to raise an exception explicitly. (*)
Test for WHEN OTHERS in the exception section, because WHEN OTHERS
traps all exceptions.
Define an EXCEPTION variable and associate it with an Oracle Server error
number using PRAGMA EXCEPTION_INIT.
Correct
13. A user-defined exception is raised by using:
Mark for Review
(1) Points
FLAG exception_name;
RAISE exception-name; (*)
PRAGMA EXCEPTION_INIT
RAISE(error_number, exception_name);
Correct
14. No employees are in department_id 99. What output will be displayed when the
following code is executed?
DECLARE
v_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count
FROM employees WHERE department_id = 99;
IF v_count = 0 THEN
RAISE NO_DATA_FOUND;
DBMS_OUTPUT.PUT_LINE('No employees found');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Department 99 is empty');
END;
Mark for Review
(1) Points
No employees found
No employees found Department 99 is empty
Department 99 is empty (*)
The block will fail because you cannot explicitly RAISE a predefined Oracle
Server error such as NO_DATA_FOUND
Correct
15. No employees are in department 99. What message or messages will be displayed
when the following code is executed?
DECLARE
e_my_excep EXCEPTION;
BEGIN
BEGIN
UPDATE employees SET salary = 10000
WHERE department_id = 99;
IF SQL%ROWCOUNT = 0 THEN
RAISE e_my_excep;
END IF;
EXCEPTION
WHEN e_my_excep THEN
DBMS_OUTPUT.PUT_LINE('Message 1');
RAISE e_my_excep;
DBMS_OUTPUT.PUT_LINE('Message 2');
END;
DBMS_OUTPUT.PUT_LINE('Message 3');
EXCEPTION
WHEN e_my_excep THEN
DBMS_OUTPUT.PUT_LINE('Message 4');
END;
Mark for Review
(1) Points
Message 1
Message 3
Message 1
Message 2
Message 1
Message 3
Message 4
Message 1
Message 4
(*)
Correct
16. Which of the following will successfully return a user-defined error message?
Mark for Review
(1) Points
RAISE_APPLICATION_ERROR('Error Raised',-22001);
RAISE_APPLICATION_ERROR(-20257,'Error raised'); (*)
RAISE_APPLICATION_ERROR(-22001,'Error Raised');
RAISE_APPLICATION_ERROR('Error Raised',-20257);
Incorrect. Refer to Section 7 Lesson 3.
17. While a PL/SQL block is executing, more than one exception can occur at the same
time. True or False?
Mark for Review
(1) Points
True
False (*)
Correct
18. Which of the following are good practice guidelines for exception handling?
(Choose three.)
Mark for Review
(1) Points
(Choose all correct answers)
Test your code with different combinations of data to see what potential
errors can happen. (*)
Use an exception handler whenever there is any possibility of an error
occurring. (*)
Include a WHEN OTHERS handler as the first handler in the exception
section.
Allow exceptions to propagate back to the calling environment.
Handle specific named exceptions where possible, instead of relying on
WHEN OTHERS. (*)
Correct
19. Which of the following EXCEPTION sections are constructed correctly? (Choose
two.)
Mark for Review
(1) Points
(Choose all correct answers)
EXCEPTION
WHEN NO_DATA_FOUND THEN statement_1;
WHEN OTHERS THEN statement_2;
END;
(*)
EXCEPTION
WHEN OTHERS THEN statement_2;
WHEN NO_DATA_FOUND THEN statement_1;
END;
EXCEPTION
WHEN NO_DATA_FOUND THEN statement_1;
WHEN NO_DATA_FOUND THEN statement_2;
WHEN OTHERS THEN statement_3;
END;
EXCEPTION
WHEN OTHERS THEN statement_1;
END;
(*)
Correct
20. Examine the following code. Why does this exception handler not follow good
practice guidelines?
DECLARE
v_salary employees.salary%TYPE;
BEGIN
SELECT salary INTO v_salary FROM employees
WHERE employee_id = 999;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An error occurred');
END;
Mark for Review
(1) Points
You should not use DBMS_OUTPUT.PUT_LINE in an exception handler.
employee_id 999 does not exist in the employees table.
The exception handler should test for the named exception
NO_DATA_FOUND. (*)
The exception handler should COMMIT the transaction.
Correct
Section 8
(Answer all questions in this section)
21. You have created a procedure named MYPROC that accepts three IN parameters
A, B, and C (all numbers). Which of the following calls to MYPROC is NOT correct?
Mark for Review
(1) Points
myproc(5,10,20);
myproc(a=>5,b=>10,20) (*)
myproc(a=>5,b=>10,c=>20)
myproc(5,10,c=>20)
Correct
22. Which of the following statements about actual parameters is NOT true?
Mark for Review
(1) Points
An actual parameter is declared in the calling environment, not in the called
procedure.
An actual parameter must be the name of a variable. (*)
An actual parameter can have a Boolean datatype.
The datatypes of an actual parameter and its formal parameter must be
compatible.
An actual parameter can have a TIMESTAMP datatype.
Correct
23. You have created procedure MYPROC with a single parameter PARM1 NUMBER.
Now you want to add a second parameter to the procedure. Which of the
following will change the procedure successfully?
Mark for Review
(1) Points
ALTER PROCEDURE myproc ADD (parm2 NUMBER);
The procedure cannot be modified. Once a procedure has been created, the
number of parameters cannot be changed.
CREATE OR REPLACE PROCEDURE someproc
(parm1 NUMBER, parm2 NUMBER);
(You do not need to repeat the detailed code of the procedure, only the
header)
REPLACE PROCEDURE someproc
(parm1 NUMBER, parm2 NUMBER)
IS
BEGIN ...
CREATE OR REPLACE PROCEDURE someproc
(parm1 NUMBER, parm2 NUMBER)
IS
BEGIN ... (*)
Correct
24. A procedure will execute faster if it has at least one parameter.
Mark for Review
(1) Points
True
False (*)
Correct
25. Examine the following procedure:
CREATE OR REPLACE PROCEDURE smallproc
(p_param IN NUMBER)
IS
BEGIN ....
The procedure is invoked by:
DECLARE
v_param NUMBER := 20;
BEGIN
smallproc(v_param);
END;
Which of the following statements is true?
Mark for Review
(1) Points
p_param is a parameter and v_param is an argument
p_param is a formal parameter and 20 is an actual parameter
p_param is a formal parameter and v_param is an actual parameter (*)
p_param and v_param are both formal parameters, while 20 is an actual
parameter
p_param is an actual parameter and v_param is a formal parameter
Incorrect. Refer to Section 8 Lesson 2.
26. View and reload your code later by clicking on the History button in the SQL
Commands window. True or False?
Mark for Review
(1) Points
True
False (*)
Correct
27. One PL./SQL subprogram can be invoked from within many applications. True or
False?
Mark for Review
(1) Points
True (*)
False
Correct
28. Which of the following are characteristics of PL/SQL stored procedures? (Choose
three.)
Mark for Review
(1) Points
(Choose all correct answers)
They are named PL/SQL blocks. (*)
They must return exactly one value to the calling environment.
They can have an exception section. (*)
They can be invoked from inside a SQL statement.
They can accept parameters. (*)
Incorrect. Refer to Section 8 Lesson 1.
29. What is another name for a nested subprogram?
Mark for Review
(1) Points
Hosted subprogram
Local subprogram (*)
Limited subprogram
Correct
30. A PL/SQL procedure named MY_PROC1 has been successfully created in the
database. The procedure has no parameters. Which of the following will
successfully invoke the procedure in Application Express? (Choose two.)
Mark for Review
(1) Points
(Choose all correct answers)
DECLARE
v_var1 NUMBER := 20;
BEGIN
my_proc1(v_var1);
END;
EXECUTE my_proc1;
BEGIN
my_proc1;
END;
(*)
CREATE OR REPLACE PROCEDURE my_proc2 IS
BEGIN
my_proc1;
END my_proc2;
(*)
SELECT my_proc1 FROM DUAL;
Correct
Section 8
(Answer all questions in this section)
31. Which of the following are benefits of using PL/SQL subprograms rather than
anonymous blocks? (Choose three.)
Mark for Review
(1) Points
(Choose all correct answers)
Easier to write
Better data security (*)
Easier code maintenance (*)
Faster performance (*)
Do not need to declare variables
Correct
32. The following procedure has been created:
CREATE OR REPLACE PROCEDURE myproc
(A IN NUMBER := 20,
B IN NUMBER,
C IN NUMBER DEFAULT 30)
IS .....
Which of the following will invoke the procedure correctly?
Mark for Review
(1) Points
myproc(40);
myproc(10, B => 30, 50);
myproc(C => 25);
All of the above
None of the above (*)
Correct
33. The following procedure has been created:
CREATE OR REPLACE PROCEDURE defproc
(A IN NUMBER := 50,
B IN NUMBER,
C IN NUMBER DEFAULT 40)
IS .....
Which one of the following will invoke the procedure correctly?
Mark for Review
(1) Points
defproc(30 => A);
defproc(30, 60 => C);
defproc(40, 70); (*)
defproc(10 => A, 25 => C);
defproc;
Incorrect. Refer to Section 8 Lesson 3.
34. Procedure SOMEPROC has five parameters named A, B, C, D, E in that order. The
procedure was called as follows:
SOMEPROC(10,20,D=>50);
How was parameter B referenced?
Mark for Review
(1) Points
Positional (*)
Named
A combination of positionally and named
A combination of named and defaulted
Defaulted
Incorrect. Refer to Section 8 Lesson 3.
35. Suppose you set up a parameter with an explicit OUT mode. What is true about
that parameter?
Mark for Review
(1) Points
It must have a DEFAULT value.
It cannot have a DEFAULT value. (*)
It acts like a constant (its value cannot be changed inside the subprogram).
It must be the same type as the matching IN parameter.
It inherits its type from the matching IN parameter.
Incorrect. Refer to Section 8 Lesson 3.
Section 9
(Answer all questions in this section)
36. Which of the following is a difference between a procedure and a function?
Mark for Review
(1) Points
Functions cannot be nested; procedures can be nested to at least 8 levels.
A procedure can have default values for parameters, while a function cannot.
An explicit cursor can be declared in a procedure, but not in a function.
A function cannot be used within a SQL statement; a procedure can be used
within SQL.
A function must return a value; a procedure may or may not. (*)
Correct
37. Which of the following best describes a stored function?
Mark for Review
(1) Points
A subprogram that must return exactly one value (*)
A subprogram that must have at least one IN parameter
A subprogram that has no OUT or IN OUT parameters
A subprogram that executes automatically when a DML statement is executed
on a table
A subprogram which invokes another subprogram
Correct
38. A function must have at least one IN parameter, and must return exactly one
value.
Mark for Review
(1) Points
True
False (*)
Correct
39. Which of the following is a difference between a procedure and a function?
Mark for Review
(1) Points
A procedure can include DML statements, but a function cannot.
A function must have at least one IN parameter, while parameters are
optional for a procedure.
A procedure can return a BOOLEAN datatype, while a function cannot.
A function can be used inside a SQL statement, while a procedure cannot. (*)
A procedure can include an EXCEPTION section, while a function cannot.
Correct
40. You have created a function named NEWFUNC. You now change some of the
function code, and try to recreate the function by executing:
CREATE OR REPLACE FUNCTION newfunc .... ;
What happens?
Mark for Review
(1) Points
The command fails because the function already exists.
The function is automatically dropped and then recreated. (*)
The command fails because you should execute: CREATE AND REPLACE ....;
A second function named NEWFUNC_2 is created.
The function is dropped but not recreated.
Correct
Section 9
(Answer all questions in this section)
41. What is wrong with the following code?
CREATE FUNCTION badfunc
(p_param NUMBER(4))
RETURN BOOLEAN
IS BEGIN
RETURN (p_param > 10);
END badfunc;
Mark for Review
(1) Points
P_PARAM must be declared AFTER the RETURN clause.
P_PARAM must have a default value.
The datatype of the IN parameter cannot have a precision or scale. It must
be NUMBER, not NUMBER(4). (*)
RETURN (p_param > 10); is wrong because you cannot return an expression.
The NUMBER datatype must have a scale as well as a precision.
Correct
42. When must AUTHID CURRENT_USER be included in an autonomous transaction
subprogram?
Mark for Review
(1) Points
When declaring Definer's rights
When declaring Invoker's rights (*)
When using COMMIT or ROLLBACK
When using GRANT on the subprogram
Correct
43. User REYHAN creates the following procedure:
CREATE PROCEDURE proc1
AUTHID CURRENT_USER IS
v_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count
FROM tom.employees;
END;
User BILL wants to execute this procedure. What privileges will BILL need?
Mark for Review
(1) Points
EXECUTE on REYHAN.PROC1 and SELECT on TOM.EMPLOYEES (*)
EXECUTE on REYHAN.PROC1
SELECT on TOM.EMPLOYEES
BILL needs no privileges
None of the above. The procedure will fail to compile because REYHAN does
not have SELECT privilege on TOM.EMPLOYEES.
Correct
44. User BOB creates procedure MYPROC using the default Definer's Rights. BOB then
executes:
GRANT EXECUTE ON bob.myproc TO ted;
When TED invokes BOB.MYPROC, whose privileges are checked?
Mark for Review
(1) Points
TED's privileges
PUBLIC's privileges
SYSTEM's privileges
BOB's privileges (*)
ORACLE's privileges
Correct
45. You want to see the names, modes, and data types of the formal parameters of
function MY_FUNC in your schema. How can you do this? (Choose two)
Mark for Review
(1) Points
(Choose all correct answers)
Query USER_PARAMETERS
Query USER_SOURCE (*)
Query USER_FUNCTIONS
SHOW PARAMETER my_func;
DESCRIBE my_func; (*)
Correct
46. The following code shows the dependencies between three procedures:
CREATE PROCEDURE parent
IS BEGIN
child1;
child2;
END parent;
You now try to execute:
DROP PROCEDURE child2;
What happens?
Mark for Review
(1) Points
You cannot drop CHILD2 because PARENT is dependent on it.
CHILD2 is dropped successfully. PARENT and CHILD1 are both marked
INVALID.
The database automatically drops PARENT as well.
CHILD2 is dropped successfully. PARENT is marked INVALID, but CHILD1 is
still valid. (*)
The database automatically drops CHILD1 as well.
Correct
47. You want to remove the procedure NO_NEED from your schema. You execute:
DROP PROCEDURE no_need;
Which Data Dictionary views are updated automatically?
Mark for Review
(1) Points
USER_PROCEDURES
USER_OBJECTS
USER_SOURCE
All of the above (*)
None of the above
Correct
48. When creating a user-defined function, the size of the returned values may be up
to the size of any PL/SQL data type. True or False?
Mark for Review
(1) Points
True
False (*)
Correct
49. Which of the following is a benefit of user-defined functions? (Choose 3)
Mark for Review
(1) Points
(Choose all correct answers)
They can add business rules to the database and can be reused many times.
(*)
They can be used in a WHERE clause to filter data and thereby increase
efficiency. (*)
They can do the same job as built-in system functions such as UPPER and
ROUND.
They can often be used inside SQL statements. (*)
Correct
50. The function avg_ann_sal returns the average annual salary for a particular
department. The example below is a valid use of this function. True or False?
SELECT first_name, last_name
FROM employees
WHERE avg_ann_sal(20) > 15000;
Mark for Review
(1) Points
True (*)
False
Correct
