1.  Which of the following is NOT an advantage of including an exception handler in a PL/SQL block?  Mark for Review 
(1) Points 
      
    Prevents errors from occurring (*) 
  
    Code is more readable because error-handling routines can be written in the same block in which the error occurred 
  
    Prevents errors from being propagated back to the calling environment 
  
    Avoids costly and time-consuming correction of mistakes 
  
      
      Correct  
 
2.  Which of the following best describes a PL/SQL exception?  Mark for Review 
(1) Points 
      
    A user enters an invalid password while trying to log on to the database. 
  
    An error occurs during the execution of the block, which disrupts the normal operation of the program. (*) 
  
    A compile-time error occurs because the PL/SQL code references a non-existent table. 
  
    The programmer forgets to declare a cursor while writing the PL/SQL code. 
  
      
      Correct  
 
3.  Which of these exceptions can be handled by an EXCEPTION section in a PL/SQL block?  Mark for Review 
(1) Points 
      
    An attempt is made to divide by zero 
  
    A SELECT statement returns no rows 
  
    Any other kind of exception that can occur within the block 
  
    All of the above (*) 
  
    None of the above 
  
      
      Correct  
  
     
4.  Only one exception can be raised automatically during one execution of a PL/SQL block. True or False?  Mark for Review 
(1) Points 
      
    True (*) 
  
    False 
  
      
      Incorrect. Refer to Section 6.  
 
5.  Which of the following EXCEPTION sections are constructed correctly? (Choose three.)  Mark for Review 
(1) Points 
      
   (Choose all correct answers)  
      
    EXCEPTION 
    WHEN NO_DATA_FOUND THEN statement_1; 
    WHEN OTHERS THEN statement_2; 
END; 
(*)
 
  
    EXCEPTION 
    WHEN TOO_MANY_ROWS THEN statement_1; 
END; 
(*)
 
  
    EXCEPTION 
    WHEN NO_DATA_FOUND THEN statement_1; 
    WHEN NO_DATA_FOUND THEN statement_2; 
    WHEN OTHERS THEN statement_3; 
END; 
 
  
    EXCEPTION 
    WHEN OTHERS THEN statement_1; 
END; 
(*)
 
  
    EXCEPTION 
    WHEN OTHERS THEN statement_1; 
    WHEN NO_DATA_FOUND THEN statement_2; 
END; 
 
  
      
      Correct  
 
6.  The following EXCEPTION section is constructed correctly. True or False? 
EXCEPTION 
    WHEN ZERO_DIVIDE OR TOO_MANY_ROWS OR NO_DATA_FOUND 
       THEN statement_1; 
          statement_2; 
             WHEN OTHERS 
       THEN statement_3; 
END;

 Mark for Review 
(1) Points 
      
    True (*) 
  
    False 
  
      
      Correct  
  
     
7.  Which of the following are NOT good practice guidelines for exception handling? (Choose two.)  Mark for Review 
(1) Points 
      
   (Choose all correct answers)  
      
    Test your code with different combinations of data to see what potential errors can happen. 
  
    Use an exception handler whenever there is any possibility of an error occurring. 
  
    Include a WHEN OTHERS handler as the first handler in the exception section. (*) 
  
    Allow exceptions to propagate back to the calling environment. (*) 
  
    Handle specific named exceptions where possible, instead of relying on WHEN OTHERS. 
  
      
      Correct  
 
8.  Examine the following code. Why does this exception handler not follow good practice guidelines? (Choose two.) 
DECLARE 
    v_dept_name departments.department_name%TYPE; 
BEGIN 
    SELECT department_name INTO v_dept_name FROM departments 
       WHERE department_id = 75; 
EXCEPTION 
    WHEN OTHERS THEN 
       DBMS_OUTPUT.PUT_LINE('A select returned more than one row'); 
END; 

 Mark for Review 
(1) Points 
      
   (Choose all correct answers)  
      
    You should not use DBMS_OUTPUT.PUT_LINE in an exception handler. 
  
    department_id 75 does not exist in the departments table. 
  
    The exception handler should test for the named exception NO_DATA_FOUND. (*) 
  
    The exception handler should COMMIT the transaction. 
  
    The exception section should include a WHEN TOO_MANY_ROWS exception handler. (*) 
  
      
      Correct  
 
