PL/SQL RAISE_APPLICATION_ERROR parameter question

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.

You can pick any number in that range for your own application errors. It's certainly possible to create a real mess with duplicates numbers and subtly different error text messages. Error numbers you define could be errors that are business logic errors not known possible errors such as too many rows.

There is a really good detailed explanation here and here by Steven Feurstein where he outlines the proper scope.

Error handling is worth spending some time thinking about and leads to some abstract questions such as:

• can the application carry on processing if there is an error?
• If not then you need to stop processing as you don't want an incomplete or partial transaction processed
• what information do you need to trace where an error occurred? (usually, package name, function/procedure name, line number, offending code, parameters in use when the exception was thrown.

In my experience most applications cannot and should not continue processing a transaction when an exception of any kind is called. At that time you want to log all the details to a table, halt further processing and have the application redirect to a "sorry, an error occurred" page.

For the code above I would remove the When Others. You have errors that you know could occur such as too many values. You want to log details and stop processing. For unknown errors I would rather have the error bubble up from the procedure or function call than seeing it possibly erroneously classed as a known error.

You are correct in that codes between 20000 and 20999 are for user-defined errors. There is no need to force such an error code into a condition/response that is already defined by Oracle unless you need to add some other essential level of detail to the error handling.