4 Replies Latest reply: Apr 14, 2014 6:19 AM by Jules123 RSS

    validations, raised exceptions and internationalized error messages

    Jules123

      Hi everyone,

       

      I want to create an application in multiple languages.

       

      I wrote PL/SQL functions for the C(R)UD operations (that are called via Apex) - but without validations. All the validation code is still in the GUI/APEX, because Apex...

      • offers me the possibility to translate my error messages afterwards
      • permits me to assign a validation error to an input field

       

      But is it possible to...

      • put the validation into the PL/SQL functions?
      • still be able assign an error to an input field?

       

      It would be great to have 1 exception for multiple columns, but then, Apex wouldn't know in which input field the error happened, right?

       

      But even if I have e.g. a NO_POSITIVE_NUMBER_IN_COLUMN_1 and a NO_POSITIVE_NUMBER_IN_COLUMN_2 exception, would it be possible for Apex to display the error message(s) besides the right input fields?

      If yes, how is this possible? I know there's a new error handling function option in Apex 4.1, but does that mean that the function needs to know every single exception that's going to be raised?

      And where does the translation take place?

       

      Thanks in advance,

      Jules

        • 1. Re: validations, raised exceptions and internationalized error messages
          Nicolette

          Jules

           

          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.

          But you could use apex_lang and messages to handle 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.

           

          Nicolette

          • 2. Re: validations, raised exceptions and internationalized error messages
            Jules123

            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

            BEGIN

                 IF p_param1 IS NULL THEN

                    validation_failed := TRUE;

                    RAISE param1_is_null_exception;

                END IF;

             

                IF p_param2 IS NULL THEN

                    validation_failed := TRUE;

                    RAISE param2_is_null_exception;

                END IF;

             

                IF validation_failed = FALSE THEN

                    -- everything's fine

                    INSERT INTO ...;

                END IF;

            END;

             

            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?

             

            Jules

            • 3. Re: validations, raised exceptions and internationalized error messages
              Nicolette

              Justin

               

              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 apex_error.c_on_error_page.

              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.

               

              Nicolette

              • 4. Re: validations, raised exceptions and internationalized error messages
                Jules123

                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!

                 

                Jules