CODEWITHSUNDEEP
oracle-databaseerror-handlingplsql
Adam Paynter
Adam Paynter

Reputation: 46908

Providing a more meaningful message when an error is raised in PL/SQL

Suppose I have a PL/SQL function that selects one value from a table. If the query returns no records, I wish for the NO_DATA_FOUND error to propagate (so that the calling code can catch it), but with a more meaningful error message when SQLERRM is called.

Here is an example of what I am trying to accomplish:

FUNCTION fetch_customer_id(customer_name VARCHAR2) RETURN NUMBER;
    customer_id NUMBER;
BEGIN
    SELECT customer_id
      INTO customer_id
      FROM CUSTOMERS
     WHERE customer_name = fetch_customer_id.customer_name;

    RETURN customer_id;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        meaningful_error_message := 'Customer named ' || customer_name || ' does not exist';
        RAISE;
END;

Is there a way to associate meaningful_error_message with the NO_DATA_FOUND error?

Update: It has been suggested that I use RAISE_APPLICATION_ERROR to raise a custom error code when NO_DATA_FOUND is encountered. The purpose of this question was to determine if this technique could be avoided so that the calling code can catch NO_DATA_FOUND errors rather than a custom error code. Catching NO_DATA_FOUND seems more semantically correct, but I could be wrong.

Upvotes: 2

Views: 5584

Answers (2)

Thomas Jones-Low
Thomas Jones-Low

Reputation: 7161

Use RAISE_APPLICATION_ERROR (-20001, 'your message');

This will return an error number -20001, and your message instead of the NO_DATA_FOUND message. Oracle has reserved the error numbers between -20001 and -210000 for user use in their applications, so you won't be hiding another Oracle error by using these numbers.

EDIT: RAISE_APPLICATION_ERROR is specifically designed to allow you to create your own error messages. So Oracle does not have another method of allowing dynamic error messages. To further refine this you can define your own exception in the package where you define your procedure. Add the following: 

CUSTOMER_NO_DATA_FOUND  EXCEPTION; 
EXCEPTION_INIT (CUSTOMER_NO_DATA_FOUND, -20001);

In your procedure code, you do the RAISE_APPLICATION_ERROR, and the client code can do a WHEN CUSTOMER_NO_DATA_FOUND THEN which looks better, and they still have the error message captured in SQLERRM.

Upvotes: 7

Vincent Malgrat
Vincent Malgrat

Reputation: 67722

As suggested by Thomas you can use RAISE_APPLICATION_ERROR. If you also want to keep the NO_DATA_FOUND error on the error stack you can add TRUE as a third parameter to the function:

DECLARE
   l NUMBER;
BEGIN
   SELECT NULL INTO l FROM dual WHERE 1 = 2;
EXCEPTION
   WHEN no_data_found THEN
      raise_application_error(-20001, 'Meaningful Message', TRUE);
END;

ORA-20001: Meaningful Message
ORA-06512: at line 8
ORA-01403: no data found (*)

The line tagged (*) is the original error message.

Upvotes: 2

Related Questions