SQLCODE and SQLERRM in Oracle
Managing program execution failures is essential for creating reliable applications in Oracle’s PL/SQL. This procedure, called exception handling, keeps applications from ending suddenly by enabling developers to respond to problems graciously. RAISE statement, including RAISE_APPLICATION_ERROR procedure, for manually generating exceptions and SQLCODE and SQLERRM for getting error information are two essential tools for handling exceptions.
Error Information
Oracle has procedures built in to detect and investigate errors (or exceptions) that occur in PL/SQL blocks. The two main tools for this are SQLERRM and SQLCODE.
SQLCODE (Oracle error number)
The most recent exception raised in your PL/SQL block’s numeric error code is returned by the SQLCODE function. When SQLCODE is called outside of an exception handler or when no problem has occurred, it returns 0. Because Oracle keeps track of SQLCODE values, if an outer procedure raises an exception and an inner procedure raises one as well, SQLCODE will accurately identify the error code of the exception within its current scope. This variable offers a precise identification of the error that happened, making it an important tool for problem diagnosis. An example of a ZERO_DIVIDE exception is ORA-01476.
SQLERRM (Oracle error message)
In addition to SQLCODE, the SQLERRM function provides the textual error message linked to a particular error code. SQLERRM receives the message for the error code that SQLCODE is currently returning if no error code is supplied as an input. When SQLCODE = 1 (which is frequently used for generic user-defined exceptions), it returns ‘User-Defined Exception’; when SQLCODE = 0, it returns ‘ORA-0000: normal, successful completion’.
A disadvantage of SQLERRM is that, although it is very helpful, it usually provides a string that is no more than 512 bytes long (or 255 bytes in previous Oracle versions). With a maximum character count of 1,899, Oracle advises using DBMS_UTILITY.FORMAT_ERROR_STACK for a thorough error message, particularly in complicated cases. Furthermore, the Oracle Database 10g-introduced DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function offers a formatted string that displays the execution call stack, complete with line numbers, tracing the error’s first raise. This is essential for troubleshooting and comprehending the execution flow that caused the mistake.
RAISE Statement
During program execution, developers can explicitly trigger a specified exception using PL/SQL’s RAISE statement. Enforcing application-specific business rules or reacting to unforeseen circumstances that Oracle might not automatically identify as errors require this.
Three ways to employ the RAISE statement are as follows:
- RAISE exception_name;: This form is used to raise a specific, named exception. The
exception_namecan be either a programmer-defined exception (declared usingEXCEPTIONin theDECLAREsection) or a predefined system exception (such asNO_DATA_FOUNDorZERO_DIVIDE, which are part of theSTANDARDpackage). - RAISE package_name.exception_name;: An exception must be qualified with the package name when it is raised outside of a package if it is declared inside of one (and that package is not
STANDARD). - RAISE;: This form can only be used in a
WHENclause, which is anEXCEPTIONsection. The current exception is raised again and propagated to the PL/SQL block that surrounds it. If you want to log the problem in the current block but still want the calling block to know about the initial exception and possibly handle it further, this is helpful.
The built-in procedure RAISE_APPLICATION_ERROR in Oracle allows you to raise application-specific problems and offer the user with a personalised message. You can attach an error number (which needs to be between -20000 and -20999) and a custom error message (up to 2,000 characters) with the exception using this process, which is defined in the DBMS_STANDARD package. For user-defined faults, the range -20000 to -20999 is especially set apart.
Any changes made to the OUT or IN OUT parameters are reversed, and the current PL/SQL block’s execution instantly ceases when RAISE_APPLICATION_ERROR is called. However, if you want to roll back any Data Manipulation Language (DML) changes made during the transaction (like INSERT, UPDATE, DELETE), you must explicitly use the ROLLBACK command. Significant flexibility in handling and informing the user of error scenarios is provided by this process.
Example: Stock Management with Exception Handling
Let’s illustrate these concepts with a stock management scenario. We’ll create a PRODUCT_STOCK table and then use PL/SQL to attempt to deduct stock, demonstrating both a successful operation and an exception due to insufficient stock, showcasing SQLCODE, SQLERRM, and RAISE_APPLICATION_ERROR.
First, we create the PRODUCT_STOCK table and insert an initial value:
-- 1. Create the PRODUCT_STOCK table
CREATE TABLE product_stock (
product_id NUMBER PRIMARY KEY,
product_name VARCHAR2(50),
stock_level NUMBER
);
Table created.
-- 2. Insert initial data
INSERT INTO product_stock VALUES (1, 'Laptop', 5);
1 row created.
COMMIT;
Commit complete.
-- Enable output to see DBMS_OUTPUT messages
SET SERVEROUTPUT ON;Now, let’s demonstrate a successful stock deduction:
-- 3. PL/SQL block for a successful stock deduction
DECLARE
v_product_id NUMBER := 1;
v_deduct_qty NUMBER := 2;
v_current_stock NUMBER;
BEGIN
-- Retrieve current stock level
SELECT stock_level INTO v_current_stock FROM product_stock WHERE product_id = v_product_id;
-- Check if deduction is possible
IF v_current_stock - v_deduct_qty < 0 THEN
-- This path will not be taken in this successful scenario
RAISE_APPLICATION_ERROR(-20001, 'Error: Unexpected insufficient stock condition.');
ELSE
-- Perform the update
UPDATE product_stock SET stock_level = stock_level - v_deduct_qty WHERE product_id = v_product_id;
DBMS_OUTPUT.PUT_LINE('Product ' || v_product_id || ' stock updated successfully. New level: ' || (v_current_stock - v_deduct_qty));
END IF;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An unexpected error occurred during successful update:');
DBMS_OUTPUT.PUT_LINE('Oracle Error Number: ' || SQLCODE);
DBMS_OUTPUT.PUT_LINE('Oracle Error Message: ' || SQLERRM);
ROLLBACK; -- Rollback in case of any unexpected error
END;
/-- Output for successful deduction:
-- Product 1 stock updated successfully. New level: 3
-- PL/SQL procedure successfully completed.
-- Verify the updated stock level
SELECT * FROM product_stock;
-- Output:
-- PRODUCT_ID PRODUCT_NAME STOCK_LEVEL
-- ---------- ------------ -----------
-- 1 Laptop 3Next, we’ll demonstrate an insufficient stock scenario where an exception is manually raised and caught:
-- 4. PL/SQL block for insufficient stock (triggers exception)
DECLARE
v_product_id NUMBER := 1;
v_deduct_qty NUMBER := 10; -- Attempt to deduct more than available
v_current_stock NUMBER;
e_insufficient_stock EXCEPTION; -- Declare a programmer-defined exception
BEGIN
-- Retrieve current stock level
SELECT stock_level INTO v_current_stock FROM product_stock WHERE product_id = v_product_id;
-- Business logic to check for sufficient stock
IF v_current_stock - v_deduct_qty < 0 THEN
RAISE e_insufficient_stock; -- Manually raise the programmer-defined exception using RAISE statement
ELSE
-- This path will not be taken in this error scenario
UPDATE product_stock SET stock_level = stock_level - v_deduct_qty WHERE product_id = v_product_id;
DBMS_OUTPUT.PUT_LINE('Product ' || v_product_id || ' stock updated successfully.');
END IF;
COMMIT;
EXCEPTION
-- Handle the specific programmer-defined exception
WHEN e_insufficient_stock THEN
DBMS_OUTPUT.PUT_LINE('Caught user-defined exception: Insufficient stock!');
-- Use RAISE_APPLICATION_ERROR to provide a user-friendly message with a custom error number
RAISE_APPLICATION_ERROR(-20002, 'ERROR: Insufficient stock for product ' || v_product_id ||
'. Available: ' || v_current_stock || ', Requested: ' || v_deduct_qty);
-- Handle any other unexpected exceptions
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An unexpected database error occurred.');
DBMS_OUTPUT.PUT_LINE('Oracle Error Number: ' || SQLCODE); -- Display Oracle error number
DBMS_OUTPUT.PUT_LINE('Oracle Error Message: ' || SQLERRM); -- Display Oracle error message
ROLLBACK; -- Rollback any pending changes in case of an unexpected error
END;
/-- Output for insufficient stock:
-- Caught user-defined exception: Insufficient stock!
-- DECLARE
-- *
-- ERROR at line 1:
-- ORA-20002: ERROR: Insufficient stock for product 1. Available: 3, Requested: 10
-- ORA-06512: at line 20
-- Verify that the stock level has not changed due to the error and rollback
SELECT * FROM product_stock;
-- Output:
-- PRODUCT_ID PRODUCT_NAME STOCK_LEVEL
-- ---------- ------------ -----------
-- 1 Laptop 3In this example, when attempting to deduct 10 units from a stock of 3, the IF condition triggers RAISE e_insufficient_stock;. This immediately transfers control to the EXCEPTION section, where the WHEN e_insufficient_stock handler is executed. Inside this handler, RAISE_APPLICATION_ERROR is called with a custom error number (-20002) and a descriptive message, which is then displayed to the user. If any other unexpected error were to occur, the WHEN OTHERS handler would catch it, displaying the generic SQLCODE and SQLERRM information, and then explicitly performing a ROLLBACK. This structured approach ensures that application errors are handled gracefully and provides meaningful feedback to the user and for debugging purposes.