1.  Which of the following is NOT a predefined Oracle Server error?  Mark for Review 
(1) Points 
      
    NO_DATA_FOUND 
  
    TOO_MANY_ROWS 
  
    e_sal_too_high EXCEPTION; (*) 
  
    ZERO_DIVIDE 
  
    DUP_VAL_ON_INDEX 
  
      
      Correct  
 
 2.  Which of the following best describes a predefined Oracle Server error?  Mark for Review 
(1) Points 
      
    Has a standard Oracle error number but must be declared and named by the PL/SQL programmer 
  
    Has a standard Oracle error number and a standard name which can be referenced in the EXCEPTION section (*) 
  
    Is associated with an Oracle error number using PRAGMA EXCEPTION_INIT 
  
    Is not raised automatically but must be declared and raised explicitly by the PL/SQL programmer 
  
      
      Correct 
 
3.  Which kind of error can NOT be handled by PL/SQL?  Mark for Review 
(1) Points 
      
    Syntax errors (*) 
  
    Predefined Oracle Server errors 
  
    Non-predefined Oracle Server errors 
  
    User-defined errors 
  
      
      Correct  
 
 4.  Examine the following code. At Line A, you want to raise an exception if the employee's manager_id is null. What kind of exception is this? 
DECLARE 
    v_mgr_id employees.manager_id%TYPE; 
BEGIN 
    SELECT manager_id INTO v_mgr_id FROM employees 
       WHERE employee_id = 100; 
    IF v_mgr_id IS NULL THEN 
       -- Line A 
    END IF; 
... 
 Mark for Review 
(1) Points 
      
    A predefined Oracle Server exception 
  
    A constraint violation 
  
    A non-predefined Oracle server exception 
  
    A user-defined exception (*) 
  
    A NO_DATA_FOUND exception 
  
      
      Correct  
 
.  How would you trap Oracle Server exception ORA-01403: no data found?  Mark for Review 
(1) Points 
      
    WHEN NO DATA FOUND THEN ... 
  
    WHEN ORA-01403 THEN ... 
  
    WHEN NO_DATA_FOUND THEN ... (*) 
  
    WHEN SQL%ROWCOUNT=0 THEN ... 
  
      
      Correct  
 
 6.  There are no employees whose salary is less than 2000. Which exception handlers would successfully trap the exception which will be raised when the following code is executed? (Choose two.) 
DECLARE 
    v_mynum NUMBER := 10; 
    v_count NUMBER; 
BEGIN 
    SELECT COUNT(*) INTO v_count FROM employees 
       WHERE salary < 2000; 
    v_mynum := v_mynum / v_count; 
EXCEPTION ... 
END; 

 Mark for Review 
(1) Points 
      
   (Choose all correct answers)  
      
    NO_DATA_FOUND 
  
    ZERO_DIVIDE (*) 
  
    SQL%ROWCOUNT = 0 
  
    OTHERS (*) 
  
    OTHER 
  
      
      Correct  
 
7.  What is the correct syntax to associate an exception named EXCEPNAME with the non-predefined Oracle Server error ORA-02292?  Mark for Review 
(1) Points 
      
    PRAGMA EXCEPTION_INIT (newname, -2292) (*) 
  
    RAISE_APPLICATION_ERROR (-2292, excepname); 
  
    SQLCODE (-2292, excepname); 
  
    WHEN (-2292, excepname) THEN ? 
  
      
      Incorrect. Refer to Section 6.  
 
8.  An ORA-1400 exception is raised if an attempt is made to insert a null value into a NOT NULL column. DEPARTMENT_ID is the primary key of the DEPARTMENTS table. What will happen when the following code is executed? 
DECLARE 
    e_not_null EXCEPTION; 
BEGIN 
    PRAGMA EXCEPTION_INIT(e_not_null, -1400); 
    INSERT INTO departments (department_id, department_name) 
       VALUES(null, 'Marketing'); 
EXCEPTION 
    WHEN e_not_null THEN 
       DBMS_OUTPUT.PUT_LINE('Cannot be null'); 
END; 

 Mark for Review 
(1) Points 
      
    The exception will be raised and "Cannot be null" will be displayed. 
  
    The code will not execute because the syntax of PRAGMA EXCEPTION_INIT is wrong. 
  
    The code will not execute because PRAGMA EXCEPTION_INIT must be coded in the DECLARE section. (*) 
  
    The code will not execute because the syntax of the INSERT statement is wrong. 
  
      
      Correct  
 
 9.  Examine the following code. The UPDATE statement will raise an ORA-02291 exception. 
