Skip to Main Content
Feature Request FR-2581
Product Area Page Components
Status OPEN

23 Voters

Error messages from constraints in UI Defaults

joseassumpcao Public
· Jun 19 2022

Idea Summary
Constraints are a good way to maintain your database integrity. If you want to show a user-friendly message when a constraint is violated, you have to write a validation to check the constraint before it's violated, just to show your custom message. This leads to a large amount of validations being written and maintained just to get a clean message (your database is already protected by the constraint, it doesn't need the validation whatsoever).  Development would be noticeably faster if you didn't need to write a validation for every constraint in every page it can gets violated just to get a friendly error message. 

Inside SQL Workshop, on UI Defaults, let developers configure a friendly error message for each constraint in the database. Having this would mean the need for custom validations in applications would drop significantly (in my case over 60%), enhancing productivity by a fair margin. The application would also be cleaner, easier to maintain and more consistent.

This idea is open.



  • ino.laurensse OP1.2 years ago

    A better solution is to put your constraint messages in a message table, which is far easier to maintain and move from DEV to TEST to PROD. The APEX error handling sample function gives an example on how to do that:

            -- If it's a constraint violation like
            --   -) ORA-00001: unique constraint violated
            --   -) ORA-02091: transaction rolled back (-> can hide a deferred constraint)
            --   -) ORA-02290: check constraint violated
            --   -) ORA-02291: integrity constraint violated - parent key not found
            --   -) ORA-02292: integrity constraint violated - child record found
            -- we try to get a friendly error message from our constraint lookup configuration.
            -- If we don't find the constraint in our lookup table we fallback to
            -- the original ORA error message.
            if p_error.ora_sqlcode in (-1, -2091, -2290, -2291, -2292) then
                l_constraint_name := apex_error.extract_constraint_name (
                                         p_error => p_error );
                    select message
                      into l_result.message
                      from constraint_lookup
                     where constraint_name = l_constraint_name;
                exception when no_data_found then null; -- not every constraint has to be in our lookup table
            end if;
  • joseassumpcao OP1.2 years ago

    @ino.laurensse, that's not optimal. We use a similar solution here, but that's exactly what I'm trying to avoid. 

    One of the most voted ideas in this site is the auto dismiss of success messages. One can argue that you can write a custom javascript code to make it happen as we speak.  Does this mean that the implementation of the new feature is useless, since we can write a custom code to achieve the behavior expected? Not at all. 

    We use Apex to boost our productivity, so I think the error-friendly message should be native in UI Defaults exactly to save us from having to custom develop it on our own, since it's a universal necessity.

    Having this feature native also means the friendly error message could appear in other contexts, like if the violation happens when trying to insert a row using a RESTful API.

  • jayson hanes Admin OP1.2 years ago

    Hi Jose, I can see value in this - it does seem to be a good fit to put such a UI here