Display user friendly error message while logging SQL error into debugger

I'm using Oracle Apex version 24.1.7.

In my PL/SQL block, I want to catch all errors. If an error occurs, I want to exit the code, display a user friendly error message to the user, and log the SQLERRM for the developer to review in the debug logs.

I've tried multiple different combinations of raise_application_error, apex_error.add_error, and APEX_DEBUG_MESSAGE.LOG_MESSAGE. No matter how I arrange or which option I use. The raise_application_error message overwrites any other error message triggered in the apex debug logs. Here's an example of what I think should work:

EXCEPTION
    when others then
        -- Log the unidentified error for the devs
        apex_debug_message.log_message(
            p_message => 'Unidentified error: ' || sqlerrm || ': ' || dbms_utility.format_error_backtrace, p_level   => 1
        );
        -- Raise a user friendly error message for the user
        raise_application_error(-20001, 'An unidentified error occurred. Please contact Support.');   
END;

I make sure to log the error before I raise the application error. But when I go into the debug log, the only error that's displayed is "ORA-20001: An unidentified error occurred. Please contact support."

This seems like basic functionality i'm trying to achieve, what am I missing?

I think you're mixing a few things. Debug is meant only for development. When an app is production, it will not run in "debug" mode and those debug message will not be logged. If you want to change error the user is seeing, then the way to go is a custom error handling function. This allows you to look at the error and decide how to (change it if needed and) present it to the user. You only need to define this single function and reference it in your app and not add any special calls in your pl/sql code (in page processes, validations, etc)

There is a great example of such a function in the documentation The original blog from when this feature was introduced is no longer available online but google "oracle apex error handling function" and you'll get some blogs that might help you.