Final Exam Sem 2 #1

1. 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?

♦True
♦False (*)

---

2. 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?

♦True
♦False (*)

---

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 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?

♦True (*)
♦False

---

4. Procedure B has a new IN OUT parameter added to it. It compiles successfully. In Signature mode, Procedure A, which is dependent on rem ote Procedure B, will fail. True or False?

♦True (*)
♦False

---

5. Which command changes the dependency mode to SIGNATURE in the current session?

♦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

---

6. 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 t o be recompiled successfully without editing its code? (Choose two.)

♦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; (*)

---

7. The PL/SQL variable V_LAST_NAME is used to store fetche
d 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?

♦True
♦False (*)

---

8. Which of the following will display dependency informat ion which has been generated by executing the DEPTREE_FILL procedure? (Choose two.)

♦The USER_DEPENDENCIES view
♦The DEPTREE view (*)
♦The UTLDTREE script
♦The DISPLAY_DEPTREE view
♦The IDEPTREE view (*)

---

9. Examine the following code:

CREATE FUNCTION deptfunc
RETURN NUMBER IS

v_count NUMBER(6); BEGINSELECT COUNT(*) INTO v_count FROM departments; RETURN v_count; END;
Which of the following will display the dependency between DEPTFUNC and DEPARTME NTS?

♦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’;

---

10. Which of the following will display the number of inval id package bodies in your schema?

♦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’;

---

11. 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?

♦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

---

12. Which of the following is NOT created when the utldtree .sql script is run?

♦The DEPTREE view
♦The DEPTREE_FILL procedure
♦The USER_DEPENDENCIES view (*)
♦The DEPTREE_TEMPTAB table

---

13. Package EMPPACK contains a public procedure GET_EMP, which contains a reference to the EMPLOYEES table. Procedure CALL_EMP invokes EMPP
ACK.GET_EMP. The following SQL statement is executed:
ALTER TABLE employees ADD (gender CHAR(1));

Which one of the following statements is true?

♦The specification and body of EMPPACK are invalidated, but CALL_EMP rema ins 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 GE NDER column.

---

14. What are the two methods for obfuscating PL/SQL subprograms? (Choose two)

♦DBMS_DDL.CREATE_WRAPPED (*)
♦DBMS_DDL.WRAP
♦DBMS_DML.CREATE_WRAPPED
♦PL/SQL wrapper utility program (*)
♦SQL wrapper utility program

---

15. 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.

♦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. (*)

---

16. 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?

♦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);

---

17. In order to use the deterministic functions in Oracle version 11, you can use the following sample code to test for the Oracle version
before compiling that section. True or False?
CREATE OR REPLACE FUNCTION myfunc

RETURN NUMBER $IF DBMS_DB_VERSION.VERSION >= 11 $THEN DETERMINISTIC $END IS BEGIN — body of function END myfunc;

♦True (*)
♦False

---

18. Identify some benefits of using conditional compilation . (Choose two)

♦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 (*)

---

19. How would you determine the current Oracle database version?

♦DBMS_DB_VERSION.RELEASE
♦DBMS_DB_VERSION.VERSION (*)
♦DBMS_DB_VERSION.VER_LE_10
♦DBMS_DB_VERSION.VER_LE_11

---

 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.

♦Error
♦Warning (*)

---

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

♦True (*)
♦False

---

22. 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’;

♦True
♦False (*)

---

23. What is the name of the column used to identify the PLS QL_OPTIMIZE_LEVEL in the data dictionary?

♦OPTIMIZE_LEVEL
♦PLSQL_OPTIMIZE_LEVEL (*)
♦PLSQL_CODE_TYPE
♦PLSQL_LEVEL
♦USER_PLSQL_OPTIMIZE

---

24. Native compilation always runs faster; therefore SQL statements in PL/SQL will always run faster, also. True or False?

♦True
♦False (*)

---

25. When setting PLSQL_OPTIMIZE_LEVEL = 3, the compiled cod e will run more slowly, but it will work with older versions of the Oracle software. True or False?

♦True
♦False (*)