1. The following example shows a valid record data type and variaable.

True
or False?
TYPE DeptRecTyp
IS RECORD (deptid NUMBER(4) NOT NULL := 99,
dname departments.department_name%TYPE,
loc departments.location_id%TYPE,
region regions%ROWTYPE );
dept_rec DeptRecTyp;
(1) Points

True (*)

False

Incorrect. Refer to Section 10 Lesson 3.

2. What is the correct format to declare a variable using

the following emp_pkg package composite data type? TYPE emprec_type IS TABLE OF
employees%ROWTYPE INDEX BY BINARY_INTEGER; (1) Points

emp_pkg.emprec_type;

emprec_type.emp_pkg;

v_emp_table emprec_type.emp_pkg;

v_emp_table emp_pkg.emprec_type; (*)

None of the above

Incorrect. Refer to Section 10 Lesson 3.

3. Which of the following are not allowed in a bodiless pa

ckage? (Choose three) (1) Points
(Choose all correct answers)

Subprograms (*)

Global variables

Private variables (*)

 User-defined exceptions

DML statements (*)

Incorrect. Refer to Section 10 Lesson 3.

4. Which of the following statements about a package initi

alization block is true? (1) Points

It cannot contain any SQL statements.

It is an anonymous block at the end of a package body. (*)

It is a procedure in a package that must be invoked before the rest of t

he package can be used.

It is an anonymous block in the package specification.

It is executed automatically every time any global variable in the packa

ge is referenced.

Correct

5. Functions called from a SQL query or DML statement must

not end the current transaction, or create or roll back to a savepoint. True or
False? (1) Points

True (*)

False

Incorrect. Refer to Section 10 Lesson 3.

6. Package MYPACK contains procedure MYPROC. You can see w

hich parameters MYPROC uses by executing: DESCRIBE mypack.myproc. True or False?
(1) Points

True

False (*)
 Correct

7. Which one of the following can NOT be part of a Package

? (1) Points

Procedures

Explicit cursors

Triggers (*)

Functions

Global variables

Incorrect. Refer to Section 10 Lesson 1.

8. Which of the following are good reasons for creating an

d using Packages?
Related procedures, functions and variables can be grouped together as a single
unit
We can recompile the package body without having to recompile the specification
We can create packages without needing any system privileges
We can declare INDEX BY tables and use them as parameters
(1) Points

A and B

A, B and C

A and C

A, B and D (*)

A, B, C and D

Incorrect. Refer to Section 10 Lesson 1.

9. Which of the following statements about packages is NOT

true ? (1) Points
 All procedures and functions must be declared in the specification. (*)

Cursors can be declared in the specification.

The body contains the detailed code of the subprograms.

Variables can be declared in the body.

The specification must be created before the body.

Incorrect. Refer to Section 10 Lesson 1.

10. Package OLDPACK is in your schema. What will happen whe

n the following statement is executed?
DROP PACKAGE oldpack;
(1) Points

The body will be dropped but the specification will be retained.

The specification will be dropped but the body will be retained.

Both the specification and the body will be dropped. (*)

The statement will fail because you must drop the body before you can dr
op the specification.

Correct
Section 10
11. Package NEWPACK contains several procedures and functio
ns, including private function PRIVFUNC. From where can PRIVFUNC be invoked? (Ch
oose two.) (1) Points
(Choose all correct answers)

From an anonymous block

From any procedure in NEWPACK (*)

From any private function in another package

 From any function in NEWPACK (*)

From any public procedure in another package

Incorrect. Refer to Section 10 Lesson 2.

12. In a package, public components are declared in the spe

cification but private components are not. True or False? (1) Points

True (*)

False

Correct

13. We need to declare a package variable named MYVAR, whic

h can be referenced by any subprogram in the package but can NOT be referenced f
rom outside the package. In the following code, where should MYVAR be declared?
CREATE OR REPLACE PACKAGE varpack IS
-- Point A
...
END varpack;
CREATE OR REPLACE PACKAGE BODY varpack IS
-- Point B
PROCEDURE varproc IS
-- Point C
BEGIN
...
END varproc;
PROCEDURE ...
...
-- Point D
END varpack;
(1) Points

