Oracle PLSQL Section 13-15 Answers

Test: Section 13 Quiz
Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.
Section 13 Quiz, , , ,
(Answer all questions in this section), , , ,
1. , You can see trigger information in the following Data Dictionary views except:, , Mark for Review
(1) Points
USER_TRIGGERS
USER_ERRORS
USER_SOURCE (*)
You can view trigger information in all of these Data Dictionary views.
USER_OBJECTS
Incorrect. Refer to Section 13 Lesson 5.

2. , After the following SQL statement is executed, all the triggers on the DEPARTMENTS table will no
longer fire, but will remain in the database. True or False?ALTER TABLE departments DISABLE ALL
TRIGGERS;, , Mark for Review
(1) Points
True (*)
False
Correct
3. , Which of the following will remove from the database the trigger in your schema named
EMP_TRIGG?, , Mark for Review
(1) Points
ALTER TRIGGER emp_trigg DISABLE;
DROP emp_trigg TRIGGER;
DROP TRIGGER emp_trigg; (*)

None of these.
REMOVE TRIGGER emp_trigg;
4. , A trigger can be a public subprogram within a PL/SQL package. True or False?, , Mark for Review
(1) Points
True
False (*)
5. , While editing a document in Microsoft Word, you go to the FILE menu and SAVE your work. To do
this, Microsoft Word has executed an application trigger. True or False?, , Mark for Review
(1) Points
True (*)
False
Correct
Page 1 of 3, Next, Summary

6. A user's schema contains procedure MYPROC, function MYFUNC, trigger MYTRIGG and package
MYPACK which contains a public procedure PACKPROC. These subprograms have no parameters, and
the function returns a NUMBER. Which of the following calls to these objects (from an anonymous
block) are incorrect? (Choose two) Mark for Review
(1) Points
(Choose all correct answers)
IF NOT myfunc THEN ... (*)
mypack.packproc;
myproc;
mytrigg; (*)
v_number := myfunc;
Incorrect Incorrect. Refer to Section 13 Lesson 1.
7. Examine the following code. To create a row trigger, what code should be included at Line A?
CREATE OR REPLACE TRIGGER del_emp_trigg
BEFORE DELETE ON employees
---- Line A
BEGIN ...
Mark for Review
(1) Points
Nothing is needed because DML triggers are row triggers by default.
FOR EVERY ROW
FOR EACH ROW (*)
FOR ALL ROWS
FOR EVERY ROW

8. The following view and trigger have been created:
CREATE VIEW dept_view AS SELECT * FROM departments;
CREATE OR REPLACE TRIGGER dept_view_trigg
INSTEAD OF UPDATE ON dept_view
BEGIN
DBMS_OUTPUT.PUT_LINE('Sample Message');
END;
Departments 50 and 80 exist but department 81 does not. A user now executes the following statement:
UPDATE dept_view SET department_name = 'Sales'
WHERE department_id IN (50,80,81);
What happens?
Mark for Review
(1) Points
None of these.
No rows are updated and "Sample Message" is displayed three times.

Two rows are updated and "Sample Message" is displayed once.
No rows are updated and "Sample Message" is displayed twice. (*)
No rows are updated and "Sample Message" is displayed once.
9. With which kind of trigger can the :OLD and :NEW qualifiers be used? Mark for Review
(1) Points
Row triggers (*)
Statement triggers
Database Event triggers
AFTER triggers
DDL triggers
Correct Correct
10. We want to prevent employees from being deleted on Sundays. To do this, we create the
following trigger:
CREATE OR REPLACE TRIGGER stop_del_emps
....... DELETE ON employees -- Line A
BEGIN
IF TO_CHAR(SYSDATE','DY') = 'SUN' THEN
RAISE_APPLICATION_ERROR(-20101,'Invalid delete');
END IF;
END;
Should this be a BEFORE or AFTER trigger, and why?
Mark for Review
(1) Points
It should be a BEFORE trigger because if an AFTER trigger were created, the employee would already
have been deleted by the time the trigger checks the date. (*)
It should be an AFTER trigger because the Oracle Server cannot fire the trigger until it knows that the
employee has been deleted.
It should be a BEFORE trigger because you cannot use RAISE_APPLICATION_ERROR with AFTER
triggers.
It does not matter, either a BEFORE or an AFTER trigger could be created.
Correct Correct
11. What is wrong with the following code?
CREATE OR REPLACE TRIGGER emp_dept_trigg
BEFORE UPDATE OR DELETE ON employees, departments
BEGIN
...
Mark for Review
(1) Points
The second line should be:
BEFORE (UPDATE,DELETE) ON employees, departments
DML triggers must be row triggers, so FOR EACH ROW is missing

BEFORE UPDATE OR DELETE ON employees OR departments
One trigger can be associated with only one table
(*)
12. 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?
Mark for Review
(1) Points

AFTER UPDATE OF last_name ON employees
AFTER SELECT ON employees (*)
AFTER INSERT OR DELETE ON employees
BEFORE UPDATE ON employees
CREATE TRIGGER call_trigg
AFTER LOGOFF ON SCHEMA
BEGIN
CALL drop_proc;
END;
Mark for Review
(1) Points
All of these. (*)

When using CALL, you must not code END;
When using CALL, you must not code BEGIN
You cannot code an AFTER LOGOFF trigger
The CALL statement must not end with a semicolon (;)
Correct Correct
14. Mutating table errors can be caused by DML triggers, but not by database event triggers. True
or False? Mark for Review
(1) Points
True (*)
False
Correct Correct
15. User HARJIT wants to prevent any objects which he owns from being dropped. Harjit decides
to execute the following code:
CREATE OR REPLACE TRIGGER stop_drop
---- Line A
BEGIN
RAISE_APPLICATION_ERROR(-20201,'Attempted drop');
END;
What should Harjit code at Line A?
Mark for Review
(1) Points
BEFORE DROP ON TABLE
BEFORE DROP ON SCHEMA (*)
BEFORE DROP ON HARJIT
BEFORE DROP ON USER_OBJECTS
BEFORE DROP ON OWNER

1. What is the event that will cause the trigger on the emp_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,
UPDATE new_depts :NEW.salary, :NEW.department_id);
new_depts
SET dept_sal = dept_sal + :NEW.salary
WHERE department_id = :NEW.department_id;
END;
Mark for Review
(1) Points
A new employee is added to the emp_details table
An attempt to update salary column on the new_depts table
An attempt to add a row in the new_depts table.
A procedure calls the new_emp_dept trigger.

An attempt to add a row in the emp_details view (*)
2. There are 3 employees in department 90 and 5 employees in department 50. The following
trigger has been created:
CREATE TRIGGER upd_emp_trigg
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
...
A user now executes:
UPDATE employees
SET department_id = 50
WHERE department_id = 90;
How many times will the trigger fire?
Mark for Review
(1) Points
Once
Three times (*)
Four times
Five times
Eight times
3. Which of the following can NOT be coded in the body of a DML trigger? (Choose two.) Mark
for Review
(1) Points
IF UPDATING ('JOB_ID') THEN
IF INSERTING THEN
IF SELECTING THEN (*)
IF DELETING THEN
IF OTHERS THEN (*)
4. After the following SQL statement is executed, all the triggers on the DEPARTMENTS table will
no longer fire, but will remain in the database. True or False?
ALTER TABLE departments DISABLE ALL TRIGGERS;
Mark for Review
(1) Points
True (*)
False
Correct Correct
5. Which of the following will remove from the database the trigger in your schema named
EMP_TRIGG? Mark for Review
(1) Points
ALTER TRIGGER emp_trigg DISABLE;
None of these.
REMOVE TRIGGER emp_trigg;
DROP emp_trigg TRIGGER;
DROP TRIGGER emp_trigg; (*)
6. By default, any user can create a DML trigger on a table in his/her schema. True or False? Mark
for Review
(1) Points
True
False (*)
7. Which of the following could NOT cause a DDL or Database Event trigger to fire? Mark for
Review
(1) Points
The DBA starts up the database.
A user deletes rows from the EMPLOYEES table. (*)
A specific exception is raised in a user's session.
A table is dropped.
A user connects to the database.
8. The database administrator wants to write a log record every time any user's session raises an
ORA-00942 exception. The DBA decides to create the following trigger:
CREATE OR REPLACE TRIGGER log_942_trigg
AFTER SERVERERROR ON DATABASE
BEGIN
-- Line A
INSERT INTO log_table VALUES ( ...);
END;
What should the DBA code at Line A?
Mark for Review
(1) Points
IF (SERVERERROR = 942) THEN
IF (IS_SERVERERROR = 942) THEN
IF (SERVERERROR(942)) THEN
IF (IS_SERVERERROR(942)) THEN (*)
IF (IS_SERVERERROR(ORA-00942)) THEN
9. What is the benefit of using the CALL statement in a trigger body? Mark for Review
(1) Points
It prevents data being read from a mutating table.
It allow both DDL events and database events to be handled by a single trigger.
It allows the trigger body code to be placed in a separate procedure. (*)
It allows the database administrator to monitor who is currently connected to the database.

