4 Replies Latest reply on Feb 11, 2013 11:35 AM by 650063

    Apex - how to validate in common PlSql package and show custom messages

    650063
      I'm new to APEX 4.2.
      Currently if i let required field empty, then APEX puts systematic error message "XXX must have some value." near the field and into the page header.
      I want to create custom business rule, that would show message for example "This action cannot be performed, because this and that...".
      That kind of custom validation should be in my plSql package and somehow it should throw/send the phrase "This action cannot be performed, because this and that..." to the page header where was message "XXX must have some value.".
      How can i create such custom error message to appear out using PlSql package function that i define myself?
      I plan to create a common validation package, which has lots of plsql functions to validate lot of APEX pages/forms, all those functions should throw/send somehow arror messages to page header.
      Can you give a hint how to do it?
      I looked that this unofficial solution does not suit for me:
      http://blog.theapexfreelancer.com/2011/02/writing-validations/
      Because it disables APEX systematic automatic validations as i understand, and is unofficial.
        • 1. Re: Apex - how to validate in common PlSql package and show custom messages
          Nicolette
          Charles Roos

          Have a look at the [url http://docs.oracle.com/cd/E37097_01/doc/doc.42/e35127/apex_error.htm#BGBEEGIB]apex_error documentation .

          An "Error" added with apex_error.add_error is treated as a validation error.

          Nicolette
          1 person found this helpful
          • 2. Re: Apex - how to validate in common PlSql package and show custom messages
            fac586
            CharlesRoos wrote:
            I'm new to APEX 4.2.
            Currently if i let required field empty, then APEX puts systematic error message "XXX must have some value." near the field and into the page header.
            I want to create custom business rule, that would show message for example "This action cannot be performed, because this and that...".
            That kind of custom validation should be in my plSql package and somehow it should throw/send the phrase "This action cannot be performed, because this and that..." to the page header where was message "XXX must have some value.".
            How can i create such custom error message to appear out using PlSql package function that i define myself?
            I plan to create a common validation package, which has lots of plsql functions to validate lot of APEX pages/forms, all those functions should throw/send somehow arror messages to page header.
            Can you give a hint how to do it?
            APEX 4.1 introduced new built-in error handling features and the <tt>apex_error</tt> error handling API.

            Patrick Wolf has written a couple of articles on how to use these to customize APEX error handling:

            <li>APEX 4.1 – Error Handling Improvements – Part 1

            <li>APEX 4.1 – Error Handling Improvements – Part 2
            1 person found this helpful
            • 3. Re: Apex - how to validate in common PlSql package and show custom messages
              650063
              With APEX 4.2 comes with and example application:

              "Aplication Builder"-->"Packaged Applications"-->"Sample Database Application"
              (The Sample Database Application is an application that highlights common design concepts. It includes dedicated pages for customers, products, and orders as well as demonstrates the use of reports, charts, calendar, map, and tree.).

              That example application has example of what "Patrick Wolf" describes.
              The idea is to click administrative button "Edit Application Properties" and enter there a global error handling function that Patrick describes and that the example application has.
              That global common error handling function expects that one has already thrown custom error with "raise_application_error" function. It takes the custom error code and translates it little bit nicer and puts into APEX page header.

              But where and how i should raise such custom errors, can you give example?

              I understand that i can put into database table trigger code some if-else clauses and call "raise_application_error". But trigger itself is not a good place. I think better place would still be to have a special plSql function "doValidationForButton1Click" which get called when Button1 is clicked. How can i call a plSql function when button is clicked?

              The global error handling demo application code is below.
              Specially for me is interested the section
                          -- If an ORA error has been raised, for example a raise_application_error(-20xxx, '...')
                              -- in a table trigger or in a PL/SQL package called by a process and we
                          -- haven't found the error in our lookup table, then we just want to see
                          -- the actual error text and not the full error stack with all the ORA error numbers.
                          if p_error.ora_sqlcode is not null and l_result.message = p_error.message then
                              l_result.message := apex_error.get_first_ora_error_text (
                                                      p_error => p_error );
                          end if;
              create or replace package body sample_pkg as
                  --
                  -- Error Handling function
                  --
                  function demo_error_handling (
                      p_error in apex_error.t_error )
                      return apex_error.t_error_result
                  is
                      l_result          apex_error.t_error_result;
                      l_reference_id    number;
                      l_constraint_name varchar2(255);
                  begin
                      l_result := apex_error.init_error_result (
                                      p_error => p_error );
                      -- If it's an internal error raised by APEX, like an invalid statement or
                      -- code which can't be executed, the error text might contain security sensitive
                      -- information. To avoid this security problem we can rewrite the error to
                      -- a generic error message and log the original error message for further
                      -- investigation by the help desk.
                      if p_error.is_internal_error then
                          -- Access Denied errors raised by application or page authorization should
                          -- still show up with the original error message
                          if p_error.apex_error_code <> 'APEX.AUTHORIZATION.ACCESS_DENIED' then
                              -- log error for example with an autonomous transaction and return
                              -- l_reference_id as reference#
                              -- l_reference_id := log_error (
                              --                       p_error => p_error );
                              --
                  
                              -- Change the message to the generic error message which doesn't expose
                              -- any sensitive information.
                              l_result.message         := 'An unexpected internal application error has occurred. '||
                                                          'Please get in contact with your system administrator and provide '||
                                                          'reference# '||to_char(l_reference_id, '999G999G999G990')||
                                                          ' for further investigation.';
                              l_result.additional_info := null;
                          end if;
                      else
                          -- Always show the error as inline error
                          -- Note: If you have created manual tabular forms (using the package
                          --       apex_item/htmldb_item in the SQL statement) you should still
                          --       use "On error page" on that pages to avoid loosing entered data
                          l_result.display_location := case
                                                         when l_result.display_location = apex_error.c_on_error_page then apex_error.c_inline_in_notification
                                                         else l_result.display_location
                                                       end;
                  
                          -- 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 );
                              begin
                                  select message
                                    into l_result.message
                                    from demo_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;
                          end if;
                          -- If an ORA error has been raised, for example a raise_application_error(-20xxx, '...')
                              -- in a table trigger or in a PL/SQL package called by a process and we
                          -- haven't found the error in our lookup table, then we just want to see
                          -- the actual error text and not the full error stack with all the ORA error numbers.
                          if p_error.ora_sqlcode is not null and l_result.message = p_error.message then
                              l_result.message := apex_error.get_first_ora_error_text (
                                                      p_error => p_error );
                          end if;
                          -- If no associated page item/tabular form column has been set, we can use
                          -- apex_error.auto_set_associated_item to automatically guess the affected
                          -- error field by examine the ORA error for constraint names or column names.
                          if l_result.page_item_name is null and l_result.column_alias is null then
                              apex_error.auto_set_associated_item (
                                  p_error        => p_error,
                                  p_error_result => l_result );
                          end if;
                      end if;
                      return l_result;
                  end demo_error_handling;
                      
                  
              end sample_pkg;
              
              I understand that i can put into database table trigger code some if-else clauses and call "raise_application_error". But trigger itself is not a good place. I think better place would still be to have a special plSql function "doValidationForButton1Click" which get called when Button1 is clicked. How can i call a plSql function when button is clicked?

              Edited by: CharlesRoos on 11.02.2013 11:58
              • 4. Re: Apex - how to validate in common PlSql package and show custom messages
                650063
                Sorry for messy long questions of mine.
                I got answer.
                Solution:

                1. See application "Sample Database Application" page 7 "Customer Details".
                2. See section "Page processing"-->"Validating"-->"Validations".
                3. Add new validation.
                Type="PL/SQL Error".
                Conditions-->"When Button Pressed"="SAVE".
                For field "Validation Expression 1" type such example code:
                if 1=1 then
                   raise_application_error (-20001,'My custom error.');
                end if;
                4. Now test/run your page by selecting one Customer, and press button "Save". It displays your custom error message.
                5. That all works of course if you have set up your global common error handler function, like "Sample Database Application" does and "Patrick Wolf" describes.

                Instead of having plSql code "if 1=1 then..." you can add call to your validation package function. Somehow the parameters to the validation function can be passed.

                So, shortly this way. Topic is answered.


                Edit:
                Better add such code there:
                apex_error.add_error (
                    p_message          => 'This custom account is not active!',
                    p_display_location => apex_error.c_inline_in_notification );
                Edited by: CharlesRoos on 11.02.2013 13:34