Oracle: ORA-06510 Error Message

ORA-06510 Error Message

Learn the cause and how to resolve 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 handle 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 handle 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 caused because you raised an exception called no_sales, but you did not include code in the EXCEPTION block to handle the no_sales exception.

You could correct this by modifying the procedure 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;