Functions and Procedures for Error Handling in PL/SQL

In Oracle 23ai, exception handling is still built around the same PL/SQL fundamentals: you trap exceptions in an EXCEPTION section, use functions such as SQLCODE and SQLERRM to capture diagnostic details, and use procedures (most commonly RAISE_APPLICATION_ERROR) to return meaningful errors to the caller.

The key best practice is to treat error handling as a design feature. Your PL/SQL blocks should: 1) detect expected business-rule errors early, 2) raise clear application errors for those conditions, and 3) record enough diagnostics for unexpected failures without exposing internal details to end users.

Functions used inside exception handlers

Example: capture error code and message

The block below shows a modern, readable pattern: capture the error details, roll back the unit of work, and print (or log) the diagnostics. In a production application, you would typically log diagnostics to a table (or a centralized logging system) rather than relying on DBMS_OUTPUT.

DECLARE
  v_error_code    PLS_INTEGER;
  v_error_message VARCHAR2(4000);
BEGIN
  DELETE FROM product
  WHERE product_id = 22;

  COMMIT;

EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;

    v_error_code    := SQLCODE;
    v_error_message := SQLERRM;

    DBMS_OUTPUT.PUT_LINE('SQLCODE: ' || v_error_code);
    DBMS_OUTPUT.PUT_LINE('SQLERRM : ' || v_error_message);

    -- In a real application, consider re-raising or returning a controlled error.
END;

Procedures used for controlled error reporting

The most important procedure for application-grade error handling is RAISE_APPLICATION_ERROR. It lets you raise a custom error number and message that your calling layer (APEX, JDBC, OCI, ORDS, etc.) can reliably interpret.

Putting it together: a robust PL/SQL block with expected and unexpected errors

Example: validate input, compute totals, and handle exceptions

DECLARE
  -- Inputs (in real apps these come from parameters)
  l_product_id  product.product_id%TYPE := 101;

  -- Data
  l_base_price  product.base_price%TYPE;
  l_total_price NUMBER;

  -- Business-rule exception
  e_invalid_price EXCEPTION;
BEGIN
  -- Look up price (NO_DATA_FOUND will be raised automatically if missing)
  SELECT base_price
  INTO   l_base_price
  FROM   product
  WHERE  product_id = l_product_id;

  -- Business rule
  IF l_base_price <= 0 THEN
    RAISE e_invalid_price;
  END IF;

  -- Example computation (replace with your own business logic)
  l_total_price := l_base_price
                   + (l_base_price * 0.05)     -- tax
                   + (l_base_price * 0.001);   -- shipping/handling (0.10%)

  DBMS_OUTPUT.PUT_LINE('Total price: ' || TO_CHAR(l_total_price));

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    -- Controlled, user-facing error
    RAISE_APPLICATION_ERROR(-20001, 'Product ID does not exist.');

  WHEN e_invalid_price THEN
    RAISE_APPLICATION_ERROR(-20002, 'Product price is invalid (must be greater than zero).');

  WHEN OTHERS THEN
    -- Capture diagnostics for troubleshooting (log in real systems)
    DBMS_OUTPUT.PUT_LINE('SQLCODE: ' || SQLCODE);
    DBMS_OUTPUT.PUT_LINE('SQLERRM : ' || SQLERRM);

    -- Return a controlled error to the application layer
    RAISE_APPLICATION_ERROR(-20099, 'Unexpected error while processing the order.');
END;

Operational guidance for Oracle 23ai

• Use WHEN OTHERS deliberately. It is appropriate as a safety net, but do not hide errors. Either re-raise the original error, or raise a controlled error after logging diagnostics.
• Prefer application errors for business rules. If the caller needs to react (retry, prompt user, choose another ID), raise a clear -20000 series error.
• Log diagnostic detail separately from user messaging. End users should see a safe, stable message; DBAs and developers need the real SQLCODE/SQLERRM and contextual information in logs.
• Keep transactions consistent. If the block performs DML, decide whether the block owns the transaction boundary. If so, commit on success and rollback on failure. If not, avoid commits and let the caller control the transaction.

The next lesson concludes this module.