10. Which of the following are good guidelines to follow when creating a database trigger?
(Choose two.) Mark for Review
(1) Points
Where possible, use a trigger to enforce a foreign key constraint.
Use triggers to prevent unauthorized users from SELECTing confidential data.
Do not create a trigger that automatically fires another trigger. (*)
Use triggers to override privilege checking and view other users' private tables.
Do not use a trigger to replace or duplicate something which the Oracle Server does automatically. (*)
11. 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: Mark for Review
(1) Points
A database trigger
A datatype of NUMBER(7,2) for the SALARY column
A view
A check constraint (*)
An application trigger
12. Which of the following events could NOT automatically fire a database trigger? Mark for
Review
(1) Points
You click your mouse on a button to choose the correct answer to this question (*)
A DML operation on a view
A SQL INSERT statement
A user logging on to the database
The Database Administrator shuts down the database
Correct Correct
13. An AFTER UPDATE trigger can specify more than one column. True or False? Mark for
Review
(1) Points
True (*)
False
Correct Correct
BEGIN
...
Mark for Review
(1) Points
(*)
Correct Correct
15. A DML statement trigger fires only once for each triggering DML statement, while a row
trigger fires once for each row processed by the triggering statement. True or False? Mark for Review
(1) Points
True (*)
False
Correct Correct
1. You decide to create the following trigger:
CREATE OR REPLACE TRIGGER empl_trigg
BEGIN
-- Line A
RAISE_APPLICATION_ERROR('Cannot update salary');
ELSE
INSERT INTO log_table values (USER, SYSDATE);
END IF;
END;
You want the trigger to prevent updates to the SALARY column, but allow updates to all other columns.
What should you code at Line A?
Mark for Review
(1) Points
IF UPDATING(SALARY) THEN
IF UPDATE('SALARY') THEN
IF UPDATING SALARY THEN
IF UPDATE(SALARY) THEN
IF UPDATING('SALARY') THEN (*)
2. Examine the following trigger. It should raise an application error if a user tries to update an
employee's last name. It should allow updates to all other columns of the EMPLOYEES table. What
should be coded at line A?
CREATE TRIGGER stop_ln_trigg
BEGIN
-- Line A
RAISE_APPLICATION_ERROR(-20201,'Updating last name not allowed');
END IF;
END;
Mark for Review
(1) Points
IF UPDATING('LAST_NAME') THEN (*)
IF UPDATE('LAST_NAME') THEN
IF UPDATING LAST_NAME THEN
IF UPDATING THEN
Correct Correct
3. What is wrong with the following code example for a compound 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;
Mark for Review
(1) Points
The COMPOUND TRIGGER statement is missing IS.
There is nothing wrong with this example.
The declaration section is missing the DECLARE keyword. (*)
The order of the timing statements is reversed.

The triggering event FOR UPDATE is not allowed.
4. A business rule states that an employee's salary must be between 4000 and 30000. We could
enforce this rule using a check constraint, but it is better to use a database trigger. True or False?
Mark for Review
(1) Points
True
False (*)
5. A user's schema contains procedure MYPROC, function MYFUNC, trigger MYTRIGG and package
MYPACK which contains a public procedure PACKPROC. These subprograms have no parameters, and
the function returns a NUMBER. Which of the following calls to these objects (from an anonymous
block) are incorrect? (Choose two) Mark for Review
(1) Points

IF NOT myfunc THEN ... (*)
mytrigg; (*)
mypack.packproc;
myproc;
v_number := myfunc;
Incorrect Incorrect. Refer to Section 13 Lesson 1
6. Which of the following are NOT stored inside the database? Mark for Review
(1) Points
A database trigger
A PL/SQL package specification

An anonymous block (*)
A sequence
An index
Review
(1) Points
A table is dropped.
Correct Correct
8. The database administrator wants to write a log record every time an Oracle Server error
occurs in any user's session. The DBA creates the following trigger:
CREATE TRIGGER log_errs_trigg
-- Line A
BEGIN
INSERT INTO errlog_table VALUES (...);
END;
What should the DBA code at Line A ?
Mark for Review
(1) Points
AFTER ERROR ON DATABASE
AFTER ORACLE ERROR ON SCHEMA
AFTER SERVER ERROR ON DATABASE

AFTER SERVERERROR ON DATABASE (*)
AFTER SERVERERROR ON SCHEMA
CREATE OR REPLACE TRIGGER call_trigg
AFTER UPDATE OR DELETE ON employees
BEGIN
CALL del_emp_proc
END;
Mark for Review
(1) Points
You cannot use a CALL statement in a DML trigger.
When using CALL, only one DML statement can be tested, so UPDATE OR DELETE is wrong.
The CALL statement should end with a semicolon (;)

When CALL is used, the BEGIN and END; statements should be omitted. (*)
10. Which of the following is the correct syntax for creating a DML trigger associated with the
EMPLOYEES table? The trigger must fire whenever an employee's JOB_ID is updated, but not if a
different column is updated. Mark for Review
(1) Points
CREATE TRIGGER job_upd_trigg
AFTER UPDATE OF job_id ON employees
BEGIN ...
(*)
AFTER UPDATE ON employees(job_id)
BEGIN ...
AFTER UPDATE ON employees.job_id
BEGIN ...
WHENEVER UPDATE OF job_id IN employees
BEGIN ...
Correct Correct
11. Which of the following are possible keywords for the timing component of a trigger? (Choose
three.) Mark for Review
(1) Points
INSTEAD
WHENEVER
AFTER (*)
BEFORE (*)
INSTEAD OF (*)
CREATE OR REPLACE TRIGGER loc_trigg
BEFORE DELETE ON locations
BEGIN
ROLLBACK;
END;
Mark for Review
(1) Points
BEFORE DELETE OF locations
The last line should be:
END loc_trigg;
Nothing is wrong, this trigger will compile and execute successfully.
You cannot use RAISE_APPLICATION_ERROR inside a trigger.

You cannot use ROLLBACK inside a trigger.
(*)
13. Which dictionary view would you query to see the detailed body code of triggers in your
schema? Mark for Review
(1) Points
USER_OBJECTS
USER_SOURCE
None of these; you cannot view the code of the trigger body after the trigger has been created.
USER_TRIGGERS (*)
USER_TRIGGER
14. User KULJIT creates two triggers named EMP1_TRIGG and EMP2_TRIGG, which are both DML
triggers referencing her EMPLOYEES table. Kuljit now wants to remove both of these triggers from the
database. What command(s) should Kuljit use to do this? Mark for Review
(1) Points
DROP TRIGGER emp1_trigg AND emp2_trigg;
DROP TRIGGERS ON employees;
DROP TRIGGER emp1_trigg;
(*)
DROP ALL TRIGGERS ON employees;

