Using the apex_error package you can put the validations in pl/sql packages.
Using the add_error procedure with the signatures 2 and 3 you can assign the error to a page item.
With signature 4 and 5 of the add_error procedure you can assign the error to a column.
As for the translation.
I haven't worked with translated applications yet. But as I understand the process if you move the validation to pl/sql packages you have to do the translations.
Where to do the translations depends on you preferences. You could add the already translated message to apex_error.add_error in your validation package.
Or do the translation more centralised in your apex_error_handling_example function. In that case you set the message of apex_error.add_error to the name of the text message.
I would do the translation in the apex_error_handling function so there is only 1 place for the translation of error either validations or errors.
And while creating the validation packages I and my colleagues can concentrate on the validations. But we are already working with a setup that "translates" contraint names to user friendly messages. And as such already have a custom error handling function.
If you translate the messages in your validation packages, the standard error handling should be enough.
Hi Nicolette, thanks for your answer!
I realized that the idea of putting the validations into the procedures will lead to a not very user-friendly application.
Let's take for example this:
PROCEDURE insert_values(p_param1 ..., p_param2 ...) IS
IF p_param1 IS NULL THEN
validation_failed := TRUE;
IF p_param2 IS NULL THEN
validation_failed := TRUE;
IF validation_failed = FALSE THEN
-- everything's fine
INSERT INTO ...;
If the user enters neither column1/p_param1 nor column2/p_param2, then two exceptions should be thrown and catched = two (translated) error messages should appear.
This works, when I put the validations in Apex (+ translation is easy).
Or am I missing something?
Instead of raising an error use apex_error.add_error to add the validation error to the stack of errors.
Using the apex.add_error shows the messages as if they where regular validations.
PROCEDURE insert_values(p_param1 ..., p_param2 ...) IS BEGIN IF p_param1 IS NULL THEN validation_failed := TRUE; apex_error.add_error( p_message=>'VALIDATION_NOT_NULL', p_display_location =>apex_error.c_inline_with_field_and_notif, p_page_item_name =>'P1_ITEM1_NAME' ); END IF; IF p_param2 IS NULL THEN validation_failed := TRUE; apex_error.add_error( p_message=>apex_lang.message(p_message=>'VALIDATION_NOT_NULL',p0=>'Label P2_ITEM_NAME'), p_display_location =>apex_error.c_inline_with_field_and_notif, p_page_item_name =>'P1_ITEM2_NAME') ); END IF; END;
In the documentation there is the following note:
This procedure must be called before the Application Express application has performed the last validation or process. Otherwise, the error is ignored if it does not have a display location of
This means that you need to separate the validation checks from you dml actions.
Note that I show 2 different methods for translations in the above example.
For P1_ITEM1_NAME the translation is done centralised in your custom error handeling function.
For P1_ITEM2_NAME the translation is done direct with the validation.
Also note that the not null validations is a poor example because they are set by the page items settings.
Yep, but my idea of putting the validations into the procedures was to be able to re-use the PL/SQL code for a webservice e.g. or another application that knows PL/SQL (since raising exceptions "comes with" PL/SQL).
Plus separating the GUI layer from the other layers (that contain the validations).
Using an APEX specific function or package (like APEX_ERROR) would still make it "connected" to APEX.
Sorry, I should have thought better before asking this question (obviously, it's not possible to raise 2 exceptions at the same time), but thanks for your time!