You can return the default or custom error messages from the stored procedure, and if any error returns, then show it using the apex_error.add_error procedure in the process in Oracle Apex.

Check the following example:

Return one more parameter as out parameter from your procedure, for example:

Create or Replace Procedure My_Proc (o_error_message out varchar2) is
Begin
    -- your procedure code goes here
   
Exception
  when others then
    o_error_message := sqlerrm;
End;

The above procedure will handle the error in the exception section and return the error message using the o_error_message out parameter.

Now you have to catch this error message in the Process in Oracle Apex. Below is an example of the process code:

Declare
  v_error_message varchar2(1000);
  e_error exception;
Begin
  -- call your db procedure
  my_proc(v_error_message);
  if v_error_message is not null then
     -- meaning an error occurred
     raise e_error;
  end if;
  
Exception
   when others then
      apex_error.add_error (
p_message => o_error_message,
p_additional_info => 'Errors',
p_display_location => apex_error.c_inline_in_notification);
raise;
End;

Please test and let me know if any issues.