15. By default, any user can create a DML trigger on a table in his/her schema. True or False?
Mark for Review
(1) Points
True
False (*)
Section 14 Quiz
(Answer all questions in this section)
1. If two related objects are in different databases, the dependency between them is
automatically recorded on the dependent object's data dictionary. True or False? Mark for Review
(1) Points
True
False (*)
2. Which Data Dictionary table stores information about a procedure's timestamp? Mark for
Review
(1) Points
USER_MODE
USER_TIMESTAMP
USER_OBJECTS (*)
USER_PROCEDURES
3. In this scenario, the following status is given for each procedure:
- Procedure A is local and has a time stamp of 10 AM
- Procedure B is remote and has a local time stamp of 5 AM and has a remote time stamp of 4 AM
In Timestamp Mode, Procedure A will execute successfully at 11 AM. True or False?
Mark for Review
(1) Points
True
False (*)
4. Which is not a mode that can be used to determine the dependency status of schema objects
when dealing with remote dependencies? Mark for Review
(1) Points
Signature Mode
Time Stamp Mode
Time Mode (*)

All of these are valid.
5. Procedure B has the ZERO_DIVIDE pre-defined exception added to its EXCEPTION section. It is
compiled successfully. In Timestamp Mode, Procedure A, which is dependent on remote Procedure B,
will compile and execute successfully. True or False? Mark for Review
(1) Points
True
False (*)

6. Which of the following techniques will make it more likely that an invalidated PL/SQL subprogram
will recompile successfully? (Choose two.) Mark for Review
(1) Points
SELECTing a list of column-names instead of using SELECT *

Declaring record structures using %ROWTYPE (*)
Including a column list with INSERT statements (*)
Using a cursor FOR loop instead of opening and closing the cursor explicitly
7. When a table is dropped, all PL/SQL subprograms that reference the table are automatically
dropped. True or False? Mark for Review
(1) Points
True
False (*)

8. Which of the following will display only the number of invalid package bodies in your schema?
Mark for Review
(1) Points
SELECT COUNT(*) FROM user_objects
WHERE object_type LIKE 'PACKAGE%'
AND status = 'INVALID';
SELECT COUNT(*) FROM user_dependencies
WHERE type = 'PACKAGE BODY'
SELECT COUNT(*) FROM user_packages
WHERE status = 'INVALID';
WHERE object_type = 'PACKAGE BODY'
(*)

9. A procedure includes the following code:
CURSOR loc_curs IS SELECT location_id, city, country_id FROM locations;
Which of the following changes to the LOCATIONS table will allow the procedure to be recompiled
successfully without editing its code? (Choose two.)
Mark for Review
(1) Points
RENAME locations TO new_locations;
ALTER TABLE locations DROP COLUMN postal_code; (*)
ALTER TABLE locations DROP COLUMN city;
ALTER TABLE locations ADD (climate VARCHAR2(30)); (*)

10. Procedure get_depts has been marked invalid because one of the objects it references has
been altered. Which of the following statements are true? (Choose two.) Mark for Review
(1) Points
The procedure can be recompiled manually by:
ALTER PROCEDURE get_depts COMPILE;
(*)
The procedure does not need to be recompiled.
The procedure will be recompiled automatically the next time it is invoked. The recompilation may or
may not be successful.
(*)
The procedure will be recompiled automatically the next time it is invoked. The recompilation will
always be successful.
The procedure can be recompiled manually by:
ALTER PROCEDURE get_depts RECOMPILE;

11. The PL/SQL variable V_LAST_NAME is used to store fetched values of the LAST_NAME column of
the EMPLOYEES table. To minimize dependency failures, the variable should be declared as:
v_last_name VARCHAR2(25);
True or False?
Mark for Review
(1) Points
True
False (*)
12. A procedure show_emps contains the following declaration:
CURSOR emp_curs IS SELECT last_name, salary FROM employees;
What will happen to the procedure if a new column is added to the employees table?
Mark for Review
(1) Points
The procedure will automatically be dropped and must be recreated.
The procedure will still be valid and execute correctly because it does not reference the added
column.
The procedure will be marked invalid and must be recompiled before it can be reexecuted. (*)
Users' privileges to execute the procedure will automatically be revoked.
13. Which of the following database objects are created when the utldtree.sql script is run?
(Choose three.) Mark for Review
(1) Points
The deptree_fill procedure (*)

The deptree and ideptree views (*)
The deptree table
The utldtree table
The deptree_temptab table (*)
CREATE VIEW ed_view AS
SELECT * FROM employees NATURAL JOIN departments;
CREATE PROCEDURE ed_proc IS
CURSOR ed_curs IS SELECT * FROM ed_view;
Which of the following statements about dependencies are true? (Choose two.)
Mark for Review
(1) Points

ED_CURS is directly dependent on ED_VIEW.
ED_PROC is referenced by ED_VIEW.
EMPLOYEES is referenced by ED_VIEW. (*)
ED_PROC is indirectly dependent on DEPARTMENTS. (*)
ED_PROC is directly dependent on EMPLOYEES.
15. Function FETCH_EMP references the EMPLOYEES table. The table is modified by:
ALTER TABLE employees ADD (resume CLOB);
When will the ORACLE server try to recompile FETCH_EMP automatically?
Mark for Review
(1) Points
When the command ALTER FUNCTION fetch_emp COMPILE; is executed

When the RESUME column is dropped from the EMPLOYEES table
When FETCH_EMP is dropped and recreated
The next time a user session invokes FETCH_EMP (*)
1. Procedure B has its local variable emp_number changed to emp_name. The data type of emp_id is
changed from number to integer. It is compiled successfully. In Signature Mode, Procedure A, which is
dependent on remote Procedure B, will compile and execute successfully. True or False? Mark for
Review
(1) Points
True (*)
False
Correct Correct
(1) Points
Signature Mode
Time Mode (*)
Time Stamp Mode
3. A remote dependency is when a dependent object resides ________________. Mark for

Review
(1) Points
In the same object

In a local database
In a remote database (*)
Review
(1) Points
USER_PROCEDURES
USER_TIMESTAMP
USER_MODE
USER_OBJECTS (*)

5. Procedure B has a new IN OUT parameter added to it. It compiles successfully. Procedure A is
dependent on Procedure B. In Signature mode,what will happen? Mark for Review
(1) Points
Procedure B will fail when executed.
Both procedures will fail.
Both procedures will execute successfully.
Procedure A will fail when executed. (*)
6. User BOB wants to know which objects reference his DEPARTMENTS table. Which of the following
must he execute to populate the DEPTREE_TEMPTAB table? Mark for Review
(1) Points
BEGIN
deptree_fill('TABLE','DEPARTMENTS');
END;
BEGIN
deptree_fill('TABLE','BOB','DEPARTMENTS');
END;
(*)
BEGIN
ideptree('TABLE','BOB','DEPARTMENTS');
END;
BEGIN
utldtree('DEPARTMENTS');
END;
7. Package emp_pack contains two public procedures: get_emps and upd_emps. A separate
procedure emp_proc invokes emp.pack.get_emps. The upd_emps package body code is now altered,
and the package body (but not the package specification) is recreated.
emp_proc will be marked invalid and needs to be recompiled. True or False?
Mark for Review

(1) Points
True
False (*)
8. The IDEPTREE view shows dependencies by indenting the lines of output instead of by using a
NESTED_LEVEL column. True or False? Mark for Review
(1) Points
True (*)
False
Correct Correct
9. A single PL/SQL subprogram such as a procedure can be both a referenced object and a
dependent object. True or False? Mark for Review
(1) Points
True (*)
False
Correct Correct
CREATE FUNCTION deptfunc
RETURN NUMBER IS
v_count NUMBER(6);
BEGIN
SELECT COUNT(*) INTO v_count FROM departments;
RETURN v_count;
END;
Which of the following will display the dependency between DEPTFUNC and DEPARTMENTS?
Mark for Review
(1) Points
SELECT name, type
FROM user_dependencies
WHERE name IN ('DEPTFUNC','DEPARTMENTS');
SELECT name, type, referenced_name, referenced_type
WHERE name = 'DEPARTMENTS'
AND type = 'TABLE';
SELECT object_name, object_type
FROM user_objects
WHERE object_name IN ('DEPARTMENTS','DEPTFUNC')
AND referenced = 'YES';
WHERE referenced_name = 'DEPARTMENTS'
AND referenced_type = 'TABLE';
(*)