BEGIN 
UPDATE employees SET department_id = 45; 
EXCEPTION 
WHEN OTHERS THEN 
INSERT INTO error_log_table VALUES (SQLCODE); 
END; 

What will happen when this code is executed? 

 Mark for Review 
(1) Points 
      
    The code will execute and insert error number 02291 into error_log_table. 
  
    The code will fail because SQLCODE has not been declared. 
  
    The code will fail because we access error message numbers by using SQLERRNUM, not SQLCODE. 
  
    The code will fail because we cannot use functions like SQLCODE directly in a SQL statement. (*) 
  
      
      Correct  
 
10.  Which type(s) of exception MUST be explicitly raised by the PL/SQL programmer?  Mark for Review 
(1) Points 
      
    User-defined exceptions. (*) 
  
    Predefined Oracle server errors such as TOO_MANY_ROWS. 
  
    Non-predefined Oracle server errors such as ORA-01203. 
  
    All of the above. 
  
      
      Correct  
 
11.  A PL/SQL block executes and an Oracle Server exception is raised. Which of the following contains the text message associated with the exception?  Mark for Review 
(1) Points 
      
    SQLCODE 
  
    SQLERRM (*) 
  
    SQL%MESSAGE 
  
    SQL_MESSAGE_TEXT 
  
      
      Correct  
 
 12.  Which one of the following events would implicitly raise an exception?  Mark for Review 
(1) Points 
      
    The PL/SQL programmer mis-spells the word BEGIN as BEGAN. 
  
    A database constraint is violated. (*) 1.  What is a user-defined exception?  Mark for Review 
(1) Points 
      
    A predefined Oracle server exception such as NO_DATA_FOUND. 
  
    An exception which has a predefined Oracle error number but no predefined name. 
  
    An exception handler which the user (the programmer) includes in the EXCEPTION section. 
  
    An exception which is not raised automatically by the Oracle server, but must be declared and raised explicitly by the PL/SQL programmer. (*) 
  
      
      Correct  
 
2.  What is the datatype of a user-defined exception?  Mark for Review 
(1) Points 
      
    BOOLEAN 
  
    VARCHAR2 
  
    EXCEPTION (*) 
  
    NUMBER 
  
    None of the above 
  
      
      Correct  
 
 3.  What is wrong with the following code? 
BEGIN 
    UPDATE employees SET salary = 20000 
       WHERE job_id = 'CLERK'; 
    IF SQL%ROWCOUNT = 0 THEN 
       RAISE NO_DATA_FOUND; -- Line A 
    END IF; 
EXCEPTION 
    WHEN NO_DATA_FOUND THEN 
       DBMS_OUTPUT.PUT_LINE('No employee was updated'); 
END; 

 Mark for Review 
(1) Points 
      
    You cannot use SQL%ROWCOUNT in conditional control statements such as IF or CASE. 
  
    NO_DATA_FOUND has not been DECLAREd 
  
    Line A should be: HANDLE NO_DATA_FOUND 
  
    You cannot explicitly raise predefined Oracle Server errors such as NO_DATA_FOUND. 
  
    Nothing is wrong, the code will execute correctly. (*) 
  
      
      Correct  
  
     
4.  What will be displayed when the following code is executed? 
DECLARE 
    e_myexcep EXCEPTION; 
BEGIN 
    DBMS_OUTPUT.PUT_LINE('Message 1'); 
    RAISE e_myexcep; 
    DBMS_OUTPUT.PUT_LINE('Message 2'); 
EXCEPTION 
    WHEN e_myexcep THEN 
       DBMS_OUTPUT.PUT_LINE('Message 3'); 
       RAISE e_myexcep; 
       DBMS_OUTPUT.PUT_LINE('Message 4'); 
END; 

 Mark for Review 
(1) Points 
      
    Message 1 
Message 3 
 
  
    Message 1 
Message 3 
Message 4 
 
  
    Message 1 
Message 2 
Message 3 
Message 4 
 
  
    The code will not execute because it contains at least one syntax error. 
 
  
    The code will execute but will return an unhandled exception to the calling environment. 
(*)
 
  
      
      Incorrect. Refer to Section 6.  
 
5.  The following line of code is correct. True or False? 
RAISE_APPLICATION_ERROR(-21001,'My error message');  Mark for Review 
(1) Points 
      
    True 
  
    False (*) 
  
      
      Correct  
 
