Download - 32270975 Final Exam 2 Plsql
8/6/2019 32270975 Final Exam 2 Plsql
http://slidepdf.com/reader/full/32270975-final-exam-2-plsql 1/10
1. A change in a remote referenced subprogram is automatically recorded asinvalid 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.
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 s
uccessfully. True or False? (1) Points
True
False (*)
Correct
3. In this scenario, the following status is given for eachprocedure:- 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 executesuccessfully at 11 AM. True or False?(1) Points
True (*)
False
Correct
4. Procedure B has a new IN OUT parameter added to it. Itcompiles successfully. In Signature mode, Procedure A, which is dependent on remote Procedure B, will fail. True or False? (1) Points
True (*)
8/6/2019 32270975 Final Exam 2 Plsql
http://slidepdf.com/reader/full/32270975-final-exam-2-plsql 2/10
False
Correct
5. Which command changes the dependency mode to SIGNATUREin 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.
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 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; (*)
Incorrect. Refer to Section 14 Lesson 1.
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 dependencyfailures, the variable should be declared as:
v_last_name VARCHAR2(25);
True or False?
8/6/2019 32270975 Final Exam 2 Plsql
http://slidepdf.com/reader/full/32270975-final-exam-2-plsql 3/10
(1) Points
True
False (*)
Incorrect. Refer to Section 14 Lesson 1.
8. 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 (*)
Incorrect. Refer to Section 14 Lesson 1.
9. Examine the following code:CREATE FUNCTION deptfuncRETURN 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, typeFROM user_dependencies
WHERE name IN ('DEPTFUNC','DEPARTMENTS');
8/6/2019 32270975 Final Exam 2 Plsql
http://slidepdf.com/reader/full/32270975-final-exam-2-plsql 4/10
SELECT name, type, referenced_name, referenced_typeFROM user_dependenciesWHERE referenced_name = 'DEPARTMENTS'AND referenced_type = 'TABLE';(*)
SELECT name, type, referenced_name, referenced_typeFROM user_dependenciesWHERE name = 'DEPARTMENTS'AND type = 'TABLE';
SELECT object_name, object_typeFROM user_objectsWHERE object_name IN ('DEPARTMENTS','DEPTFUNC')AND referenced = 'YES';
Incorrect. Refer to Section 14 Lesson 1.
10. Which of the following will display the number of invalid package bodies in your schema? (1) Points
SELECT COUNT(*) FROM user_objectsWHERE object_type = 'PACKAGE BODY'AND status = 'INVALID';(*)
SELECT COUNT(*) FROM user_dependenciesWHERE type = 'PACKAGE BODY'AND status = 'INVALID';
SELECT COUNT(*) FROM user_packagesWHERE status = 'INVALID';
SELECT COUNT(*) FROM user_objectsWHERE object_type LIKE 'PACKAGE%'AND status = 'INVALID';
Incorrect. Refer to Section 14 Lesson 1.
Section 14
11. Function FETCH_EMP references the EMPLOYEES table. Thetable is modified by:ALTER TABLE employees ADD (resume CLOB);
When will the ORACLE server try to recompile FETCH_EMP automatically?
(1) Points
8/6/2019 32270975 Final Exam 2 Plsql
http://slidepdf.com/reader/full/32270975-final-exam-2-plsql 5/10
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
12. 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
Incorrect. Refer to Section 14 Lesson 1.
13. 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 v
alid.
8/6/2019 32270975 Final Exam 2 Plsql
http://slidepdf.com/reader/full/32270975-final-exam-2-plsql 6/10
Nothing is invalidated because the PL/SQL code does not reference the GENDER column.
Correct
Section 15
14. What are the two methods for obfuscating PL/SQL subprograms? (Choose two) (1) Points
(Choose all correct answers)
DBMS_DDL.CREATE_WRAPPED (*)
DBMS_DDL.WRAP
DBMS_DML.CREATE_WRAPPED
PL/SQL wrapper utility program (*)
SQL wrapper utility program
Incorrect. Refer to Section 15 Lesson 4.
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.
(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. (*)
8/6/2019 32270975 Final Exam 2 Plsql
http://slidepdf.com/reader/full/32270975-final-exam-2-plsql 7/10
Incorrect. Refer to Section 15 Lesson 4.
16. You created a package named pkg1. The code is approxima
tely 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.
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 myfuncRETURN NUMBER$IF DBMS_DB_VERSION.VERSION >= 11 $THEN
DETERMINISTIC$END
IS BEGIN-- body of function
END myfunc;
(1) Points
True (*)
False
Incorrect. Refer to Section 15 Lesson 3.
18. Identify some benefits of using conditional compilation. (Choose two) (1) Points
(Choose all correct answers)
8/6/2019 32270975 Final Exam 2 Plsql
http://slidepdf.com/reader/full/32270975-final-exam-2-plsql 8/10
Use new features with the latest database release and disable them witholder 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(*)
Incorrect. Refer to Section 15 Lesson 3.
19. 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
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 (*)
Incorrect. Refer to Section 15 Lesson 2.
Section 15
21. The informational warning level of the PL/SQL compileridentifies poor coding practices, for example, code that can never be executed.True or False? (1) Points
8/6/2019 32270975 Final Exam 2 Plsql
http://slidepdf.com/reader/full/32270975-final-exam-2-plsql 9/10
True (*)
False
Correct
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';(1) Points
True
False (*)
Correct
23. What is the name of the column used to identify the PLSQL_OPTIMIZE_LEVEL in the data dictionary? (1) Points
OPTIMIZE_LEVEL
PLSQL_OPTIMIZE_LEVEL (*)
PLSQL_CODE_TYPE
PLSQL_LEVEL
USER_PLSQL_OPTIMIZE
Correct
24. Native compilation always runs faster; therefore SQL st
atements in PL/SQL will always run faster, also. True or False? (1) Points
8/6/2019 32270975 Final Exam 2 Plsql
http://slidepdf.com/reader/full/32270975-final-exam-2-plsql 10/10
True
False (*)
Incorrect. Refer to Section 15 Lesson 1.
25. 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? (1) Points
True
False (*)
Incorrect. Refer to Section 15 Lesson 1.