Mark for Review
(1) Points

12. Which of the following will NOT help to minimize dependency failures? (Choose two.) Mark
for Review
(1) Points
Declaring scalar variables with NOT NULL if the corresponding table column has a NOT NULL
constraint (*)
Including a column list with INSERT statements
Declaring scalar variables using the %TYPE attribute
SELECTing a list of column names instead of using SELECT * (*)
Declaring records using the %ROWTYPE attribute

(1) Points
The deptree table
The utldtree table
14. Which data dictionary view shows information about references and dependencies? Mark
for Review
(1) Points
USER_LOCAL_DEPENDENCIES
USER_DEPENDENCIES (*)
DEPTREE
USER_REFERENCES
15. View dept_view is based on a select from table departments. Procedure show_dept contains
code which selects from dept_view. Which of the following statements are true? (Choose three.)
Mark for Review
(1) Points
emp_view is directly dependent on show_dept

departments is indirectly dependent on show_dept
dept_view is directly dependent on departments (*)
show_dept is indirectly dependent on departments (*)
show_dept is directly dependent on dept_view (*)
1. Which of the following techniques will make it more likely that an invalidated PL/SQL subprogram
will recompile successfully? (Choose two.) Mark for Review
(1) Points
Declaring record structures using %ROWTYPE (*)
Including a column list with INSERT statements (*)
Using a cursor FOR loop instead of opening and closing the cursor explicitly
SELECTing a list of column-names instead of using SELECT *
2. Which of the following is NOT created when the utldtree.sql script is run? Mark for Review
(1) Points
The USER_DEPENDENCIES view (*)
The DEPTREE_TEMPTAB table
The DEPTREE_FILL procedure
The DEPTREE view
Correct Correct
SELECT first_name, salary INTO v_first_name, v_salary
FROM employees WHERE employee_id = 100;
Which of the following changes to the employees table will allow the procedure to be recompiled
successfully ? (Choose two.)
Mark for Review
(1) Points
The table is dropped.
The table name is changed to newemps.
The first_name column is dropped from the table.
The table is dropped but a public table exists with the same name and structure. (*)
A new column is added to the table. (*)

Mark for Review
(1) Points
5. A single procedure can be both a referenced object and a dependent object. True or False?
Mark for Review
(1) Points
True (*)
False
Correct Correct
Mark for Review
(1) Points
column.
Mark for Review
(1) Points

Mark for Review
(1) Points

9. Which of the following will NOT help to minimize dependency failures? (Choose two.) Mark
for Review
(1) Points
Declaring scalar variables using the %TYPE attribute
SELECTing a list of column names instead of using SELECT * (*)
Declaring scalar variables with NOT NULL if the corresponding table column has a NOT NULL
constraint (*)
Declaring records using the %ROWTYPE attribute

Including a column list with INSERT statements
10. Which of the following will display only the number of invalid package bodies in your schema?
Mark for Review
(1) Points
SELECT COUNT(*) FROM user_packages
WHERE status = 'INVALID';
SELECT COUNT(*) FROM user_dependencies
WHERE type = 'PACKAGE BODY'
WHERE object_type = 'PACKAGE BODY'
(*)
WHERE object_type LIKE 'PACKAGE%'
11. A remote dependency is when a dependent object resides ________________. Mark for Review
(1) Points
In a local database
In the same object
(1) Points
Time Stamp Mode
Signature Mode
Time Mode (*)
Review
(1) Points
USER_MODE
USER_TIMESTAMP
USER_OBJECTS (*)
USER_PROCEDURES
(1) Points
True
False (*)
15. The Signature of a procedure is calculated from : Mark for Review
(1) Points
All of these make up the signature. (*)

The data types of the parameters.
The modes of the parameters.
The name of the procedure.
Correct Correct
Section 15 Quiz
1. Which pair of DBMS_WARNING commands would allow you to obtain the current settings and
change and restore those settings in a PL/SQL subprogram? (Choose two) Mark for Review
(1) Points
DBMS_WARNING.ADD_WARNING_SETTING_CAT
DBMS_WARNING.GET_WARNING_STRING
DBMS_WARNING.SET_WARNING_SETTING_STRING (*)
DBMS_WARNING.GET_WARNING_SETTING_STRING (*)
2. In the USER_ERRORS data dictionary view, if an error is prefixed with "Warning," the command
completes but has a side effect the user needs to know about. For all other errors, the command
terminates abnormally. True or False? Mark for Review
(1) Points
True (*)
False
Correct Correct
3. The two statements below are equivalent. True or False?
('INFORMATIONAL','ENABLE','SESSION');
and
ALTER SESSION
SET PLSQL_WARNINGS = 'ENABLE:INFORMATIONAL';
Mark for Review
(1) Points
True (*)
False
Correct Correct
4. What does the following statement do?
DBMS_WARNING.ADD_WARNING_SETTING_CAT('PERFORMANCE','ENABLE','SESSION');
Mark for Review
(1) Points
Enables the PERFORMANCE warning category, leaving other category settings unchanged, for the
current session. (*)
Disables all warning categories, then enables the PERFORMANCE category.

Add the PERFORMANCE warning category into a PL/SQL variable.
Enables the PERFORMANCE warning category, setting other category settings to disabled.
Enables the PERFORMANCE warning category, leaving other category settings unchanged.
Correct Correct
5. When wrapping subprograms, the entire PL/SQL code must be included as an IN argument with
data type VARCHAR2 up to 32,767 characters. True or False? Mark for Review
(1) Points
True (*)
False
Correct Correct
6. Obfuscation does not allow anyone to see the source code, including the owner. True or False?
Mark for Review
(1) Points
True (*)
False
Correct Correct
7. Below are the steps to obfuscate a PL/SQL subprogram using the wrapper utility. What is the
missing step?
1. Create a text file containing your complete unwrapped source code.
2. Execute WRAP to create a second text file containing the wrapped code.
3. Connect to the database and execute the wrapped text file as a script to compile the wrapped code
into the Data Dictionary.
Mark for Review
(1) Points
Create the wrapper using DBMS_DML.
Verify the code was hidden in USER_CODE.

Log into the database server computer. (*)
Validate the wrapped code at the operating system level.
Grant EXECUTE privileges on the subprogram.
8. The value of DBMS_DB_VERSION.VER_LE_11 is TRUE when the version of the Oracle database
is version 11 or greater. True or False? Mark for Review
(1) Points
True
False (*)

9. In the following example, what statement belongs in Line A?
ALTER SESSION SET PLSQL_CCFLAGS = 'debug:true';
CREATE OR REPLACE PROCEDURE testproc IS BEGIN
...
$IF $$debug $THEN
DBMS_OUTPUT.PUT_LINE('This code was executed');
-- Line A
...
END testproc;
ALTER SESSION SET PLSQL_CCFLAGS = 'debug:false';
Mark for Review
(1) Points
$$END;
$ELSIF
$ENDIF
$END;
$END (*)
10. Identify the selection directives used in conditional compilation. Mark for Review
(1) Points
$IF
$THEN
$ELSE $ELSIF
$ENDIF
$IF
$THEN
$ELSE
$END
$CCFLAG
$$IF
$$THEN
$$ELSE
$$END
$$DEBUG
$$IF
$$THEN
$$ELSE
$$ELSIF
$$END
$IF
$THEN
$ELSE
$ELSIF
$END
(*)
11. To include selections of code for compilation based on user-defined values, use the
PLSQL_CCFLAGS parameters. True or False? Mark for Review
(1) Points
True (*)
False
Correct Correct
12. When setting PLSQL_OPTIMIZE_LEVEL = 2, the compiled code will remove code and
exceptions that can never be executed. True or False? Mark for Review
(1) Points
True (*)
False
Correct Correct
13. When setting PLSQL_OPTIMIZE_LEVEL = 3, the compiled code will run more slowly, but it will
work with older versions of the Oracle software. True or False? Mark for Review
(1) Points
True
False (*)
14. To determine the current setting for PLSQL_OPTIMIZE_LEVEL, query the data dictionary view
USER_PLSQL_OBJECTS_SETTINGS. True or False? Mark for Review
(1) Points
True (*)
False
Correct Correct
15. To set the PLSQL_CODE_TYPE to its fastest execution speed, which command do you use?
Mark for Review
(1) Points
ALTER SESSION SET PLSQL_CODE_TYPE = INTERPRETED;