6.  How are user-defined exceptions raised ?  Mark for Review 
(1) Points 
      
    By PRAGMA EXCEPTION_INIT 
  
    By DECLARE e_my_excep EXCEPTION; 
  
    By RAISE exception_name; (*) 
  
    None of the above. They are raised automatically by the Oracle server. 
  
      
      Correct  
  
     
7.  The following three steps must be performed to use a user-defined exception: 
- Raise the exception 
- Handle the exception 
- Declare the exception 
In what sequence must these steps be performed? 
 Mark for Review 
(1) Points 
      
    Raise, Handle, Declare 
  
    Handle, Raise, Declare 
  
    Declare, Raise, Handle (*) 
  
    The steps can be performed in any order. 
  
      
      Correct  
 
8.  You want to display your own error message to the user. What is the correct syntax to do this?  Mark for Review 
(1) Points 
      
    RAISE_APPLICATION_ERROR(20001, 'My own message'); 
  
    RAISE_APPLICATION_ERROR('My own message', -20001); 
  
    RAISE application_error; 
  
    RAISE_APPLICATION_ERROR (-20001, 'My own message') (*) 
  
      
      Incorrect. Refer to Section 6.  
 

  
    A SELECT statement returns exactly one row. 
  
    An UPDATE statement modifies no rows. 
  
      
      Correct  
 
1.  Three blocks (outer, middle and inner) are nested inside each other. A variable is declared in the middle block. What is the scope of this variable?  Mark for Review 
(1) Points 
      
    Middle block only 
  
    Middle and inner blocks (*) 
  
    All three blocks 
  
    Middle and outer block only 
  
    None of the above 
  
      
      Incorrect. Refer to Section 6.  
 
2.  Examine the following code. What is the scope and visibility of the outer block's v_myvar? 
DECLARE 
    v_myvar VARCHAR2(20); 
BEGIN 
    DECLARE 
       v_myvar VARCHAR2(20); 
    BEGIN 
       ... 
    END: 
    ... 
END; 

 Mark for Review 
(1) Points 
      
    It is in scope and visible in the outer block only. 
  
    It is in scope and visible in both blocks. 
  
    It is visible in both blocks but in scope only in the outer block. 
  
    It is in scope in both blocks but visible only in the outer block. (*) 
  
      
      Incorrect. Refer to Section 6.  
 
.  Explicit cursors are variables and follow the same scoping and visibility rules as other types of variable. True or False?  Mark for Review 
(1) Points 
      
    True (*) 
  
    False 
  
      
      Correct  
 
4.  Why will the following code not execute correctly? 
DECLARE 
    v_var1 NUMBER; 
BEGIN 
    DECLARE 
       v_var2 NUMBER; 
    BEGIN 
       v_var1 := '20'; 
    END; 
    v_var2 := 30; 
END; 

 Mark for Review 
(1) Points 
      
    v_var1 cannot be referenced in the inner block. 
  
    v_var1 is a NUMBER variable and cannot be assigned a character string value. 
  
    v_var2 cannot be referenced in the outer block. (*) 
  
    The block labels are missing. 
  
    Nothing is wrong, the code will execute correctly 
  
      
      Correct  
 
5.  In a pair of nested blocks, block labels such as <<label_name>> must be used for both blocks or neither block. True or False?  Mark for Review 
(1) Points 
      
    True 
  
    False (*) 
  
      
      Correct  
 
6.  Which of the following will display the value 'Smith'?  Mark for Review 
(1) Points 
      
    <<outer>> 
DECLARE 
    v_name VARCHAR2(10) := 'Smith'; 
BEGIN 
    DECLARE 
       v_name VARCHAR2(10) := 'Jones'; 
    BEGIN 
       DBMS_OUTPUT.PUT_LINE(v_name); 
    END; 
END; 
 
  
    <<outer>> 
DECLARE 
    v_name VARCHAR2(10) := 'Smith'; 
BEGIN 
    DECLARE 
       v_name VARCHAR2(10) := 'Jones'; 
    BEGIN 
       DBMS_OUTPUT.PUT_LINE(<<outer>>.v_name); 
    END; 
END; 
 
  
    <<outer>> 
DECLARE 
    v_name VARCHAR2(10) := 'Smith'; 
