5 Replies Latest reply on Feb 15, 2013 8:28 AM by Scott Wesley

    Report Still Runs When Validation Errors Occur


      I'm using Apex 4.2.1 against Oracle 11gR2 and Oracle App Server (mod_plsql).

      My Set-Up:
      I created an application containing a "classical" type report that is driven by a WHERE clause. I also have two date fields in an HTML region on this same page. These fields are used in the report WHERE clause. And the report is run by clicking a GO button on the page.

      Here's what the WHERE clause looks like:
      where table_date >= to_date(nvl(:P1_BEGIN_DATE, '01-jan-1900'), 'dd-mon-yyyy') and table_date <= to_date(nvl(:P1_END_DATE, '31-dec-2300'), 'dd-mon-yyyy')
      This is designed so that, if any of the date fields are NULL, then the minimum or maximum date will be used for the query.

      These date fields should always have a value when the report is run, and so, I created two after submit NOT NULL validations, one for each of the date fields. If any of these fields is NULL when the GO button is clicked, an error message appears in the notification area at the top of the page.

      Looking at all this, you might realize (as I do) that the nvl() functions in the above WHERE clause are redundant. After all, the validations are supposed to prevent these date fields from becoming NULL when the report is run. In any case, this set-up does work ok and points out a strange behavior with Apex.

      My Problem:
      If any of these validations throws an error message, the report still runs. In previous versions of Apex, if a validation (or, for that matter, any error) is thrown, the report remains static. The report region does not disappear and the report is not invoked until the user corrects the error.

      If both date fields are filled, I click the GO button and the report correctly returns, say, 100 rows. But when I empty both of the date fields and click GO, the validation errors appear and the report still runs, now returning the maximum number of rows, say, 1000.

      What I've Tried:
      I added a Branch-to-Page with condition "No Inline Validation Errors Displayed" so that the page branches back to itself only when no errors appear inline and I changed the validation error messages to appear inline. This has no effect.

      And if I remove the nvl() functions from the above WHERE clause, then, when validation errors are thrown, the report data is replaced with the "No data found" message. All very disconcerting to my users.

      Does anyone know why Apex reports are still run in presence of validation errors?
      And how do I get around this behavior?

      Thank you for any help.

        • 1. Re: Report Still Runs When Validation Errors Occur
          Scott Wesley
          Question - why validate for NULL if you're NVLing the parameters anyway?

          suggested options to explore:
          1) Stop submitting page - change your button action from submit to controlled by dynamic action, then refresh the region region only when both values are provided.
          2) Don't display report region if parameters null
          3) use region cache the parameters null

          1 person found this helpful
          • 2. Re: Report Still Runs When Validation Errors Occur
            Hi, Scott.

            Thank you for your help/suggestions.

            I like your first suggestion: creating a DA on the GO button that causes my report region to refresh only if both begin and end date fields are populated. And so, I tried doing this.

            My DA looks like:
            Event: Click
            Type: Button
            Button Name: P1_GO
            Condition: - No Condition -
            Action: Refresh
            Fire When Event Result Is: True
            Fire On Page Load: Checked
            Selection Type: Region
            Region Name: People Count
            Event Scope: Static
            Condition Type: PL/SQL Function Body Returning A Boolean
            My PLSQL Code in Expression 1:
              RETURN TRUE;
              RETURN FALSE;
            END IF;
            My P1_GO button was automatically changed to "Controlled by Dynamic Action". And "Execute Validations" for this button is set to "Yes".

            Unfortunately, this DA does not work. When both date fields are filled, clicking the GO button does not cause the report (nor anything else) to refresh. However, if I navigate to another application tab and then return back to my report page, I notice that the report does refresh at that point. But clicking the GO button has no effect.

            And when either or both of the date fields is NULLed, clicking the GO button does not cause my validation error messages to appear.

            By the way, I do agree with your comment about the NVL() functions. As I had mentioned in my original post, I also realize that, with my WHERE clause, checking for NULLs in my date fields is not needed. Still, if users fail to fill these date fields, then it would be useful to remind users that these fields should be populated. This is why having the validation checks are still needed despite my WHERE clause.

            In any case, would you know what I am doing wrong here with this Dynamic Action?

            Also, with respect to my first question, why is Apex causing my report to refresh when validaion errors have occurred? This did not occur prior to version 4.2.

            Thank you very much for any further help/advice.

            • 3. Re: Report Still Runs When Validation Errors Occur
              Scott Wesley
              The problem with the DA is a common slip-up - the conditions need adjusting.

              Remove the condition on the DA itself - this decides when to render the DA as part of the page. In your case you always want this to happen (no condition).

              What you need to define is the condition that will cause the DA to do it's work when the relevant event happens in the browser (click on button).
              A javascript expression like (sorry JS purists)
              ($v('P1_BEGIN_DATE') !== null) && ($v('P1_END_DATE') !== null)
              Check this out for your situation, there seems to be a zillion ways to check for null

              In these cases, your friend is running in debug mode with the browser console log open.

              You'd maybe need to wait for someone from the APEX dev for feedback on the differences in 4.2

              Hope that makes sense

              • 4. Re: Report Still Runs When Validation Errors Occur

                Thank you very much for your help. Using the JS expression worked.

                By the way, I tried to open the link you included but it refuses to open. I even copied/pasted the link into a separate browser (both IE and Firefox). The browser just sits, trying to open up the page. After a couple minutes I'm left with just a blank page, Not sure why.

                With respect to my question as to why Apex 4.2.1 is behaving as I observed (despite validation errors being thrown, the report still runs), I'm hoping the good folk at Oracle/Apex address this.

                Thank you, again, for all your help/advice. It's much appreciated.


                Edited by: EEG on Feb 14, 2013 11:54 AM
                • 5. Re: Report Still Runs When Validation Errors Occur
                  Scott Wesley
                  No probs.

                  That link worked for me at work & home. It was just one of many results regarding null checks in javascript.