Having some confusion in exception types

Question

1. We can pass error message, error code to front end using USER_DEFINED EXCEPTION or PREDEFINED EXCEPTION (others, SQLERRM, SQLCODE) using OUT PARAMETER MODE, then why we use RAISE_APPLICATION_ERROR procedure?

2. What is the difference between RAISE_APPLICATION_ERROR and pragma EXCEPTION_INIT?

I have Googled these questions, but can't get a clear answer - that is why posted here.

Answer 1

See this similar question. The answer to both of those is that RAISE_APPLICATION_ERROR is the canonical way to define custom error messages to be displayed in the client application. Yes, you could also pass custom messages back using an OUT parameter, or DBMS_OUTPUT, or something similar, but those are not standardized - you'll have to add code on both the server and client side to handle them.

DECLARE
   my_exception EXCEPTION;
   PRAGMA EXCEPTION_INIT(my_exception, -20001);
BEGIN
   raise my_exception; -- message is default "ORA-20001: "
END;
/

BEGIN
   raise_application_error(-20001, 'My custom error message'); -- message is "ORA-20001: My custom error message"
END;
/

This is often useful when you want to give a more helpful, descriptive error message.

DECLARE
  age NUMBER;
BEGIN
  age := 'hello';
EXCEPTION when VALUE_ERROR then
  raise_application_error(-20001, 'Age must be a number.');
END;
/

So instead of a generic "PL/SQL: numeric or value error: character to number conversion error", we can tell the user exactly what the problem is: "Age must be a number".

Answer 2

A user-defined exception doesn't provide any message to the calling application. It'll just get a generic 'ORA-06510: PL/SQL: unhandled user-defined exception'. raise_application_error lets you pass a message describing the actual problem.

declare
    out_of_stock exception;
begin
    raise out_of_stock;
end;
/

ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at line 4


begin
    raise_application_error(-20000, 'Cheese is out of stock');
end;
/

ERROR at line 1:
ORA-20000: Cheese is out of stock
ORA-06512: at line 2

pragma exception_init(exception_name, error_code) lets you associate your own user-defined exception with a system error code. This can be useful from a programming perspective, but ultimately it doesn't add any value for the caller:

declare
    invalid_day_of_month exception;
    pragma exception_init(invalid_day_of_month, -1847);

    d date;
begin
    d := to_date('99-JAN-2020','DD-MON-YYYY');
exception
    -- Pointless exception handler - just to demo raising a user-defined exception
    when invalid_day_of_month then raise;
end;
/

ERROR at line 2:
ORA-01847: day of month must be between 1 and last day of month
ORA-06512: at line 10
ORA-06512: at line 7

Passing success/failure status via OUT parameters is usually a bad idea because the procedure will appear to have completed successfully, and the caller has to read the status to see whether it really succeeded or not.