You might want to have a look at the APEX error handling function, here's an example of one.
The error handling function will be able to modify the error message and display location or to log the error if one occurs.
It can reference a package function or stand alone function in the database.
It can be set in the Shared Components, Application Definition under the Error Handling section.
You might also want to have a look at the APEX_ERROR API.
Hope this can help.
yes, you can create a custom function, but it will always produce more or less generic messages (confusing for end users). I think the proper way is just not let these situations happen, e.g. using validations. It needs some extra effort, eats a bit of system resources, but error messages produced by validations are user friendly and usually self-explanatory. So in this particular case I would create a validation and error message:
1) No rows returned - Department already exists (or something similar),
2) Item is NOT NULL - Field x_y must have some value (or just change the Value Required attribute to yes),
3) custom function returning boolean and an appropriate error message.
Patrick Wolf did a blog post some time ago about apex 4.1 custom error handling: APEX 4.1 – Error Handling Improvements – Part 1 | Inside Oracle APEX by Patrick Wolf
I used this technique in several projects and am quite happy with it. It still works in Apex 5.1.