9 Replies Latest reply on Sep 17, 2018 10:56 AM by Carsten Czarski-Oracle

    APEX 18.1 Tabular Form ORA-01403 error with Break Column

    M L

      Hi All,

       

      I have several pages with tabular forms based on SQL queries. The Column  Breaks (with Compute Sums) are no longer working after upgrading to 18.1. They are throwing  ORA-1403 errors.

      I've tried removing the 'order by' clauses and replacing them with the Sorting > Default Sequences but that didn't help. If I remove the Column Breaks, the Compute Sum does give me Report Totals.

      Does anyone know what might be causing this?

       

      Thanks very much!

        • 1. Re: APEX 18.1 Tabular Form ORA-01403 error with Break Column
          Carsten Czarski-Oracle

          Hi,

           

          I can reproduce the behavior you are describing. I have filed bug number 28639703 to track this issue and we endeavour to fix this bug in the upcoming release of Application Express.

           

          The only workaround I currently have for you right now is to disable Column Breaks. Another option would be to consider using Interactive Grids instead of the Legacy Tabular Forms - however, the latter one is not just turning a switch.

           

          I hope this helps

           

          -Carsten

          1 person found this helpful
          • 2. Re: APEX 18.1 Tabular Form ORA-01403 error with Break Column
            M L

            Unfortunately switching to Interactive Grids is not a realistic option at this point since there's a lot of coding behind the forms.

            Thanks for filing the bug!

            • 3. Re: APEX 18.1 Tabular Form ORA-01403 error with Break Column
              Carsten Czarski-Oracle

              Hi,

               

              I have good news for you.

               

              A patch is available to be applied on top of APEX 18.1.0.00.45. Go to My Oracle Support and download patch number 28128115. This is actually a "Bundle Patch" which also fixes other issues. Once the patch has been applied, the issue should go away.

               

              Best regards

               

              .Carsten

              1 person found this helpful
              • 4. Re: APEX 18.1 Tabular Form ORA-01403 error with Break Column
                M L

                Thanks for your followup Carsten. That IS good news and I will give this information to my service provider.

                • 5. Re: APEX 18.1 Tabular Form ORA-01403 error with Break Column
                  M L

                  Hi,

                  My service provider applied patch 28128115 and now I am faced with even more problems. All of my classic reports that have Column Breaks are ignoring the 'order by' clauses. So I am forced to use Sorting > Default Sequences. This is doable but I don't want my users to be able to change the sort sequence as that causes the column breaks to function incorrectly. Is there an easy way to disallow users from clicking on the sortable headings?

                  Thanks!

                  • 6. Re: APEX 18.1 Tabular Form ORA-01403 error with Break Column
                    Carsten Czarski-Oracle

                    Hi,

                     

                    this is the impact of the engine changes for Classic Reports which are introduced with APEX 18. They are listed in the Release Notes:

                    https://docs.oracle.com/database/apex-18.1/HTMRN/toc.htm#HTMRN-GUID-C86A170E-8092-48C0-ADE6-EF3F9F426ADA

                     

                    In your case, your report query is wrapped by APEX to execute the "Compute Sum" feature. Due to execution the SUM() analytic function, your original ORDER BY clause is not respected by the Query Optimizer any more.

                     

                    The solution to this is to use the declarative Column Sorting, as you already indicated (Sorting > Default Sequences). In APEX 18.2 (which we plan to release soon), you will be able to set the Default Sequence independently from the Sortable attribute - you'll be able to set the Default Sequence while Sortable remains being set to "No".

                     

                    In the meantime you might enable sorting, set the Default Sequence and use the following Javascript Expression (Page Attributes > Javascript > Execute on Page Load) in order to disable the Column Header Links.

                     

                    $("th div.u-Report-sort span.u-Report-sortHeading a").contents().unwrap();
                    

                     

                    Does that help?

                     

                    Best regards

                     

                    -Carsten

                    1 person found this helpful
                    • 7. Re: APEX 18.1 Tabular Form ORA-01403 error with Break Column
                      M L

                      Yes! This is exactly what I was hoping for (i.e. that the Sortable attribute could be set separately from the Default Sequence). And in the meantime, the javascript worked perfectly.

                       

                      Thanks so much for your help, Carsten.

                      • 8. Re: APEX 18.1 Tabular Form ORA-01403 error with Break Column
                        fac586

                        Carsten Czarski-Oracle wrote:

                         

                        this is the impact of the engine changes for Classic Reports which are introduced with APEX 18. They are listed in the Release Notes:

                        https://docs.oracle.com/database/apex-18.1/HTMRN/toc.htm#HTMRN-GUID-C86A170E-8092-48C0-ADE6-EF3F9F426ADA

                         

                        In your case, your report query is wrapped by APEX to execute the "Compute Sum" feature. Due to execution the SUM() analytic function, your original ORDER BY clause is not respected by the Query Optimizer any more.

                        That section of the Release Notes makes no mention of ORDER BY clauses. Please clarify all of the circumstances in which Classic Reports will no longer respect ORDER BY clauses in the report source query, and update the documentation (Release Notes, App Builder's User Guide, and online help) accordingly.

                        The solution to this is to use the declarative Column Sorting, as you already indicated (Sorting > Default Sequences). In APEX 18.2 (which we plan to release soon), you will be able to set the Default Sequence independently from the Sortable attribute - you'll be able to set the Default Sequence while Sortable remains being set to "No".

                        What about multi-column and complex expression sorts? Will all of the sort columns and expressions have to be included in the query projection, defined as hidden columns, and have these sort properties manually set?

                        • 9. Re: APEX 18.1 Tabular Form ORA-01403 error with Break Column
                          Carsten Czarski-Oracle

                          Hi,

                           

                          thank you for the Heads-Up. The basic change in APEX 18.1 is as follows:

                           

                          • Pre 18.1, Classic reports always started fetching at the first row (also for the second and following report pages). "Compute Sum" was calculated in PL/SQL only (while APEX fetched through the rows, it calculated the sum).
                          • In APEX 18, report pagination is done with SQL - APEX only fetches the rows which are really needed for the current page view
                          • Thus, the manual sum calculation cannot be done any more. APEX now uses a SQL analytic function (SUM() OVER()) for this. To do this, APEX wraps the original query with an outer query containing the SUM() function.

                           

                          This can lead to (but not has to) the original ORDER BY clause being overridden by the Query Optimizer. Whether this happens (or not) depends on the actual execution plan, the optimizer chooses for query execution. The cases we have seen so far, were classic reports with Compute Sum and Column Break enabled. I don't expect that to happen for reports with Column Break not enabled.

                           

                          We might add some more clarity to the Release Notes - but it depends on the query optimizer (thus also on database versions etc) - so it will be hard to exactly enumerate the cases where this will happen.

                           

                          I hope this helps

                           

                          -Carsten