Method to use the query of a Classic Report, including the user defined sort order (if any) in a pl/sql procedure.
A similar (but simpler) method to get the query of an Interactive Report can be found on the Use IR results in pl/sql
Getting the SQL query of a Classic Report is usually quite simple; "select region_source from apex_application_page_regions" is enough.
However, as soon as you also need the sort order (either the default one or what the user selected manually), this is not enough.
The apex_application_page_regions view simply returns the SQL query, nothng else.
The default sort order can be queries using apex_application_page_rpt_cols, but the sort order that the user selected is more difficult.
The function used below does this by using the apex_workspace_preferences view.
Two notes on this:
- The apex_workspace_preferences view is not available in older Apex versions.
The underlying wwv_flow views are however, so you could give yourself some grants and get the information anyway, but that is not very elegant of course.
- The apex_workspace_preferences view only contains information for users that are logged in.
Unfortunately this means the demo on this page does not work here (because this site does not require users to log in), but if you use the code in an application that does require logging in, it should work fine.
I expected to find the session state for that somewhere in a view or something, so I could use it here as well, but I haven't found that one.
In the demo below, I selected all columns from EMP, and moved the DEPT column (which is number 8 in the select statement) to be the first column shown (to demonstrate that the apex_workspace_preferences uses the original column order from the query).
Then I set the Sort Default Sequence for DEPT to 1, and for EMPNO to 2. So the last line in Results shows those 2 columns in the order by.
Copy the objects and code to your own application, manually sort the report on some column (e.g. MGR) , and then click Run Procedure.
The last line in Results should then change to "order by 4, DEPTNO , EMPNO", where 4 is the MGR column you sorted on.