Handling predefined server exceptions of the PL/SQL block for Oracle 19c, utilizing best practices:
DECLARE
v_prod_name VARCHAR2(30);
BEGIN
-- Attempt to retrieve product name for the given product ID
SELECT product_name
INTO v_prod_name
FROM product
WHERE product_id = 24;
EXCEPTION
WHEN NO_DATA_FOUND THEN
-- Log specific error for missing product
INSERT INTO application_error_table (error_type, error_message, error_date)
VALUES ('NO_DATA_FOUND', 'No product exists with Product ID 24', SYSDATE);
WHEN OTHERS THEN
-- Log generic error with detailed error message
INSERT INTO application_error_table (error_type, error_message, error_date)
VALUES ('OTHER_ERROR', 'Error encountered: ' || SQLERRM, SYSDATE);
END;
/
Changes and Improvements for Oracle 19c:**
Error Logging Table:
Updated error logging to use INSERT INTO rather than UPDATE, ensuring a new error log entry is created for every error occurrence.
Includes SYSDATE to log the timestamp of the error.
Use of SQLERRM:
Included in the WHEN OTHERS clause to capture and log the exact error message for better debugging.
Modern Best Practices:
Added comments for clarity and maintainability.
Simplified logic with proper error reporting for modern Oracle environments.
Scalable Logging:
The application_error_table now serves as a centralized error log, designed to accommodate multiple error types with detailed messages.
This modernized version is better suited for Oracle 19c environments, leveraging enhanced readability, maintainability, and proper error-handling mechanisms.
Predefined Server Exceptions
In the previous lesson, you learned about the two kinds of implicitly raised exceptions:
Predefined server exceptions
Non-predefined server exceptions
The Oracle server raises implicit exceptions at runtime and you do not have a choice in this matter.
Oracle dictates the raising of such exceptions. However, you do need to write an error handing routine to handle these exceptions.
Handling predefined server exceptions is the easiest way to handle exceptions. The following diagram best explains how to code them.
EXCEPTION
WHEN cursor_name THEN
statement_1,...,statement_n
WHEN OTHERS THEN
statement_1,...,statement_n
DECLARE
v_prod_name VARCHAR2(30);
BEGIN
SELECT PRODUCT_NAME INTO v_prod_name FROM
PRODUCT WHERE PRODUCT_ID = 24;
EXCEPTION
WHEN NO_DATA_FOUND THEN
UPDATE APPLICATION_ERROR_TABLE
SET ERROR = 'NO_DATA_FOUND: NO PRODUCT
EXISTS WITH PRODUCT ID 24';
WHEN OTHERS THEN
UPDATE APPLICATION_ERROR_TABLE
SET ERROR = 'OTHERS: ERROR ENCOUNTERED.
UNABLE TO CONTINUE PROCESSING';
END;
A predefined server cursor
A sequence of statements: statement_1,...,statement_n
A sequence of statements: statement_11,...,statement_n
SELECT PRODUCT_NAME INTO v_prod_name
FROM PRODUCT WHERE PRODUCT_ID = 24;
This might cause a 'No data found' error if there is no product with a product ID of 24 in the product table
Exception handlers begin here
WHEN NO_DATA_FOUND THEN
Handles the 'No data found' error
WHEN OTHERS THEN
Handles all other errors
Analysis:
This code demonstrates handling exceptions in PL/SQL:
NO_DATA_FOUND:
Catches the scenario where the SELECT INTO statement does not find a matching row.
Logs the error to an APPLICATION_ERROR_TABLE with a specific error message.
OTHERS:
A generic handler for all other exceptions not explicitly caught.
Logs a generic error message.
Error Logging:
Error messages are logged in a table (APPLICATION_ERROR_TABLE) instead of displaying them, which is a good practice for persistent error tracking.
Improvement Areas:
The WHEN OTHERS handler should ideally include SQLERRM for more detailed error reporting.
Transactions with UPDATE should include COMMIT or ROLLBACK to ensure consistent data handling.
In the next lesson, you will learn about implicit user-defined exceptions.
