Final Exam Sem 2 #2

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

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

---

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

---

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

♦True
♦False (*)

---

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

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

---

5.  Which of the following will display dependency information 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 (*)

---

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:
v_last_name VARCHAR2(25);

True or False?

♦True
♦False (*)

---

7.  User BOB wants to know which objects reference his DEPARTMENTS table. Which of the following must he execute to populate the DEPTREE_TEMPTAB table?

♦BEGIN
utldtree(‘DEPARTMENTS’);
END;

♦BEGIN
deptree_fill(‘TABLE’,’BOB’,’DEPARTMENTS’);
END;
(*)

♦BEGIN
deptree_fill(‘TABLE’,’DEPARTMENTS’);
END;

♦BEGIN
ideptree(‘TABLE’,’BOB’,’DEPARTMENTS’);
END;

---

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?

♦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

---

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

♦True
♦False (*)

---

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

---

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

---

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

♦True (*)
♦False

---

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

♦True (*)
♦False

---

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

---

15.  Select the three categories of PL/SQL compiler warning levels.

♦ENABLE
♦DISABLE
♦SEVERE (*)
♦INFORMATIONAL (*)
♦PERFORMANCE (*)

---

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

---

17.  Which is NOT a benefit of obfuscation?

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

---

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

♦True
♦False (*)

---

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

---

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

---

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

---

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

♦$ENDIF
♦$$END;
♦$END (*)
♦$ELSIF
♦$END;

---

23.  Identify examples of benefits of using PLSQL_OPTIMIZE_LEVEL. (Choose three)

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

---

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

♦True
♦False (*)

---

25.  To determine the current setting for PLSQL_OPTIMIZE_LEVEL, query the data dictionary view USER_PLSQL_OBJECTS_SETTING. True or False?

♦True
♦False (*)