PLSQL Semester 2 Mid Term Exam

Section 14
     
  1.  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
 
  2.  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 (*)
   
 3.  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
 
 4.  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

5.  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 (*)
 
6.  When a table is dropped, all PL/SQL subprograms that reference the table are automatically dropped. True or False?  (1) Points
     
    True
    False (*)
 
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:
v_last_name VARCHAR2(25);
True or False?  (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?  (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.
 
9.  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
 
 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?  (1) Points
     
    True
    False (*)
 
 Section 14
     
 11.  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; (*)
 
12.  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 (*)
 
13.  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';

  Section 15
     
 14.  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.
 
  15.  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 (*)
 
16.  Obfuscation does not allow anyone to see the source code, including the owner. True or False?  (1) Points
     
    True (*)
    False
 
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 (*)
 
18.  To include selections of code for compilation based on user-defined values, use the PLSQL_CCFLAGS parameters. True or False?  (1) Points
     
    True (*)
    False
 
19.  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;
 (1) Points
     
    True (*)
    False

20.  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
 
Section 15
     
21.  Native compilation always runs faster; therefore SQL statements in PL/SQL will always run faster, also. True or False?  (1) Points
     
    True
    False (*)

22.  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 (*)
 
23.  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. (*)
 
24.  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
 
25.  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 (*)