PL/SQL Exception Handling: Best Practices Quiz Quiz

Enhance your understanding of exception handling in PL/SQL with this quiz focusing on best practices, error management techniques, and common pitfalls. Gain insights into proper exception capture, error propagation, and maintaining robust and maintainable PL/SQL code.

1. When to Use the OTHERS Handler

   In PL/SQL exception handling, when should you use the WHEN OTHERS clause within an EXCEPTION block?

   1. Instead of using any named exceptions
   2. Whenever you want to ignore all exceptions
   3. Only when specific exceptions are already handled above it
   4. As the first handler before any specific exception handlers

   Explanation: The WHEN OTHERS clause should be used only after all specific exceptions, as it acts as a catch-all for any unhandled errors. Using it first would prevent named handlers from being reached, making error diagnosis harder. Ignoring all exceptions (second option) is bad practice as it hides issues. Replacing all named exceptions with OTHERS (fourth option) is less precise and not recommended.

2. Raising User-Defined Exceptions

   Which statement correctly raises a user-defined exception called ex_no_sales in a PL/SQL block?

   1. RAISE ex_no_sales;
   2. THROW ex_no_sales;
   3. RAISE_APPLICATION_ERROR(ex_no_sales);
   4. THROW_EXCEPTION ex_no_sales;

   Explanation: The RAISE statement is used to trigger user-defined exceptions like ex_no_sales. There is no THROW statement in PL/SQL (second option), and RAISE_APPLICATION_ERROR requires a numeric code and message, not the exception name (third option). THROW_EXCEPTION (fourth option) is not a valid PL/SQL command.

3. Benefits of Handling Exceptions at the Right Level

   What is a main benefit of handling exceptions at the lowest practical level within PL/SQL subprograms?

   1. It allows for more specific error messages and localized error recovery.
   2. It suppresses all exceptions completely.
   3. It eliminates the need to write exception handlers in outer blocks.
   4. It prevents exceptions from ever propagating.

   Explanation: Handling exceptions at the lowest level enables more detailed and relevant messages and localized recovery actions. Suppressing exceptions (second option) is risky and not a real benefit. Preventing exception propagation entirely (third option) is not always desirable. Eliminating outer handlers (fourth option) can compromise overall robustness.

4. Retrieving Error Information

   Which built-in PL/SQL function is used to obtain the numeric error code of the most recently raised exception inside an exception handler?

   1. ERRCODE
   2. SQLERR
   3. SQLCODE
   4. GET_ERROR_NUMBER

   Explanation: In PL/SQL, SQLCODE returns the numeric code associated with the most recent exception within the exception block. ERRCODE (second option) and GET_ERROR_NUMBER (third option) are not valid PL/SQL built-ins. SQLERR (fourth option) may sound similar but is not an existing PL/SQL keyword.

5. Logging Exceptions

   Which practice is recommended for handling unexpected exceptions in production PL/SQL code?

   1. Terminate the program immediately without logging.
   2. Log exception details and propagate the error upward if necessary.
   3. Silently ignore the exception and continue execution.
   4. Always rollback the transaction, regardless of the context.

   Explanation: Logging details and optionally propagating errors allows for troubleshooting and ensures issues are not hidden. Ignoring exceptions (second option) leads to hard-to-diagnose bugs. Rolling back unconditionally (third option) might cause data loss, while immediate termination without logging (fourth option) hinders error investigation.

6. Catching Built-in Exceptions

   If a SELECT INTO statement fetches no rows, which built-in exception should you handle in your PL/SQL block?

   1. ROW_LOCKED
   2. INVALID_CURSOR
   3. NO_DATA_FOUND
   4. TOO_MANY_ROWS

   Explanation: NO_DATA_FOUND is raised when a SELECT INTO does not fetch any rows. ROW_LOCKED (second option) is not a correct built-in exception for this context. INVALID_CURSOR (third option) relates to improper cursor usage. TOO_MANY_ROWS (fourth option) occurs when SELECT INTO returns more than one row.

7. Scoped Exception Handlers

   What happens if an exception is raised inside a PL/SQL procedure and is not handled within that procedure?

   1. The exception propagates to the calling block or procedure.
   2. A warning is shown but execution continues.
   3. The program exits immediately.
   4. The exception is automatically suppressed.

   Explanation: If a procedure does not handle an exception, it moves up (propagates) to the caller. The program does not exit instantly (second option). Automatic suppression (third option) is not possible unless explicitly coded. Merely showing a warning and continuing (fourth option) is not PL/SQL behavior.

8. WHEN OTHERS without RAISE

   What is a common risk when using WHEN OTHERS without re-raising the exception in a PL/SQL EXCEPTION block?

   1. The original error can be hidden, making debugging difficult.
   2. It automatically logs the error in the database.
   3. It converts the exception into a normal workflow event.
   4. It improves code performance significantly.

   Explanation: Catching all exceptions with WHEN OTHERS and not re-raising or logging can conceal important error details, complicating troubleshooting. Automatic logging (second option) does not occur unless programmed. Performance is unaffected by this pattern (third option). Exceptions are not meant to become standard events (fourth option).

9. Custom Exception Declaration

   Which is the correct syntax for declaring a user-defined exception in a PL/SQL block?

   1. my_exception EXCEPTION;
   2. DECLARE EXCEPTION my_exception;
   3. DEFINE EXCEPTION my_exception;
   4. EXCEPTION my_exception AS;

   Explanation: User-defined exceptions are declared using the syntax 'my_exception EXCEPTION;' in the declaration section. 'DECLARE EXCEPTION' and 'DEFINE EXCEPTION' are not valid styles in PL/SQL. 'EXCEPTION my_exception AS;' is incorrect and not part of PL/SQL syntax.

10. Best Practice for Handling Predictable Errors

    When a specific error condition is likely and anticipated, what is the recommended best practice in PL/SQL?

    1. Avoid writing any exception handling code.
    2. Handle the error explicitly using a named exception.
    3. Let the error propagate and handle it with WHEN OTHERS.
    4. Use a generic message for all exceptions.

    Explanation: For predictable errors, explicit handling with a named exception provides clarity and appropriate responses. Using WHEN OTHERS as a catch-all (second option) is too broad. Ignoring handling (third option) or always using a generic message (fourth option) reduces the quality and robustness of error handling.