Point A

Point B (*)

Point C

Point D

Point B or Point C, they will both work

 Incorrect. Refer to Section 10 Lesson 2.

14. Your schema contains four packages, each having a speci

fication and a body. You have also been granted privileges to access three packa
ges (and their bodies) in other users' schemas. What will be displayed by the fo
llowing query?
SELECT COUNT(*) FROM ALL_OBJECTS
WHERE object_type LIKE 'PACK%'
AND owner <> USER;
(1) Points

14

6 (*)

Incorrect. Refer to Section 10 Lesson 2.

Section 11
15. The UTL_FILE package can be used to create binary files
such as JPEGs as well as text files. True or False? (1) Points

True

False (*)

Incorrect. Refer to Section 11 Lesson 2.

16. Which of the following exceptions can be raised ONLY whe

n using the UTL_FILE package? (Choose two). (1) Points
(Choose all correct answers)

INVALID_PATH (*)
 NO_DATA_FOUND

VALUE_ERROR

READ_ERROR (*)

E_MYEXCEP

17. Why is it better to use DBMS_OUTPUT only in anonymous b

locks, not inside stored subprograms such as procedures? (1) Points

Because DBMS_OUTPUT cannot be used inside procedures

Because anonymous blocks display messages while the block is executing,

while procedures do not display anything until their execution has finished

Because DBMS_OUTPUT should be used only for testing and debugging PL/SQL
code (*)

Because DBMS_OUTPUT can raise a NO_DATA_FOUND exception if used inside a

packaged procedure

Correct

18. What will be displayed when the following code is execu

ted?
BEGIN
DBMS_OUTPUT.PUT('I do like');
DBMS_OUTPUT.PUT_LINE('to be');
DBMS_OUTPUT.PUT('beside the seaside');
END;
(1) Points

I do like to be
beside the seaside

I do like
to be
beside the seaside

I do like to be

I do liketo be
(*)
 I do like to be beside the seaside

Incorrect. Refer to Section 11 Lesson 2.

19. Package CURSPACK declares a global cursor in the packag

e specification. The package contains three public procedures: OPENPROC opens th
e cursor; FETCHPROC fetches 5 rows from the cursor's active set; CLOSEPROC close
s the cursor.
What will happen when a user session executes the following commands in the orde
r shown?
curspack.openproc; -- line 1
curspack.fetchproc; -- line 2
curspack.fetchproc; -- line 3
curspack.openproc; -- line 4
curspack.fetchproc; -- line 5
curspack.closeproc; -- line 6
(1) Points

The first 15 rows will be fetched.

The first 10 rows will be fetched, then the first 5 rows will be fetched
again.

The first 5 rows will be fetched three times.

An error will occur at line 2.

An error will occur at line 4. (*)

Incorrect. Refer to Section 11 Lesson 1.

20. Package MULTIPACK declares the following global variabl

e:
g_myvar NUMBER;
User DICK executes the following:
multipack.g_myvar := 45;
User HAZEL now connects to the database. Both users immediately execute:
BEGIN
DBMS_OUTPUT.PUT_LINE(multipack.g_myvar);
END;
What values will Dick and Hazel see?
(1) Points
 Dick: 45, Hazel: 45

Dick: 45, Hazel: 0

Dick: 45, Hazel: null (*)

Dick: 0, Hazel: 0

Both queries will fail because the syntax of DBMS_OUTPUT.PUT_LINE is inc

orrect

Incorrect. Refer to Section 11 Lesson 1.

Section 12
21. Name two reasons for using Dynamic SQL. (1) Poin
ts
(Choose all correct answers)

Provide the ability to execute SQL statements whose structure is unknown

until execution time. (*)

Provide the ability to handle mutating rows when executing a statement i

nvolving the same table.

Allow fetch of data for DML statements.

