Learn the purpose and how to unravel the ORA-06510 error message in Oracle.
Description
When you encounter an ORA-06510 error, the following error message will appear:
ORA-06510: unhandled user-defined exception
Cause
You tried to execute a block of code that raised a user-defined exception, but there was no exception block code to cope with this exception.
Resolution
The option(s) to resolve this Oracle error are:
Option #1
Remove the user-defined exception from the code.
Option #2
Add exception code to cope with the user-defined exception.
For example, if you had tried to execute the following procedure:
CREATE OR REPLACE PROCEDURE add_new_order
(order_id_in IN NUMBER, sales_in IN NUMBER)
IS
no_sales EXCEPTION;
BEGIN
IF sales_in = 0 THEN
RAISE no_sales;
ELSE
INSERT INTO orders (order_id, total_sales )
VALUES ( order_id_in, sales_in );
END IF;
END;You would receive the following error message:
This error is triggered because you raised an exception called no_sales, however you did not encompass code in the EXCEPTION block to handle the no_sales exception.
You may want to correct this by using editing the process as follows:
CREATE OR REPLACE PROCEDURE add_new_order
(order_id_in IN NUMBER, sales_in IN NUMBER)
IS
no_sales EXCEPTION;
BEGIN
IF sales_in = 0 THEN
RAISE no_sales;
ELSE
INSERT INTO orders ( order_id, total_sales )
VALUES ( order_id_in, sales_in );
END IF;
EXCEPTION
WHEN no_sales THEN
raise_application_error (-20001,'You must have sales in order to submit the order.');
END;