PL/SQL RAISE_APPLICATION_ERROR parameter question

Question

I am new to PL/SQL and I do not completely understand error-code parameter of the RAISE_APPLICATION_ERROR.

In the documentation it says The error_number is a negative integer with the range from -20999 to -20000. But that does not explain if I can pick random number or is there a system to it. Also I am wondering if I can reuse the same numbers in different functions when calling RAISE_APPLICATION_ERROR? Or will that number be used somewhere to identify that particular error?

I also was looking at the procedure someone wrote and wondering why didn't they just pass SQLCODE to RAISE_APPLICATION_ERROR instead of taking random number like RAISE_APPLICATION_ERROR(l_err_code, 'Record already exists!');

I got an impression that one only uses integers -20999 to -20000 for the user-defined errors, not existing ones.

PROCEDURE insert_record
(v_row IN OUT TABLE1%ROWTYPE) IS

 l_err_code NUMBER;
 l_err_message VARCHAR(200);
 BEGIN 


    INSERT INTO TABLE1 ....
      RETURNING id INTO V_row.id;

 EXCEPTION      
    WHEN DUP_VAL_ON_INDEX THEN
       l_err_code := SQLCODE;  
       l_err_message  := 'Insert Error: ' - ' || SQLERRM;   

       ...logging error here

      RAISE_APPLICATION_ERROR(-20001, 'Record already exists!');    
  WHEN OTHERS THEN 
    l_err_code := SQLCODE;
    l_err_message  := 'Insert Error: ' - ' || SQLERRM;   

     --- loggin error here

    RAISE_APPLICATION_ERROR(-20002, l_err_message);      

END insert_record;

Would it make sense to modify above function as follows?:

PROCEDURE insert_record
(v_row IN OUT TABLE1%ROWTYPE) IS

 l_err_code NUMBER;
 l_err_message VARCHAR(200);
 BEGIN 


    INSERT INTO TABLE1 ....
      RETURNING id INTO V_row.id;

 EXCEPTION      
    WHEN DUP_VAL_ON_INDEX THEN
       l_err_code := SQLCODE;  
       l_err_message  := 'Record already exists!';   

       ...logging error here

      RAISE_APPLICATION_ERROR(l_err_code, l_err_message);    
  WHEN OTHERS THEN 
    l_err_code := SQLCODE;
    l_err_message  := 'Insert Error: ' - ' || SQLERRM;   

     --- loggin error here

    RAISE_APPLICATION_ERROR(l_err_code, l_err_message);      

END insert_record;

I'd really appreciate if someone could answer those questions for me or point me to some documentation clarifying those.

Answer 1

RAISE_APPLICATION_ERROR only accepts numbers in the range -20999 to -20000 for its error_code parameter. Within the -20999 to -20000 you can use the error numbers however you want, without restriction. Any "organization" of the numbers is up to you. If you pass a number outside that range a different exception is raised - ORA-21000: error number argument to raise_application_error of -xxxxx is out of range. For example:

BEGIN
  RAISE_APPLICATION_ERROR(-54321, 'This is not a valid error code');
END;

causes the exceptions

ORA-21000: error number argument to raise_application_error of -54321 is out of range
ORA-06512: at line 2

to be raised. db<>fiddle here

Note that because SQLCODE values generated by Oracle are always outside the range allowed for RAISE_APPLICATION_ERROR you cannot pass SQLCODEs to RAISE_APPLICATION_ERROR or you'll get the same ORA-21000 exception raised, which rather defeats the purpose of passing SQLCODE to RAISE_APPLICATION_ERROR. The correct way to re-raise a system-defined exception such as DUP_VAL_ON_INDEX is to use the no-argument version of the RAISE statement. For example, let's rewrite your code slightly:

PROCEDURE insert_record
(v_row IN OUT TABLE1%ROWTYPE) IS

 l_err_code NUMBER;
 l_err_message VARCHAR(200);
 BEGIN 


    INSERT INTO TABLE1 ....
      RETURNING id INTO V_row.id;

 EXCEPTION      
    WHEN DUP_VAL_ON_INDEX THEN
       l_err_code := SQLCODE;  
       l_err_message  := 'Record already exists!';   

       -- log error here

       -- Now, re-raise the exception so an outer handler can deal with it.
       -- Note that there's no way to include a new error message in this case.

       RAISE;

    WHEN OTHERS THEN 
      l_err_code := SQLCODE;
      l_err_message  := 'Insert Error: ' - ' || SQLERRM;   

       --- log error here

      -- Now, re-raise the exception so an outer handler can deal with it
      RAISE;    
END insert_record;

You can use other error numbers by creating EXCEPTION variables, assigning exception numbers to them using the EXCEPTION_INIT pragma, and then using the RAISE statement to raise these exceptions. For example,

DECLARE
  my_exception EXCEPTION;

  PRAGMA EXCEPTION_INIT(my_exception, -54321);
BEGIN
  RAISE my_exception;
EXCEPTION
  WHEN my_exception THEN
    DBMS_OUTPUT.PUT_LINE('my_exception caught!');
END;

which produces the output

my_exception caught!

db<>fiddle here

PRAGMA EXCEPTION_INIT allows values in the range -10000000 to -1 except for -1403. 100 is also allowed.