Enables session-control statements to be written and executed from PL/SQ

L. (*)

Incorrect. Refer to Section 12 Lesson 1.

22. A public packaged procedure contains the following SQL

statement:
UPDATE employees SET salary = salary * 1.1;
When is this SQL statement parsed? (1) Points

When the package specification is created

When the package body is created (*)

When the package header is loaded into memory.

 When the package is loaded into memory.

Only the first time the procedure is executed.

Incorrect. Refer to Section 12 Lesson 1.

23. You want to create a function which drops a table. You

write the following code:
CREATE OR REPLACE FUNCTION droptab
(p_tab_name IN VARCHAR2)
RETURN BOOLEAN IS
BEGIN
DROP TABLE p_tab_name;
RETURN TRUE;
EXCEPTION
WHEN OTHERS THEN RETURN FALSE;
END;
Why will this procedure not compile successfully?
(1) Points

Because you can never drop a table from inside a function

Because the PL/SQL compiler cannot check if the argument of p_tab_name i

s a valid table-name (*)

Because you do not have the privilege needed to drop a table

Because you cannot use RETURN in the exception section

Incorrect. Refer to Section 12 Lesson 1.

24. A SQL statement can pass through several stages. Which

of the following is NOT one of these stages? (1) Points

BIND

FETCH

PARSE

RETURN (*)
 EXECUTE

Incorrect. Refer to Section 12 Lesson 1.

25. All but which of the following are benefits of using th

e NOCOPY hint? (Choose two) (1) Points
(Choose all correct answers)

Safer because it uses passing by value. (*)

Efficient since it uses less memory.

Uses a larger block of server memory for faster access. (*)

Faster because a single copy of the data is used.

Eliminates extra processing.

Incorrect. Refer to Section 12 Lesson 2.

26. Where would you place the BULK COLLECT statement in the
following example?
DECLARE
TYPE DeptRecTab IS TABLE OF departments%ROWTYPE;
dept_recs DeptRecTab;
CURSOR c1 IS
SELECT department_id, department_name, manager_id, location_id
-- Position A
FROM departments
WHERE department_id > 70;
BEGIN
OPEN c1
-- Position B;
FETCH c1
-- Position C
INTO dept_recs;
END;
(1) Points

Position A

Position B
 Position C (*)

Incorrect. Refer to Section 12 Lesson 2.

27. To create a list of the top 20 movies from a catalog of

millions of titles, the following statement grabs those rows using a collection.
True or False?
...
TYPE nametab IS TABLE OF movies.title%TYPE;
Title_tab nametab;
...
SELECT title BULK COLLECT INTO title_tab FROM movies
ORDER BY rental_count DESC;
...
(1) Points

True (*)

False

Incorrect. Refer to Section 12 Lesson 2.

28. FORALL can be used with any DML statement. True or Fals
e? (1) Points

True (*)

False

Correct

Section 13
29. What is the purpose of using the CALL statement in a tr
igger? (1) Points

It allows an INSTEAD OF trigger to be a statement trigger.

It allows the trigger body code to be placed in a separate procedure. (*

)
 It prevents cascading triggers.

It allows the trigger body code to be placed in a separate procedure or

function.

It allows both DML events and DDL events to be handled using a single tr
igger.

Incorrect. Refer to Section 13 Lesson 4.

30. Which kinds of trigger can cause a mutating table probl

em? (Choose two.) (1) Points
(Choose all correct answers)

BEFORE UPDATE row triggers (*)

DDL triggers

AFTER DELETE row triggers (*)

Database Event triggers

INSTEAD OF triggers

Incorrect. Refer to Section 13 Lesson 4.

Section 13
31. A trigger automatically inserts a row into a logging ta
ble every time a user's session receives this error message:
ORA-00942: table or view does not exist
What kind of trigger is this? (1) Points

A row trigger

A statement trigger

A database event trigger (*)

A DDL trigger

An AFTER trigger
 Incorrect. Refer to Section 13 Lesson 4.

32. Examine this code:

CREATE TRIGGER new_trigg
AFTER CREATE ON reserved_word
BEGIN ...
Which of the following can be used in place of reserved_word? (Choose two.)
(1) Points
(Choose all correct answers)

TABLE

SCHEMA (*)

USER

DATABASE (*)

TABLE employees

Incorrect. Refer to Section 13 Lesson 4.

33. What is the event that will cause the trigger on the em
p_details view below to fire?
CREATE OR REPLACE TRIGGER new_emp_dept
INSTEAD OF INSERT ON emp_details
BEGIN
INSERT INTO new_emps
VALUES (:NEW.employee_id, :NEW.last_name,
:NEW.salary, :NEW.department_id);
new_depts
SET dept_sal = dept_sal + :NEW.salary
WHERE department_id = :NEW.department_id;
END;
(1) Points

An attempt to update salary column on the new_depts table

A new employee is added to the emp_details table

A procedure calls the new_emp_dept trigger.

 An attempt to add a row in the emp_details view (*)

An attempt to add a row in the new_depts table.

Incorrect. Refer to Section 13 Lesson 3.

34. Examine the following code. To create a row trigger, wh

at code should be included at Line A?
CREATE TRIGGER dept_trigg
AFTER UPDATE OR DELETE ON departments
-- Line A
BEGIN ...
(1) Points

AFTER EACH ROW

FOR EVERY ROW

FOR EACH ROW (*)

ON EACH ROW

ON EVERY ROW

Incorrect. Refer to Section 13 Lesson 3.

35. In the following code:

CREATE TRIGGER mytrigg
INSTEAD OF INSERT OR UPDATE ON my_object_name
FOR EACH ROW
BEGIN ...
my_object_name can be the name of a table. True or False?
(1) Points

True

False (*)

Incorrect. Refer to Section 13 Lesson 3.

36. What is wrong with the following code example for a comp
ound trigger?
CREATE OR REPLACE TRIGGER log_emps
FOR UPDATE OF salary ON employees
COMPOUND TRIGGER
TYPE t_log_emp IS TABLE OF log_table%ROWTYPE
INDEX BY BINARY_INTEGER;
log_emp_tab t_log_emp;
AFTER EACH ROW IS
BEGIN
-- some action
END AFTER EACH ROW;
AFTER STATEMENT IS
BEGIN
-- some action
END AFTER STATEMENT;
END log_emps;
(1) Points

The order of the timing statements is reversed. (*)

The declaration section is missing the DECLARE keyword.

The triggering event FOR UPDATE is not allowed.

The COMPOUND TRIGGER statement is missing IS.

There is nothing wrong with this example.

Correct

37. INSTEAD OF triggers are always row triggers, even if FO

R EACH ROW is omitted. True or False? (1) Points

True (*)

False

Incorrect. Refer to Section 13 Lesson 3.

38. A trigger can be created in the database or within an a

pplication. True or False? (1) Points

True (*)
 False

Correct

39. A business rule states that an employee's salary cannot

be greater than 99,999.99 or less than 0. The best way to enforce this rule is
by using: (1) Points

A datatype of NUMBER(7,2) for the SALARY column

A database trigger

A check constraint (*)

An application trigger

A view

Incorrect. Refer to Section 13 Lesson 1.

40. Which of the following are NOT stored inside the databa
se? (Choose two.) (1) Points
(Choose all correct answers)

A PL/SQL package specification

A database trigger

An anonymous block (*)

An application trigger (*)

A sequence

Incorrect. Refer to Section 13 Lesson 1.

Section 13
41. Which of the following are good guidelines to follow wh
en creating a database trigger? (Choose two.) (1) Points
(Choose all correct answers)
 Where possible, use a trigger to enforce a foreign key constraint.

Use triggers to override privilege checking and view other users' privat
e tables.

Do not use a trigger to replace or duplicate something which the Oracle

Server does automatically. (*)

Use triggers to prevent unauthorized users from SELECTing confidential d

ata.

Do not create a trigger that automatically fires another trigger. (*)

