Test: Semester 2 Final Exam

Review your answers, feedback, and question scores below. An asterisk

(*) indicates a correct answer.

Section 14

1. 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? (1)
Points

True

False (*)


Correct


2. Examine the following code:
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?
(1) Points

SELECT name, type
FROM user_dependencies
WHERE name IN ('DEPTFUNC','DEPARTMENTS');


SELECT name, type, referenced_name, referenced_type
FROM user_dependencies
WHERE referenced_name = 'DEPARTMENTS'
AND referenced_type = 'TABLE';
(*)


SELECT name, type, referenced_name, referenced_type
FROM user_dependencies
WHERE name = 'DEPARTMENTS'
AND type = 'TABLE';


SELECT object_name, object_type
FROM user_objects
WHERE object_name IN ('DEPARTMENTS','DEPTFUNC')
AND referenced = 'YES';



Correct


3. Which of the following is NOT created when the utldtree.sql
script is run? (1) Points

The DEPTREE view

The DEPTREE_FILL procedure

The USER_DEPENDENCIES view (*)

The DEPTREE_TEMPTAB table


Correct


4. When a table is dropped, all PL/SQL subprograms that reference
the table are automatically dropped. True or False? (1) Points

True

False (*)


Correct


5. Which of the following will display dependency information which
has been generated by executing the DEPTREE_FILL procedure? (Choose
two.) (1) Points

(Choose all correct answers)

The USER_DEPENDENCIES view

The DEPTREE view (*)

The UTLDTREE script

The DISPLAY_DEPTREE view

The IDEPTREE view (*)


Correct


6. Examine the following code:
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.)
(1) Points

(Choose all correct answers)

ED_PROC is indirectly dependent on DEPARTMENTS (*)

EMPLOYEES is referenced by ED_VIEW (*)

ED_CURS is directly dependent on ED_VIEW

ED_PROC is referenced by ED_VIEW

ED_PROC is directly dependent on EMPLOYEES


Correct


7. 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.)
(1) Points

(Choose all correct answers)

RENAME locations TO new_locations;

ALTER TABLE locations ADD (climate VARCHAR2(30)); (*)

ALTER TABLE locations DROP COLUMN city;

ALTER TABLE locations DROP COLUMN postal_code; (*)


Correct


8. 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?
(1) Points

When the command ALTER FUNCTION fetch_emp COMPILE; is executed

The next time a user session invokes FETCH_EMP (*)

When the RESUME column is dropped from the EMPLOYEES table

When FETCH_EMP is dropped and recreated


Correct


9. When a subprogram is compiled/recompiled, a time stamp is
automatically recorded in the local data dictionary. Switching the
dependency mode to SIGNATURE means that only the signature value is
recorded. True or False? (1) Points

True

False (*)


Correct


10. 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? (1) Points

True

False (*)


Incorrect. Refer to Section 14 Lesson 2.



Page 1 of 3
Test: Semester 2 Final Exam



Review your answers, feedback, and question scores below. An asterisk
(*) indicates a correct answer.

Section 14

11. Procedure B has a new IN OUT parameter added to it. It compiles
successfully. In Signature mode, Procedure A, which is dependent on
remote Procedure B, will fail. True or False? (1) Points

True (*)

False


Incorrect. Refer to Section 14 Lesson 2.


12. A change in a remote referenced subprogram is automatically
recorded as invalid if its base object changes and that new status is
relayed to the dependent object's status and automatically marked as
invalid. True or False? (1) Points

True

False (*)


Incorrect. Refer to Section 14 Lesson 2.


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 and remote time stamp of 10:30
AM

In Timestamp Mode, Procedure A, which is dependent on Procedure B,
will execute successfully at 11 AM. True or False?

(1) Points

True (*)

False


Incorrect. Refer to Section 14 Lesson 2.




Section 15

14. Native machine code PL/SQL will always execute faster than
bytecode PL/SQL because it need not be interpreted at run time. True
or False? (1) Points

True (*)

False


Correct


15. Native compilation always runs faster; therefore SQL statements
in PL/SQL will always run faster, also. True or False? (1) Points

True

False (*)


Incorrect. Refer to Section 15 Lesson 1.


16. To determine the current setting for PLSQL_OPTIMIZE_LEVEL,
query the data dictionary view USER_PLSQL_OBJECTS_SETTING. True or
False? (1) Points

True

False (*)


Incorrect. Refer to Section 15 Lesson 1.


17. Conditional compilation allows you to determine what code will
be compiled based on select criteria you specify using inquiry
directives. True or False? (1) Points

True

False (*)


Incorrect. Refer to Section 15 Lesson 3.


18. 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';
(1) Points

