7 Replies Latest reply: Dec 6, 2006 6:54 AM by 256719 RSS

    ORA-01785 error when dynamically resetting report source

    256719
      When programmatically changing the region source on a 'function body returning SQL query' report, i get the following error:

      ORA-01785: ORDER BY item must be the number of a SELECT-list expression

      if the new SQL statement has fewer columns than the one it is replacing, and the user had previously selected a sort column for a column# higher than the highest column number in the new query

      for instance if the query was SELECT * FROM EMP and the user had clicked the COMM header to sort on COMM (column number 7), then changing the region source to SELECT * FROM DEPT results in the ORA-01785 -- evidently because Apex is submitting SELECT * FROM DEPT ORDER BY 7

      is there a way to programmatically reset Apex's ORDER BY column# setting? clear cache and reset pagination don't seem to do it
        • 1. Re: ORA-01785 error when dynamically resetting report source
          Justin Patterson
          I'm running into the same problem, ultimately on the region defintion tab under the query source you can toggle the "Use Query-Specific Column Names and Validate Query" by switching it to the opposite that you currently have applying the changes and then setting it back. This does however clear anything you have saved in the column attributes of the report... I did a trace and for my report weather I set one column to sort by or ten the order by sticks to 45.
          • 2. Re: ORA-01785 error when dynamically resetting report source
            256719
            Thanks for the reply, Justin. But the point is to be able to do it programmatically... end users can't be going into design mode and twiddling with things.

            Anyways, thanks for the contribution.
            • 3. Re: ORA-01785 error when dynamically resetting report source
              256719
              ok, it looks like if i can mimic the link generated for sortable columns, ie, http://127.0.0.1:8081/apex/f?p=118:13:6691994397634648:fsp_sort_1::RP&fsp_region_id=3945432343617540, then i'll be able to reset the sort to column 1 every time i change the source object.

              just got to figure out how to do that via the branch definition... any ideas? currently i'm using a Select List with Submit item to chose the region source, and an unconditional branch
              • 4. Re: ORA-01785 error when dynamically resetting report source
                Justin Patterson
                Would it be possible in your case just to permanantly set your region source to "Use Generic Column Names" ?
                • 5. Re: ORA-01785 error when dynamically resetting report source
                  256719
                  Would it be possible in your case just to permanantly
                  set your region source to "Use Generic Column Names" ?
                  i'm already using that -- it's the only way to have a SQL Query (PL/SQL function body returning SQL query) region that returns different query structures depending on its calculations/processing
                  • 6. Re: ORA-01785 error when dynamically resetting report source
                    VANJ
                    See if http://htmldb.oracle.com/i/doc/aadm_user_pref.htm#BABHHGBI applies to your situation.

                    Resetting all preferences for a user would reset/clear all the stored column header sort prefererences as if the report was being run for the very first time (and your design-time default options would kick in)
                    • 7. Re: ORA-01785 error when dynamically resetting report source
                      256719
                      that put me on the right track

                      i really didn't want to reset all user preferences, as i am making extensive use of them in this application

                      however, i did find HTMLDB_UTIL.REMOVE_SORT_PREFERENCES -- which, unfortunately, removes all sort preferences, not for a specific page or region.

                      i was able to find the correct preference in WWV_FLOW_PREFERENCES$, so I can user HTML_DB_UTIL.SET_PREFERENCE to reset it, but the preference_name includes the region#, which changes on import -- so i'll need to extract if at runtime using javascript, or figure out some other runtime technique for finding the correct preference_name

                      hey, apex developers, how about enhancing HTMLDB_UTIL.REMOVE_SORT_PREFERENCES to allow setting/removing the sort preference for a specific page (by page number or alias) and region (by position on the page or some other reliable identifier?