BEGIN 
    DECLARE 
       v_name VARCHAR2(10) := 'Jones'; 
    BEGIN 
       DBMS_OUTPUT.PUT_LINE(outer.v_name); 
    END; 
END; 
(*)
 
  
    <<outer>> 
DECLARE 
    v_name VARCHAR2(10) := 'Smith'; 
BEGIN 
    <<inner>> 
    DECLARE 
       v_name VARCHAR2(10) := 'Jones'; 
    BEGIN 
       DBMS_OUTPUT.PUT_LINE(v_name); 
    END; 
END; 
 
  
      
      Correct  
 
7.  Predefined Oracle Server exceptions such as NO_DATA_FOUND can be raised automatically in inner blocks and handled in outer blocks. True or False?  Mark for Review 
(1) Points 
      
    True (*) 
  
    False 
  
      
      Correct  
 
8.  Non-predefined Oracle Server errors (associated with Oracle error numbers by PRAGMA EXCEPTION_INIT) can be declared and raised in inner blocks and handled in outer blocks. True or False?  Mark for Review 
(1) Points 
      
    True 
  
    False (*) 
  
      
      Incorrect. Refer to Section 6.  
 
9.  What will happen when the following code is executed? 
DECLARE 
    e_outer_excep EXCEPTION; 
BEGIN 
    DECLARE 
       e_inner_excep EXCEPTION; 
    BEGIN 
       RAISE e_outer_excep; 
    END; 
EXCEPTION 
    WHEN e_outer_excep THEN 
       DBMS_OUTPUT.PUT_LINE('Outer raised'); 
    WHEN e_inner_excep THEN 
       DBMS_OUTPUT.PUT_LINE('Inner raised'); 
END; 
 Mark for Review 
(1) Points 
      
    The code will execute successfully and 'Outer Raised' will be displayed. 
  
    The code will propagate the e_outer_excep back to the calling environment (Application Express). 
  
    The code will fail to compile because e_inner_excep cannot be referenced in the outer block. (*) 
  
    The code will fail to compile because e_inner_excep was declared but never RAISEd. 
  
      
      Correct  
 
 10.  What will happen when the following code is executed? 
DECLARE 
    e_excep1 EXCEPTION; 
    e_excep2 EXCEPTION; 
BEGIN 
    RAISE e_excep1; 
EXCEPTION 
    WHEN e_excep1 THEN BEGIN 
       RAISE e_excep2; END; 
END; 

 Mark for Review 
(1) Points 
      
    It will fail to compile because you cannot have a subblock inside an exception section. 
  
    It will fail to compile because e_excep1 is out of scope in the subblock. 
  
    It will fail to compile because you cannot declare more than one exception in the same block. 
  
    It will compile successfully and return an unhandled e_excep2 to the calling environment. (*) 
  
      
      Correct  
 
11.  There are three employees in department 90. What will be displayed when this code is executed? 
DECLARE 
    v_last_name employees.last_name%TYPE; 
BEGIN 
    DBMS_OUTPUT.PUT_LINE('Message 1'); 
    BEGIN 
       SELECT last_name INTO v_last_name 
          FROM employees WHERE department_id = 90; 
       DBMS_OUTPUT.PUT_LINE('Message 2'); 
    END; 
    DBMS_OUTPUT.PUT_LINE('Message 3'); 
EXCEPTION 
    WHEN OTHERS THEN 
       DBMS_OUTPUT.PUT_LINE('Message 4'); 
END; 

 Mark for Review 
(1) Points 
      
    Message 1 
Message 3 
Message 4 
 
  
    Message 1 
Message 4 
(*)
 
  
    Message 1 
 
  
    An unhandled exception will be propagated back to the calling environment. 
 
  
    None of the above 
 
  
      
      Correct  
 
12.  What will be displayed when the following code is executed? 
<<outer>> 
DECLARE 
    v_myvar NUMBER; 
BEGIN 
    v_myvar := 25; 
    DECLARE 
       v_myvar NUMBER := 100; 
    BEGIN 
       outer.v_myvar := 30; 
       v_myvar := v_myvar / 0; 
       outer.v_myvar := 35; 
    END; 
    v_myvar := 40; 
EXCEPTION 
    WHEN ZERO_DIVIDE THEN 
       DBMS_OUTPUT.PUT_LINE(v_myvar); 
END; 

 Mark for Review 
(1) Points 
      
    25 
  
    30 (*) 
  
    35 
  
    40 
  
    100 
  
      
      Correct  
 