$ENDIF

$$END;

$END (*)

$ELSIF

$END;


Incorrect. Refer to Section 15 Lesson 3.


19. To include selections of code for compilation based on user-
defined values, use the PLSQL_CCFLAGS parameters. True or False? (1)
Points

True (*)

False


Incorrect. Refer to Section 15 Lesson 3.


20. 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.
(1) Points

Error

Warning (*)


Correct



Page 2 of 3
Test: Semester 2 Final Exam



Review your answers, feedback, and question scores below. An asterisk
(*) indicates a correct answer.

Section 15

21. The two statements below are equivalent. True or False?
DBMS_WARNING.SET_WARNING_SETTING_STRING
('ENABLE:SEVERE','SESSION');

and

ALTER SESSION
SET PLSQL_WARNINGS = 'ENABLE:SEVERE';

(1) Points

True

False (*)


Correct


22. The informational warning level of the PL/SQL compiler
identifies poor coding practices, for example, code that can never be
executed. True or False? (1) Points

True (*)

False


Correct


23. When wrapping subprograms, the entire PL/SQL code must be
included as an IN argument with data type CLOB to allow for any size
program. True or False? (1) Points

True

False (*)


Incorrect. Refer to Section 15 Lesson 4.


24. 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? (1) Points

DBMS_DML.CREATE_WRAPPED (pkg1);

WRAP pkg1.sql

