Section 7 Quiz



1. Which kinds of exceptions are raised implicitly (i.e., automatically)? (Choose two.)
     (Choose all correct answers)   
  •  Non-predefined Oracle Server errors such as ORA-01400 (*)
  •  All errors
  •  User-defined errors
  •  Predefined Oracle Server errors such as NO_DATA_FOUND (*)


2. Which of the following is NOT a predefined Oracle Server error?      Mark for Review
  • ZERO_DIVIDE
  •  DUP_VAL_ON_INDEX
  •  e_sal_too_high EXCEPTION; (*)
  •  TOO_MANY_ROWS
  •  NO_DATA_FOUND


3. 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;
  •  A NO_DATA_FOUND exception
  •   A constraint violation
  •   A predefined Oracle Server exception
  •  A user-defined exception (*)
  •  A non-predefined Oracle server exception                 


4. Examine the following code fragment. At Line A, you want to raise an exception if the fetched salary value is greater than 30000. How can you do this?

 DECLARE
    v_salary employees.salary%TYPE;
BEGIN
    SELECT salary INTO v_salary FROM employees
       WHERE employee_id = 100;
    IF v_salary > 30000 THEN
       -- Line A
    END IF;
  •  Use RAISE_APPLICATION_ERROR to raise an exception explicitly. (*)
  •  Test for WHEN VALUE_TOO_HIGH in the exception section
  •  Define an EXCEPTION variable and associate it with an Oracle Server error number using PRAGMA EXCEPTION_INIT.
  •  Test for WHEN OTHERS in the exception section, because WHEN OTHERS traps all exceptions.


5. Which of the following are examples of predefined Oracle Server errors? (Choose three.)
  •  NO_DATA_FOUND (*)
  •  TOO_MANY_ROWS (*)
  •  E_INSERT_EXCEP
  •  ZERO_DIVIDE (*)
  • OTHERS

                
6. No employees are in department_id 99. What output will be displayed when the following code is executed?

     v_count NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_count
       FROM employees WHERE department_id = 99;
    IF v_count = 0 THEN
       RAISE NO_DATA_FOUND;
       DBMS_OUTPUT.PUT_LINE('No employees found');
    END IF;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
       DBMS_OUTPUT.PUT_LINE('Department 99 is empty');
END;
  • The block will fail because you cannot explicitly RAISE a predefined Oracle Server error such as NO_DATA_FOUND
  •  No employees found
  •  No employees found Department 99 is empty
  •  Department 99 is empty (*)
                         

7. Which of the following will successfully return a user-defined error message?
  • RAISE_APPLICATION_ERROR(-29001,'Error Raised');
  •  RAISE_APPLICATION_ERROR(-20257,'Error raised'); (*)
  •   RAISE_APPLICATION_ERROR('Error Raised',-20257);
  •  RAISE_APPLICATION_ERROR('Error Raised',-22001);
   

8. No employees are in department 99. What message or messages will be displayed when the following code is executed?

DECLARE
    e_my_excep EXCEPTION;
BEGIN
    BEGIN
       UPDATE employees
       SET salary = 10000
          WHERE department_id = 99;
       IF SQL%ROWCOUNT = 0 THEN
          RAISE e_my_excep;
       END IF;
    EXCEPTION
       WHEN e_my_excep THEN
          DBMS_OUTPUT.PUT_LINE('Message 1');
          RAISE e_my_excep;
          DBMS_OUTPUT.PUT_LINE('Message 2');
    END;
    DBMS_OUTPUT.PUT_LINE('Message 3');
EXCEPTION
    WHEN e_my_excep THEN
       DBMS_OUTPUT.PUT_LINE('Message 4');
END;     

  • Message 1
    Message 4 (*)
  •  Message 1
    Message 3
    Message 4 
  •  Message 1
    Message 3 
  •  Message 1
    Message 2
              
    
9. Department-id 99 does not exist. What will be displayed when the following code is executed?

DECLARE
    v_deptname departments.department_name%TYPE;
BEGIN
    SELECT department_name INTO v_deptname
       FROM departments WHERE department_id = 99;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
       RAISE_APPLICATION_ERROR(-20201,'Department does not exist');
END;  

  • ORA-01403: No Data Found ORA-20201: Department does not exist
  • ORA-01403: No Data Found                
  • None of these.
  • ORA-20201: Department does not exist (*)


10. Exceptions declared in a block are considered local to that block, and global to all its sub-blocks. True or False?
  •  True (*)
  • False


11. Using two nested blocks, a TOO_MANY_ROWS exception is raised within the inner block. Which of the following exception handlers will successfully handle the exception?
  •  WHEN TOO_MANY_ROWS in either block
  •  WHEN OTHERS in either block
  •   All of these. (*)   
  •  WHEN TOO_MANY_ROWS in the inner block
  •  WHEN OTHERS in the inner block
  

12. Which of the following is NOT an advantage of including an exception handler in a PL/SQL block?
  •  Protects the database from errors
  •  Code is more readable because error-handling routines can be written in the same block in which the error occurred
  •  Avoids costly and time-consuming correction of mistakes
  •  Prevents errors from occurring (*)
       


13. Which of the following EXCEPTION sections is constructed correctly? (Choose three.)

  • EXCEPTION
    WHEN OTHERS THEN statement_1;
    WHEN NO_DATA_FOUND THEN statement_2;
END;  
              
  • EXCEPTION
    WHEN NO_DATA_FOUND THEN statement_1;
    WHEN NO_DATA_FOUND THEN statement_2;
    WHEN OTHERS THEN statement_3;
END;     
              
  • EXCEPTION
    WHEN TOO_MANY_ROWS THEN statement_1;
END;
(*)
                   
  • EXCEPTION
    WHEN OTHERS THEN statement_1;
END;
(*)
  • EXCEPTION
    WHEN NO_DATA_FOUND THEN statement_1;
    WHEN OTHERS THEN statement_2;
END;
(*)


14. 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;    
  • True (*)
  • False
   

15. The following EXCEPTION section is constructed correctly. True or False?

EXCEPTION
    WHEN NO_DATA_FOUND OR TOO_MANY_ROWS
       THEN statement_1;
       statement_2;
       WHEN OTHERS
          THEN statement_3;
END;     
                        
              
  • TRUE (*)
  • FALSE