ALTER SYSTEM SET PLSQL_CODE_TYPE=NATIVE;
ALTER SESSION SET PLSQL_CODE_TYPE = NATIVE; (*)
ALTER SESSION SET PLSQL_CODE_TYPE = 2;
ALTER SYSTEM SET PLSQL_CODE_TYPE=2;
1. PLSQL_CODE_TYPE determines the type of code for both PL/SQL code and for SQL statements,
which is what speeds up the execution speed. True or False? Mark for Review
(1) Points
True
False (*)

2. Which are NOT examples of benefits of using PLSQL_OPTIMIZE_LEVEL. (Choose two) Mark
for Review
(1) Points
Control what PL/SQL does with useless code
Modify source code to optimize frequently-used elements at the top (*)
Separating compiled code so that separate units may be repeated as needed (*)
Combining compiled code from one subprogram into another subprogram
Backward compatible with previous versions of the Oracle database
3. What is the name of the column used to identify the PLSQL_OPTIMIZE_LEVEL in the data
dictionary? Mark for Review
(1) Points
OPTIMIZE_LEVEL
PLSQL_OPTIMIZE_LEVEL (*)
PLSQL_CODE_TYPE
USER_PLSQL_OPTIMIZE
PLSQL_LEVEL
4. To set the PLSQL_CODE_TYPE to its fastest execution speed, which command do you use?
Mark for Review
(1) Points

5. Below are the steps to obfuscate a PL/SQL subprogram using the wrapper utility. What is the
missing step?
1. Create a text file containing your complete unwrapped source code.
2. Execute WRAP to create a second text file containing the wrapped code.
3. Connect to the database and execute the wrapped text file as a script to compile the wrapped code
into the Data Dictionary.
Mark for Review
(1) Points
Verify the code was hidden in USER_CODE.

Create the wrapper using DBMS_DML.
Log into the database server computer. (*)
Validate the wrapped code at the operating system level.
Grant EXECUTE privileges on the subprogram.
6. Which is NOT a benefit of obfuscation? Mark for Review
(1) Points
Source code is hidden from all users.
Source code is visible to the owner. (*)
Protection for intellectual property is provided.

Source code is not loaded in the data dictionary.
7. Obfuscation does not allow anyone to see the source code, including the owner. True or False?
Mark for Review
(1) Points
True (*)
False
Correct Correct
8. Which pair of DBMS_WARNING commands would allow you to obtain the current settings and
change and restore those settings in a PL/SQL subprogram? (Choose two) Mark for Review
(1) Points

DBMS_WARNING.GET_WARNING_STRING
DBMS_WARNING.GET_WARNING_SETTING_STRING (*)
DBMS_WARNING.SET_WARNING_SETTING_STRING (*)
DBMS_WARNING.SET_WARNING_SETTING_STRING
('ENABLE:SEVERE','SESSION');
and
ALTER SESSION
SET PLSQL_WARNINGS = 'ENABLE:SEVERE';
Mark for Review
(1) Points
True
False (*)
10. Select the three categories of PL/SQL compiler warning levels. Mark for Review
(1) Points
SEVERE (*)
INFORMATIONAL (*)
PERFORMANCE (*)
DISABLE
ENABLE
11. A warning in PL/SQL is the same as an error in PL/SQL, but can only be viewed through the
USER_ERRORS data dictionary view. True or False? Mark for Review
(1) Points
True
False (*)
12. Conditional compilation allows you to include extra code to help with debugging, which can
be removed once errors are resolved. True or False? Mark for Review
(1) Points
True (*)
False
Correct Correct
13. Conditional Compilation allows you to include some source code in your PL/SQL program that
may be compiled or may be ignored depending on: Mark for Review
(1) Points
The version of the Oracle software you are using.
Any of these could be used. (*)
The values of an initialization parameter.
The value of a global package constant.
14. To include selections of code for compilation based on user-defined values, use the
PLSQL_CCFLAGS parameters. True or False? Mark for Review
(1) Points
True (*)
False
Correct Correct
...
$IF $$debug $THEN
-- Line A
...
END testproc;
Mark for Review
(1) Points
$END (*)
$ELSIF
$ENDIF
$$END;
$END;
Correct Correct
1. Select the three categories of PL/SQL compiler warning levels. Mark for Review
(1) Points
DISABLE
SEVERE (*)
ENABLE
PERFORMANCE (*)
INFORMATIONAL (*)
2. A warning in PL/SQL is the same as an error in PL/SQL, but can only be viewed through the
USER_ERRORS data dictionary view. True or False? Mark for Review
(1) Points
True
False (*)
3. The informational warning level of the PL/SQL compiler identifies _____________. Mark for
Review
(1) Points
Code that can cause unexpected behavior or wrong results.
Code that can never be executed and other poor coding practices. (*)
Code that can cause execution speed to be slow.
All of these.
('INFORMATIONAL','ENABLE','SESSION');
and
ALTER SESSION
SET PLSQL_WARNINGS = 'ENABLE:INFORMATIONAL';
Mark for Review
(1) Points
True (*)
False
Correct Correct
5. One benefit of obfuscation is to protect intellectual property written in PL/SQL. True or False?
Mark for Review
(1) Points
True (*)
False
Correct Correct
6. Which is NOT a benefit of obfuscation? Mark for Review
(1) Points
Source code is hidden from all users.