DBMS_DML.CREATE_WRAPPED ('CREATE OR REPLACE PACKAGE BODY pkg1...);

WRAP INAME=pkg1.sql (*)

DBMS_DML.CREATE_WRAP (pkg1);


Incorrect. Refer to Section 15 Lesson 4.


25. Which is NOT a benefit of obfuscation? (1) Points

Source code is not loaded in the data dictionary.

Source code is hidden from all users.

Source code is visible to the owner. (*)

Protection for intellectual property is provided.


Incorrect. Refer to Section 15 Lesson 4.



Page 3 of 3

Test: Semester 2 Final Exam



Review your answers, feedback, and question scores below. An asterisk
(*) indicates a correct answer.

Section 14

1. 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? (1) Points

True

False (*)


Correct


2. Which command changes the dependency mode to SIGNATURE in the
current session? (1) Points

ALTER SESSION MAKE REMOTE_DEPENDENCIES_MODE = SIGNATURE

ALTER SYSTEM MAKE REMOTE_DEPENDENCIES_MODE = SIGNATURE

ALTER SESSION SET REMOTE_DEPENDENCIES_MODE = SIGNATURE (*)

ALTER SYSTEM SET REMOTE_DEPENDENCIES_MODE = SIGNATURE


Incorrect. Refer to Section 14 Lesson 2.


3. When a subprogram is compiled/recompiled, a time stamp is
automatically recorded in the local data dictionary. Switching the
dependency mode to SIGNATURE means that only the signature value is
recorded. True or False? (1) Points

True

False (*)


Correct


4. 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? (1) Points

True

False (*)


Incorrect. Refer to Section 14 Lesson 2.


5. Procedure B has a new IN OUT parameter added to it. It compiles
successfully. In Signature mode, Procedure A, which is dependent on
remote Procedure B, will fail. True or False? (1) Points

True (*)

False


Correct


6. Examine the following code:
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?
(1) Points

SELECT name, type
FROM user_dependencies
WHERE name IN ('DEPTFUNC','DEPARTMENTS');


SELECT name, type, referenced_name, referenced_type
FROM user_dependencies
WHERE referenced_name = 'DEPARTMENTS'
AND referenced_type = 'TABLE';
(*)


SELECT name, type, referenced_name, referenced_type
FROM user_dependencies
WHERE name = 'DEPARTMENTS'
AND type = 'TABLE';


SELECT object_name, object_type
FROM user_objects
WHERE object_name IN ('DEPARTMENTS','DEPTFUNC')
AND referenced = 'YES';



Correct


7. 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?
(1) Points

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.

Nothing is invalidated because the PL/SQL code does not reference
the GENDER column.


Correct


8. Which of the following techniques will make it more likely that
an invalidated PL/SQL subprogram will recompile successfully? (Choose
two.) (1) Points

(Choose all correct answers)

Declaring record structures using %ROWTYPE (*)

Using a cursor FOR loop instead of opening and closing the cursor
explicitly

SELECTing a list of column-names instead of using SELECT *

Including a column list with INSERT statements (*)


Correct


9. User BOB wants to know which objects reference his DEPARTMENTS
table. Which of the following must he execute to populate the
DEPTREE_TEMPTAB table? (1) Points

BEGIN
utldtree('DEPARTMENTS');
END;


BEGIN
deptree_fill('TABLE','BOB','DEPARTMENTS');
END;
(*)


BEGIN
deptree_fill('TABLE','DEPARTMENTS');
END;


BEGIN
ideptree('TABLE','BOB','DEPARTMENTS');
END;



Correct


10. 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?
(1) Points

When the command ALTER FUNCTION fetch_emp COMPILE; is executed

The next time a user session invokes FETCH_EMP (*)

When the RESUME column is dropped from the EMPLOYEES table

When FETCH_EMP is dropped and recreated


Correct



Page 1 of 3

Test: Semester 2 Final Exam



Review your answers, feedback, and question scores below. An asterisk
(*) indicates a correct answer.

Section 14

11. Which of the following will display the number of invalid
package bodies in your schema? (1) Points

SELECT COUNT(*) FROM user_objects
WHERE object_type = 'PACKAGE BODY'
AND status = 'INVALID';
(*)


SELECT COUNT(*) FROM user_dependencies
WHERE type = 'PACKAGE BODY'
AND status = 'INVALID';


SELECT COUNT(*) FROM user_packages
WHERE status = 'INVALID';


SELECT COUNT(*) FROM user_objects
WHERE object_type LIKE 'PACKAGE%'
AND status = 'INVALID';



Correct


12. 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? (1)
Points

True

False (*)


Correct


13. 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?
(1) Points

True

False (*)


Correct




Section 15

14. How would you determine the current Oracle database version?
(1) Points

DBMS_DB_VERSION.RELEASE

DBMS_DB_VERSION.VERSION (*)

DBMS_DB_VERSION.VER_LE_10

DBMS_DB_VERSION.VER_LE_11


Correct


15. To include selections of code for compilation based on user-
defined values, use the PLSQL_CCFLAGS parameters. True or False? (1)
Points

True (*)

False


Correct


16. 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';
(1) Points

$ENDIF

$$END;

$END (*)

$ELSIF

$END;


Correct


17. To determine the current setting for PLSQL_OPTIMIZE_LEVEL,
query the data dictionary view USER_PLSQL_OBJECTS_SETTING. True or
False? (1) Points

True

False (*)


Correct


18. Identify examples of benefits of using PLSQL_OPTIMIZE_LEVEL.
(Choose three) (1) Points

(Choose all correct answers)

Modify source code to optimize frequently-used elements at the
top.

Control what PL/SQL does with useless code. (*)

Backward compatible with previous versions of the Oracle database.
(*)

Separating compiled code so that separate units may be repeated as
needed.

Copy compiled code from one subprogram into another subprogram.
(*)


Incorrect. Refer to Section 15 Lesson 1.


19. Native machine code PL/SQL will always execute faster than
bytecode PL/SQL because it need not be interpreted at run time. True
or False? (1) Points

True (*)

False


Correct


20. 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.
(1) Points

Error

Warning (*)


Correct



Page 2 of 3

Test: Semester 2 Final Exam



Review your answers, feedback, and question scores below. An asterisk
(*) indicates a correct answer.

Section 15

21. What does the following statement do?
DBMS_WARNING.ADD_WARNING_SETTING_CAT('PERFORMANCE','ENABLE','SESSION')
;
(1) Points

Enables the PERFORMANCE warning category, setting other category
settings to disabled.

Enables the PERFORMANCE warning category, leaving other category
settings unchanged.

Add the PERFORMANCE warning category into a PL/SQL variable.

Disables all warning categories, then enables the PERFORMANCE
category.

Enables the PERFORMANCE warning category, leaving other category
settings unchanged, for the current session. (*)


Incorrect. Refer to Section 15 Lesson 2.


22. 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? (1) Points

True (*)

False


Incorrect. Refer to Section 15 Lesson 2.


23. 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? (1) Points

DBMS_DML.CREATE_WRAPPED (pkg1);

WRAP pkg1.sql

DBMS_DML.CREATE_WRAPPED ('CREATE OR REPLACE PACKAGE BODY pkg1...);

WRAP INAME=pkg1.sql (*)

DBMS_DML.CREATE_WRAP (pkg1);


Correct


24. 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.
(1) Points

Validate the wrapped code at the operating system level.

Create the wrapper using DBMS_DML.

Verify the code was hidden in USER_CODE.

Grant EXECUTE privileges on the subprogram.

Log into the database server computer. (*)


Incorrect. Refer to Section 15 Lesson 4.


25. Which is NOT a benefit of obfuscation? (1) Points

Source code is not loaded in the data dictionary.

Source code is hidden from all users.

Source code is visible to the owner. (*)

Protection for intellectual property is provided.


Correct



Page 3 of 3