oracle section 7

 Test: PL/SQL Section 7 Quiz

Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 7

(Answer all questions in this section)

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

Mark for Review

1. All of these. (*)
2. WHEN TOO_MANY_ROWS in either block
3. WHEN OTHERS in either block
4. WHEN OTHERS in the inner block
5. WHEN TOO_MANY_ROWS in the inner block

2. What will be displayed when the following code is executed?

<< outer>>

DECLARE

    v_myvar NUMBER;

BEGIN

    v_myvar := 10;

    DECLARE

       v_myvar NUMBER := 200;

    BEGIN

       outer.v_myvar := 20;

       v_myvar := v_myvar / 0; -- this raises a ZERO_DIVIDE error

       outer.v_myvar := 30;

    END;

    v_myvar := 40;

EXCEPTION

    WHEN ZERO_DIVIDE THEN

       DBMS_OUTPUT.PUT_LINE(v_myvar);

END;

1. 40
2. 30
3. 20 (*)
4. 10
5. 200

3. Which of the following are NOT good practice guidelines for exception handling? (Choose two.)

1. Include a WHEN OTHERS handler as the first handler in the exception section.
2. (*)
3. Use an exception handler whenever there is any possibility of an error occurring.
4. 
   
5. Handle specific named exceptions where possible, instead of relying on WHEN OTHERS.
6. 
   
7. Test your code with different combinations of data to see what potential errors can happen.
8. 
   
9. Allow exceptions to propagate back to the calling environment.
10. (*)

4. While a PL/SQL block is executing, more than one exception can occur at the same time. True or False?

1. TRUE
2. FALSE (*)

5. Which of the following best describes a PL/SQL exception

1. The programmer makes a spelling mistake while writiing the PL/SQL code.
2. An error occurs during execution which disrupts the normal operation of the program. (*)
3. A DML statement does not modify any rows.
4. A user enters an invalid password while trying to log on to the database.

6. Which of the following is NOT an advantage of including an exception handler in a PL/SQL

1. Protects the database from errors
2. Code is more readable because error-handling routines can be written in the same block in which the error occurred
3. Prevents errors from occurring (*)
4. Avoids costly and time-consuming correction of mistakes

7. An attempt to insert a null value into a NOT NULL table column raises an ORA-01400 exception. How can you code an exception handler to trap this exception?

1.Test for WHEN ORA-1400 in the exception section.

2.Declare a variable e_null_excep of type VARCHAR2, associate it with ORA-01400 using a PRAGMA directive, and test for WHEN e_null_excep in the exception section.

3.Declare a variable e_null_excep of type EXCEPTION, associate it with ORA-01400 using a PRAGMA directive, and test for WHEN e_null_excep in the exception section. (*)

4.Declare a variable as follows: e_null_excep EXCEPTION := -01400; Then test for WHEN e_null_excep in the exception section.

8. 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;

..

1. A constraint violation
2. A NO_DATA_FOUND exception
3. A user-defined exception (*)
4. A predefined Oracle Server exception
5. A non-predefined Oracle server exception

9. Which type of exception MUST be explicitly raised by the PL/SQL programmer?

1. All of these.
2. User-defined exceptions (*)
3. Non-predefined Oracle server errors such as ORA-01203
4. Predefined Oracle server errors such as TOO_MANY_ROW

10. How would you trap Oracle Server exception ORA-01403: no data found?

1. WHEN NO DATA FOUND THEN ...
2. WHEN NO_DATA_FOUND THEN ... (*)
3. WHEN SQL%ROWCOUNT=0 THEN ...
4. WHEN ORA-01403 THEN ...

11. How can you retrieve the error code and error message of any Oracle Server exception?

1. By defining an EXCEPTION variable and using PRAGMA EXCEPTION_INIT
2. By using the functions SQLCODE and SQLERRM (*)
3. By using the functions SQLCODE and SQLERR
4. By using RAISE_APPLICATION_ERRORR

12. The following line of code is correct. True or False?

RAISE_APPLICATION_ERROR(-21001,'My error message');

1. True
2. False (*)

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

DECLARE

    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;

1. Department 99 is empty (*)
2. No employees found Department 99 is empty
3. The block will fail because you cannot explicitly RAISE a predefined Oracle Server error such as NO_DATA_FOUND
4. No employees found

14. You want to display your own error message to the user. What is the correct syntax to do this

1. RAISE_APPLICATION_ERROR('My own message', -20001);
2. RAISE application_error;
3. RAISE_APPLICATION_ERROR(20001, 'My own message');
4. RAISE_APPLICATION_ERROR (-20001, 'My own message'); (*)

15. What is a user-defined exception?

1. A predefined Oracle server exception such as NO_DATA_FOUND.
2. An exception handler which the user (the programmer) includes in the EXCEPTION section.
3. An exception which has a predefined Oracle error number but no predefined name.
4. An exception which is not raised automatically by the Oracle server, but must be declared and raised explicitly by the PL/SQL programmer. (*)