Difference Between Raise & Raise_Application_Error

RAISE 

It allows developer to stops the processing of program when some condition is satisfied. We can change the execution path also. This will not return any error message to client application. Exceptions must be declared in deceleration section.

Ex:

SQL> Create or replace procedure Test1(A Number,
B Number) as

C Number;

Negative_Val EXCEPTION;

Begin

IF A < 0 THEN

RAISE Negative_Val;

END IF;

C := A + B;

DBMS_OUTPUT.PUT_LINE('Sum of ' || A  || ' and ' ||  B || '=' || C);

EXCEPTION

WHEN Negative_Val THEN

DBMS_OUTPUT.PUT_LINE('Sorry, -ve Values not allowed');

END;
/

Procedure created.

SQL> EXEC TEST1(-2,1000);
Sorry, -ve Values not allowed

PL/SQL procedure successfully completed.

SQL>
SQL> EXEC TEST1(2,9);
Sum of 2 and 9=11

PL/SQL procedure successfully completed.

SQL>

RAISE_APPLICATION_ERROR

It is predefined oracle procedure, allows developer to raise error with associated error code and message with the procedure. It allows developer to raise application errors instead on oracle errors. The range of error codes can define between -20000 and -20999. It returns the error messages to client application.

Ex :

SQL> Create or replace procedure Test (A Number,

B Number) as

C Number;

BEGIN  

IF A < 0 THEN 

RAISE_APPLICATION_ERROR(-20143,'Sorry, -ve Values not allowed');

END IF;

C := A + B;

DBMS_OUTPUT.PUT_LINE('Sum of ' || A  || ' and ' ||  B || '=' || C);

END; 

/

Procedure created.

SQL> EXEC TEST(-2,1000);

BEGIN TEST(-2,1000); END;

*

ERROR at line 1:

ORA-20143: Sorry, -ve Values not allowed

ORA-06512: at "SCOTT.TEST", line 10

ORA-06512: at line 1

SQL> EXEC TEST(5,9);

PL/SQL procedure successfully completed.

SQL> SET SERVEROUTPUT ON

SQL>

SQL> EXEC TEST(5,9);

Sum of 5 and 9=14

PL/SQL procedure successfully completed.

SQL>