Protection for intellectual property is provided.
Source code is not loaded in the data dictionary.
Source code is visible to the owner. (*)
7. To obfuscate the procedure my_proc, what statement should be at Line A?
BEGIN
-- Line A
('CREATE OR REPLACE PROCEDURE mycleverproc
(p_param1 IN NUMBER, p_param2 OUT NUMBER)
IS BEGIN
... /* some clever but private code here */
END mycleverproc;');
END;
Mark for Review
(1) Points
DBMS_DML.CREATE_WRAP
DBMS_DDL.WRAP_CODE
DBMS_DDL.WRAPPED
DBMS_DDL.CREATE_WRAP
DBMS_DDL.CREATE_WRAPPED (*)
(1) Points
PLSQL_LEVEL
PLSQL_CODE_TYPE
OPTIMIZE_LEVEL
USER_PLSQL_OPTIMIZE
9. When setting PLSQL_OPTIMIZE_LEVEL = 2, the compiled code will remove code and exceptions
that can never be executed. True or False? Mark for Review
(1) Points
True (*)
False
Correct Correct
(1) Points
True (*)
False
Correct Correct
11. To set the PLSQL_CODE_TYPE to its fastest execution speed, which command do you use? Mark
for Review
(1) Points

Correct Correct
12. Identify some benefits of using conditional compilation. (Choose two) Mark for Review
(1) Points
Use new features with the latest database release and disable them with older database versions (*)
Speed up the compilation time of a lengthy PL/SQL subprogram
Determine initialization values during start up of a database session
Activate debugging or tracing statements in the development environment (*)

...
$IF $$debug $THEN
-- Line A
...
END testproc;
Mark for Review
(1) Points
$ELSIF
$$END;
$END (*)
$ENDIF
$END;
14. If the version and release of the Oracle database in use is 10.2, what statement will allow
syntax available in version 10.2 or later? Mark for Review
(1) Points
$IF DBMS_DB_VERSION.VER_LE_10_2 $THEN
-- some messaage
$ELSE
-- some action
$END
-- some messaage
$ELSE
-- some action
$END
(*)
$IF DBMS_DB_VERSION.VER_GE_10_1 $THEN

-- some messaage
$ELSE
-- some action
$END
-- some messaage
$ELSE
-- some action
$END
15. Identify the selection directives used in conditional compilation. Mark for Review
(1) Points
$IF
$THEN
$ELSE
$ELSIF
$END
(*)
$IF
$THEN
$ELSE $ELSIF
$ENDIF
$$IF
$$THEN
$$ELSE
$$END
$$DEBUG
$IF
$THEN
$ELSE
$END
$CCFLAG
$$IF
$$THEN
$$ELSE
$$ELSIF
$$END
Correct Correct
(1) Points
PLSQL_LEVEL
PLSQL_CODE_TYPE
OPTIMIZE_LEVEL
USER_PLSQL_OPTIMIZE
(1) Points
True (*)
False
Correct Correct
3. What are the valid values for PLSQL_OPTIMIZE_LEVEL in the data dictionary? Mark for
Review
(1) Points
0,1,2,3,4
1,2,3
0,1,2,3 (*)
1,2,3,4

4. Which are NOT examples of benefits of using PLSQL_OPTIMIZE_LEVEL. (Choose two) Mark
for Review
(1) Points
Separating compiled code so that separate units may be repeated as needed (*)
Combining compiled code from one subprogram into another subprogram
Modify source code to optimize frequently-used elements at the top (*)
Backward compatible with previous versions of the Oracle database
Control what PL/SQL does with useless code

5. In the USER_ERRORS data dictionary view, if an error is prefixed with "Warning," the command
completes but has a side effect the user needs to know about. For all other errors, the command
terminates abnormally. True or False? Mark for Review
(1) Points
True (*)
False
Correct Correct
6. Which term best describes the action below:
A PL/SQL program compiles successfully, but contains some code that causes performance to be less
than optimal.
Mark for Review
(1) Points
Warning (*)
Error
Correct Correct
7. The informational warning level for PL/SQL compiled code identifies the code that may cause
execution speed to be slow. True or False? Mark for Review
(1) Points
True
False (*)
DBMS_WARNING.SET_WARNING_SETTING_STRING
('ENABLE:SEVERE','SESSION');
and
ALTER SESSION
SET PLSQL_WARNINGS = 'ENABLE:SEVERE';
Mark for Review
(1) Points
True
False (*)
9. For PL/SQL code larger than 32,767 characters, you must use the wrap utility. True or False?
Mark for Review
(1) Points
True (*)
False
Correct Correct
10. You created a package named pkg1. The code is approximately 90,000 characters. What is the
statement that you use to obfuscate this package in the database? Mark for Review
(1) Points
DBMS_DDL.CREATE_WRAPPED ('CREATE OR REPLACE PACKAGE BODY pkg1...);
DBMS_DDL.CREATE_WRAP (pkg1);
WRAP pkg1.sql
WRAP INAME=pkg1.sql (*)
DBMS_DDL.CREATE_WRAPPED (pkg1);
11. One benefit of obfuscation is to protect intellectual property written in PL/SQL. True or False?
Mark for Review
(1) Points
True (*)
False
Correct Correct
12. If the version and release of the Oracle database in use is 10.2, what statement will allow
syntax available in version 10.2 or later? Mark for Review
(1) Points
-- some messaage
$ELSE
-- some action
$END
-- some messaage
$ELSE
-- some action
$END
-- some messaage
$ELSE
-- some action
$END
-- some messaage
$ELSE
-- some action
$END
(*)
...
$IF $$debug $THEN
-- Line A
...
END testproc;
Mark for Review
(1) Points
$ENDIF
$END;
$$END;
$ELSIF
$END (*)
14. Identify some benefits of using conditional compilation. (Choose two) Mark for Review
(1) Points
Use new features with the latest database release and disable them with older database versions (*)
Determine initialization values during start up of a database session
Activate debugging or tracing statements in the development environment (*)
Speed up the compilation time of a lengthy PL/SQL subprogram
15. The value of DBMS_DB_VERSION.VER_LE_11 is TRUE when the version of the Oracle database
is version 11 or greater. True or False? Mark for Review
(1) Points
True
False (*)
Section 14 Quiz

1. PL/SQL procedure A invokes procedure B, which in turn invokes procedure C, which references
table T. If table T is dropped, which of the following statements is true? Mark for Review
(1) Points
C is invalid but A and B are still valid
A, B and C are all invalid (*)
B and C are invalid but A is still valid
A, B and C are all still valid
None of these.

Mark for Review
(1) Points
RETURN NUMBER IS
v_count NUMBER(6);
BEGIN
RETURN v_count;
END;
Mark for Review
(1) Points
FROM user_objects
SELECT name, type
(*)
AND type = 'TABLE';

4. User ALICE owns a procedure show_emps which references table employees. Which of the
following will generate information that shows this dependency? Mark for Review
(1) Points
BEGIN deptree_fill('ALICE','PROCEDURE','SHOW_EMPS');
END;
BEGIN deptree_fill('PROCEDURE','ALICE','SHOW_EMPS');
END;
BEGIN deptree_fill('TABLE','ALICE','EMPLOYEES');
END;
(*)
BEGIN deptree_fill('ALICE','TABLE','EMPLOYEES');
END;
BEGIN deptree_fill('TABLE','EMPLOYEES');
END;
Mark for Review
(1) Points
column.

6. The PL/SQL variable V_LAST_NAME is used to store fetched values of the LAST_NAME column of
the EMPLOYEES table. To minimize dependency failures, the variable should be declared as:
True or False?
Mark for Review
(1) Points
True
False (*)
7. View dept_view is based on a select from table departments. Procedure show_dept contains
code which selects from dept_view. Which of the following statements are true? (Choose three.)
Mark for Review
(1) Points

departments is indirectly dependent on show_dept
emp_view is directly dependent on show_dept
show_dept is directly dependent on dept_view (*)
show_dept is indirectly dependent on departments (*)
dept_view is directly dependent on departments (*)
8. User BOB wants to know which objects reference his DEPARTMENTS table. Which of the
following must he execute to populate the DEPTREE_TEMPTAB table? Mark for Review
(1) Points
BEGIN
END;
BEGIN
END;
(*)
BEGIN
END;
BEGIN
END;
9. The IDEPTREE view shows dependencies by indenting the lines of output instead of by using a
NESTED_LEVEL column. True or False? Mark for Review
(1) Points
True (*)
False
Correct Correct
10. A SELECT from DEPTREE produced the following output.
>NESTED_LEVEL >TYPE >NAME
>0 >TABLE >EMPLOYEES
>1 >VIEW >EMP_VW
>2 >PROCEDURE >ADD_EMP
>1 >PROCEDURE >QUERY_EMP
What dependencies does this show? (Choose three.)
Mark for Review
(1) Points
QUERY_EMP is directly dependent on EMPLOYEES (*)
QUERY_EMP is directly dependent on ADD_EMP
EMP_VW is directly dependent on EMPLOYEES (*)

ADD_EMP is directly dependent on EMP_VW (*)
ADD_EMP is directly dependent on EMPLOYEES
Incorrect Incorrect. Refer to Section 14 Lesson 1. 11. A remote dependency is when a dependent
object resides ________________. Mark for Review
(1) Points
In the same object
In a local database
Correct Correct
12. Which statement for setting a database parameter is the default for remote dependency
checking? Mark for Review
(1) Points
ALTER SESSION REMOTE_DEPENDENCIES_MODE = SIGNATURE
ALTER SESSION REMOTE_DEPENDENCIES_MODE = TIMESTAMP
ALTER SESSION SET REMOTE_DEPENDENCIES_MODE = TIMESTAMP (*)
ALTER SESSION SET REMOTE_DEPENDENCIES_MODE = SIGNATURE
(1) Points
True
False (*)
14. Procedure B has a new IN OUT parameter added to it. It compiles successfully. Procedure A is
dependent on Procedure B. In Signature mode,what will happen? Mark for Review
(1) Points
Both procedures will execute successfully.
Procedure A will fail when executed. (*)
Procedure B will fail when executed.
Both procedures will fail.
15. Procedure B has the ZERO_DIVIDE pre-defined exception added to its EXCEPTION section. It is
compiled successfully. In Timestamp Mode, Procedure A, which is dependent on remote Procedure B,
will compile and execute successfully. True or False? Mark for Review
(1) Points
True
False (*)
RETURN NUMBER IS
v_count NUMBER(6);
BEGIN
RETURN v_count;
END;
Mark for Review
(1) Points
FROM user_objects

(*)
SELECT name, type
AND type = 'TABLE';
2. When a table is dropped, all PL/SQL subprograms that reference the table are automatically
dropped. True or False? Mark for Review
(1) Points
True
False (*)
Mark for Review
(1) Points

4. User BOB wants to know which objects reference his DEPARTMENTS table. Which of the
following must he execute to populate the DEPTREE_TEMPTAB table? Mark for Review
(1) Points
BEGIN
END;
BEGIN
END;
BEGIN
END;
BEGIN
END;
(*)
(1) Points
The utldtree table

The deptree table
6. A SELECT from DEPTREE produced the following output.
>NESTED_LEVEL >TYPE >NAME
>0 >TABLE >EMPLOYEES
>1 >VIEW >EMP_VW
>2 >PROCEDURE >ADD_EMP
>1 >PROCEDURE >QUERY_EMP
What dependencies does this show? (Choose three.)
Mark for Review
(1) Points
ADD_EMP is directly dependent on EMPLOYEES
ADD_EMP is directly dependent on EMP_VW (*)
QUERY_EMP is directly dependent on EMPLOYEES (*)

EMP_VW is directly dependent on EMPLOYEES (*)
QUERY_EMP is directly dependent on ADD_EMP
7. The PL/SQL variable V_LAST_NAME is used to store fetched values of the LAST_NAME column
of the EMPLOYEES table. To minimize dependency failures, the variable should be declared as:
True or False?
Mark for Review
(1) Points
True
False (*)

8. Package EMPPACK contains a public procedure GET_EMP, which contains a reference to the
EMPLOYEES table. Procedure CALL_EMP invokes EMPPACK.GET_EMP. The following SQL statement is
executed:
ALTER TABLE employees ADD (gender CHAR(1));
Which one of the following statements is true?
Mark for Review
(1) Points
Nothing is invalidated because the PL/SQL code does not reference the GENDER column.
The specification and body of EMPPACK are invalidated, but CALL_EMP remains valid.
The body of EMPPACK is invalidated, but the specification remains valid. (*)
EMPPACK.GET_EMP is invalidated, but other procedures in EMPPACK remain valid.

Mark for Review
(1) Points

10. A SELECT from the DEPTREE table displays table LOCATIONS at nested level 0 and procedure
LOCPROC at nested level 2. This shows that LOCPROC is directly dependent on LOCATIONS. True or
False? Mark for Review
(1) Points
True
False (*)
1. With remote dependencies, which of the following is True? Mark for Review
(1) Points
The dependency status is not recorded.
There are two separate data dictionaries on two different servers. (*)
There is one master data dictionary that resides on one server which identifies the status of all
schema objects.
(1) Points
Time Stamp Mode
Signature Mode
Time Mode (*)
13. In this scenario, the following status is given for each procedure:
- Procedure A is local and has a time stamp of 10 AM
- Procedure B is remote and has a local time stamp of 5 AM and has a remote time stamp of 4 AM
In Timestamp Mode, Procedure A will execute successfully at 11 AM. True or False?
Mark for Review
(1) Points
True
False (*)
14. Procedure B has its local variable emp_number changed to emp_name. The data type of
emp_id is changed from number to integer. It is compiled successfully. In Signature Mode, Procedure A,
which is dependent on remote Procedure B, will compile and execute successfully. True or False?
Mark for Review
(1) Points
True (*)
False
Correct Correct
(1) Points
True
False (*)
Section 13 Quiz
1. You have created several DML triggers which reference your DEPARTMENTS table. Now you
want to disable all of them using a single SQL statement. Which command should you use? Mark for
Review
(1) Points
ALTER TABLE departments DISABLE TRIGGERS;
ALTER TRIGGER DISABLE ALL ON departments;

ALTER TABLE departments DISABLE ALL TRIGGERS; (*)
DROP ALL TRIGGERS ON departments;
2. Which dictionary view would you query to see the detailed body code of triggers in your
schema? Mark for Review
(1) Points
USER_SOURCE
USER_OBJECTS
USER_TRIGGERS (*)
USER_TRIGGER
None of these; you cannot view the code of the trigger body after the trigger has been created.
3. User KULJIT creates two triggers named EMP1_TRIGG and EMP2_TRIGG, which are both DML
triggers referencing her EMPLOYEES table. Kuljit now wants to remove both of these triggers from the
database. What command(s) should Kuljit use to do this? Mark for Review
(1) Points
DROP ALL TRIGGERS ON employees;
DROP TRIGGERS ON employees;
DROP TRIGGER emp1_trigg AND emp2_trigg;
(*)
4. We want to create a log record automatically every time any DML operation is executed on
either or both of the EMPLOYEES and DEPARTMENTS tables. What is the smallest number of triggers
that must be create to do this? Mark for Review
(1) Points
One
Two (*)
Three
Six
Eight
5. We want to prevent employees from being deleted on Sundays. To do this, we create the
following trigger:
CREATE OR REPLACE TRIGGER stop_del_emps
....... DELETE ON employees -- Line A
BEGIN
IF TO_CHAR(SYSDATE','DY') = 'SUN' THEN
END IF;
END;
Should this be a BEFORE or AFTER trigger, and why?
Mark for Review
(1) Points
It should be an AFTER trigger because the Oracle Server cannot fire the trigger until it knows that the
employee has been deleted.
It should be a BEFORE trigger because if an AFTER trigger were created, the employee would already
have been deleted by the time the trigger checks the date. (*)
It does not matter, either a BEFORE or an AFTER trigger could be created.
It should be a BEFORE trigger because you cannot use RAISE_APPLICATION_ERROR with AFTER
triggers.
BEGIN
...
Mark for Review
(1) Points
(*)

7. What is wrong with this compound trigger example?
CREATE OR REPLACE TRIGGER compound_trigger
FOR UPDATE OF salary
COMPOUND TRIGGER
threshold CONSTANT SIMPLE_INTEGER := 200;
BEFORE EACH ROW IS
BEGIN
-- some action
END BEFORE EACH ROW;
AFTER EACH ROW IS
BEGIN
-- some action
END AFTER EACH ROW;
AFTER STATEMENT IS
BEGIN
-- some action
END compound_trigger;
Mark for Review
(1) Points
Missing the BEFORE and INSTEAD OF timing sections

Missing the EXCEPTION section
Missing the INSTEAD OF timing section
Missing name of table on which the trigger fires (*)
Missing BEFORE timing statement
8. There are 3 employees in department 90 and 5 employees in department 50. The following
trigger has been created:
CREATE TRIGGER upd_emp_trigg
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
...
UPDATE employees
SET department_id = 50
WHERE department_id = 90;

How many times will the trigger fire?
Mark for Review
(1) Points
Once
Three times (*)
Four times
Five times
Eight times
9. A row trigger has been created which is fired by UPDATE ON employees. A user now executes a
single SQL statement which updates four rows of the EMPLOYEES table. How many times will the row
trigger fire? Mark for Review
(1) Points
One time
Two times
Four times (*)
Five times
Eight times
CREATE OR REPLACE TRIGGER call_trigg
AFTER UPDATE OR DELETE ON employees
BEGIN
CALL del_emp_proc
END;
Mark for Review

(1) Points
You cannot use a CALL statement in a DML trigger.
When using CALL, only one DML statement can be tested, so UPDATE OR DELETE is wrong.
When CALL is used, the BEGIN and END; statements should be omitted. (*)
The CALL statement should end with a semicolon (;)
Review
(1) Points
A table is dropped.
12. A trigger automatically inserts a row into a logging table every time a user's session receives
this error message:
ORA-00942: table or view does not exist
What kind of trigger is this? Mark for Review
(1) Points
A row trigger
An AFTER trigger
A DDL trigger
A database event trigger (*)

A statement trigger
13. You can use a trigger to prevent rows from being deleted from the EMPLOYEES table on
Mondays. True or False? Mark for Review
(1) Points
True (*)
False
Correct Correct
14. You can code COMMIT and ROLLBACK statements in a trigger body. True or False? Mark for
Review
(1) Points
True
False (*)
15. Which of the following could NOT be done by a database trigger? Mark for Review
(1) Points
Recalculating the total salary bill for a department whenever an employee's salary is changed
Enforcing a complex business rule
Enforcing a complex database security check
Keeping a log of how many rows have been inserted into a table
Ensuring that a student never arrives late for a class (*)

1. Which of the following are good guidelines to follow when creating a database trigger? (Choose
two.) Mark for Review
(1) Points
Where possible, use a trigger to enforce a foreign key constraint.
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 data.
Use triggers to override privilege checking and view other users' private tables.
Do not create a trigger that automatically fires another trigger. (*)
2. Which of the following best describes a database trigger? Mark for Review
(1) Points
A subprogram that checks whether a user has typed the correct password to log on to the database
A PL/SQL subprogram that inserts rows into a logging table
A PL/SQL subprogram that always returns exactly one value
A PL/SQL subprogram that executes automatically whenever an associated database event occurs (*)
A subprogram that is invoked explicitly by the calling application
3. You can use a database trigger to prevent invalid transactions from being committed. True or
False? Mark for Review
(1) Points
True (*)
False
Correct Correct
4. We want to create a log record automatically every time any DML operation is executed on
either or both of the EMPLOYEES and DEPARTMENTS tables. What is the smallest number of triggers
that must be create to do this? Mark for Review
(1) Points
One
Two (*)
Three
Six
Eight
5. You need to create a trigger that will fire whenever an employee's salary or job_id is updated,
but not when any other column of the EMPLOYEES table is updated. Which of the following is the
correct syntax to do this? Mark for Review
(1) Points
CREATE TRIGGER emp_upd_trigg
AFTER UPDATE ON employees (salary, job_id)
BEGIN ...
AFTER UPDATE OF salary, job_id ON employees
BEGIN ...
(*)
AFTER UPDATE OF (salary, job_id) ON employees
BEGIN ...
AFTER UPDATE OF salary OR job_id ON employees

BEGIN ...
6. There are five employees in department 50. A statement trigger is created by:
CREATE OR REPLACE TRIGGER emp_upd_trigg
AFTER DELETE ON EMPLOYEES
BEGIN ...
DELETE FROM employees WHERE department_id = 50;
How many times will the trigger fire, and when?
Mark for Review
(1) Points
Six times, once after each row and once at the end of the statement
The trigger will not fire at all.
Once, before the DELETE is executed
Once, after the DELETE is executed (*)

Five times, after each employee row is deleted
7. 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?
Mark for Review
(1) Points
True
False (*)

8. Examine the following code. To create a row trigger, what code should be included at Line A?
CREATE OR REPLACE TRIGGER del_emp_trigg
---- Line A
BEGIN ...
Mark for Review
(1) Points
FOR EVERY ROW
FOR EACH ROW (*)
Nothing is needed because DML triggers are row triggers by default.
FOR EVERY ROW
FOR ALL ROWS

9. What is wrong with the following code example for a compound 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 log_emps;
Mark for Review
(1) Points
The order of the timing statements is reversed.
There is nothing wrong with this example.
The triggering event FOR UPDATE is not allowed.

The COMPOUND TRIGGER statement is missing IS.
The declaration section is missing the DECLARE keyword. (*)
10. You have created several DML triggers which reference your DEPARTMENTS table. Now you
want to disable all of them using a single SQL statement. Which command should you use? Mark for
Review
(1) Points
ALTER TRIGGER DISABLE ALL ON departments;
ALTER TABLE departments DISABLE TRIGGERS;
ALTER TABLE departments DISABLE ALL TRIGGERS; (*)
DROP ALL TRIGGERS ON departments;

11. 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?
Mark for Review
(1) Points
None of these.
An error message is displayed because you cannot drop a table that is associated with a trigger.
Both the table and the trigger are dropped. (*)
The table is dropped and the trigger is disabled.
The trigger is dropped but the table is not dropped.

12. You can see trigger information in the following Data Dictionary views except: Mark for
Review
(1) Points
USER_ERRORS
USER_OBJECTS
USER_SOURCE (*)
You can view trigger information in all of these Data Dictionary views.
USER_TRIGGERS
13. What is the purpose of using the CALL statement in a trigger? Mark for Review
(1) Points
It allows both DML events and DDL events to be handled using a single trigger.
It prevents cascading triggers.
It allows an INSTEAD OF trigger to be a statement trigger.
It allows the trigger body code to be placed in a separate trigger.
It allows the trigger body code to be placed in a separate procedure. (*)
14. 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.)

Mark for Review
(1) Points
TABLE
DATABASE (*)
SCHEMA (*)
USER
TABLE employees
15. Which kinds of trigger can cause a mutating table problem? (Choose two.) Mark for Review
(1) Points

INSTEAD OF triggers
AFTER DELETE row triggers (*)
Database Event triggers
BEFORE UPDATE row triggers (*)
DDL triggers

Oracle PLSQL Section 13-15 Answers

Uploaded by

Copyright:

Available Formats

Oracle PLSQL Section 13-15 Answers

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Oracle PLSQL Section 13-15 Answers

Uploaded by

Copyright:

Available Formats

What is the purpose of triggers?

What is the purpose of triggers?

What are the different types of triggers?

What are the different types of triggers?

Test: Section 13 Quiz

Incorrect. Refer to Section 13 Lesson 5.

ALTER TRIGGER emp_trigg DISABLE;

DROP emp_trigg TRIGGER;

DROP TRIGGER emp_trigg; (*)

REMOVE TRIGGER emp_trigg;

Incorrect. Refer to Section 13 Lesson 5.

Incorrect. Refer to Section 13 Lesson 1.

Page 1 of 3, Next, Summary

(Choose all correct answers)

IF NOT myfunc THEN ... (*)

Incorrect Incorrect. Refer to Section 13 Lesson 1.

CREATE OR REPLACE TRIGGER del_emp_trigg

BEFORE DELETE ON employees

Mark for Review

Nothing is needed because DML triggers are row triggers by default.

FOR EVERY ROW

FOR EACH ROW (*)

FOR ALL ROWS

FOR EVERY ROW

8. The following view and trigger have been created:

CREATE VIEW dept_view AS SELECT * FROM departments;

CREATE OR REPLACE TRIGGER dept_view_trigg

INSTEAD OF UPDATE ON dept_view

UPDATE dept_view SET department_name = 'Sales'

WHERE department_id IN (50,80,81);

Mark for Review

No rows are updated and "Sample Message" is displayed three times.

No rows are updated and "Sample Message" is displayed twice. (*)

No rows are updated and "Sample Message" is displayed once.

Incorrect Incorrect. Refer to Section 13 Lesson 3.

Row triggers (*)

Database Event triggers

CREATE OR REPLACE TRIGGER stop_del_emps

....... DELETE ON employees -- Line A

IF TO_CHAR(SYSDATE','DY') = 'SUN' THEN

Should this be a BEFORE or AFTER trigger, and why?

Mark for Review

It does not matter, either a BEFORE or an AFTER trigger could be created.

11. What is wrong with the following code?

CREATE OR REPLACE TRIGGER emp_dept_trigg

BEFORE UPDATE OR DELETE ON employees, departments

Mark for Review

The second line should be:

BEFORE (UPDATE,DELETE) ON employees, departments

DML triggers must be row triggers, so FOR EACH ROW is missing

BEFORE UPDATE OR DELETE ON employees OR departments

One trigger can be associated with only one table

Incorrect Incorrect. Refer to Section 13 Lesson 2.

12. Examine the following code:

CREATE TRIGGER emp_trigg

INSERT INTO log_table VALUES (USER, SYSDATE);

Which of the following can NOT be coded at Line A?

Mark for Review

BEFORE DELETE ON employees