Incorrect. Refer to Section 13 Lesson 1.

42. What type of database object would you create to write

an auditing record automatically every time a user connects to the database?
(1) Points

A procedure

A complex view

A trigger (*)

A function

A package

Incorrect. Refer to Section 13 Lesson 1.

43. Which of the following best describes a database trigge

r? (1) Points

A subprogram that checks whether a user has typed the correct password t
o log on to the database.

A PL/SQL subprogram that executes automatically whenever an associated d

atabase event occurs. (*)
 A PL/SQL subprogram that always returns exactly one value.

A subprogram that is invoked explicitly by the calling application.

A PL/SQL subprogram that inserts rows into a logging table.

Correct

44. What is wrong with the following code?

CREATE TRIGGER dept_trigg
BEFORE UPDATE OF department_name ON departments
BEGIN
DBMS_OUTPUT.PUT_LINE(:NEW.department_name);
END;
(1) Points

You cannot use :NEW in a BEFORE trigger, only in an AFTER trigger.

You cannot use :NEW or :OLD in a statement trigger. (*)

You cannot use DBMS_OUTPUT.PUT_LINE inside a trigger.

The second line should be:

BEFORE UPDATE ON departments.department_name

Incorrect. Refer to Section 13 Lesson 2.

45. What is wrong with the following code?

CREATE OR REPLACE TRIGGER loc_trigg
BEFORE DELETE ON locations
BEGIN
RAISE_APPLICATION_ERROR(-20201,'Invalid delete');
ROLLBACK;
END;
(1) Points

The last line should be:

END loc_trigg;

You cannot use RAISE_APPLICATION_ERROR inside a trigger.

The second line should be:

BEFORE DELETE OF locations
 You cannot use ROLLBACK inside a trigger.
(*)

Nothing is wrong, this trigger will compile and execute successfully.

Incorrect. Refer to Section 13 Lesson 2.

46. A DML statement trigger fires only once for each trigge
ring DML statement, while a row trigger fires once for each row processed by the
triggering statement. True or False? (1) Points

True (*)

False

Incorrect. Refer to Section 13 Lesson 2.

47. Examine the following code:

CREATE TRIGGER emp_trigg
-- Line A
BEGIN
INSERT INTO log_table VALUES (USER, SYSDATE);
END;
Which of the following can NOT be coded at Line A?
(1) Points

BEFORE UPDATE ON employees

AFTER INSERT OR DELETE ON employees

AFTER SELECT ON employees (*)

BEFORE DELETE ON employees

AFTER UPDATE OF last_name ON employees

Correct

48. You need to disable all triggers that are associated wi

th DML statements on the DEPARTMENTS table. Which of the following commands shou
ld you use? (1) Points

ALTER TABLE departments DISABLE ALL TRIGGERS; (*)

ALTER TRIGGER DISABLE ALL ON departments;

ALTER TABLE departments DISABLE TRIGGERS;

DISABLE ALL TRIGGERS ON departments;

ALTER TABLE departments DROP ALL TRIGGERS;

Incorrect. Refer to Section 13 Lesson 5.

49. User AYSEGUL successfully creates the following trigger

:
CREATE TRIGGER loc_trigg
BEFORE UPDATE ON aysegul.locations
BEGIN ....
AYSEGUL now tries to drop the LOCATIONS table. What happens?
(1) Points

An error message is displayed because you cannot drop a table that is as

sociated with a trigger.

The table is dropped and the trigger is disabled.

The trigger is dropped but the table is not dropped.

Both the table and the trigger are dropped. (*)

None of the above.

Incorrect. Refer to Section 13 Lesson 5.

50. Which of the following will remove a trigger in your sc

hema named EMP_TRIGG from the database? (1) Points

DROP emp_trigg TRIGGER;

ALTER TRIGGER emp_trigg DISABLE;

DROP TRIGGER emp_trigg; (*)

REMOVE TRIGGER emp_trigg;

None of the above

Incorrect. Refer to Section 13 Lesson 5.