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 page.

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.

Page

Identification
	Page ID	1174
	Name	Apex - Reports - Get Classic Report query

Region

Identification
	Sequence	20
	Title	Classic Report
	Type	Report
Source
	Source Type	SQL_QUERY
	Region Source	select e.empno ,e.ename ,e.job ,e.mgr ,e.hiredate ,e.sal ,e.comm ,e.deptno from emp e

Button

Name
	Button Name	P1174_RUN_PROCEDURE
	Text Label / Alt	Run Procedure
Displayed
	Sequence	10
	Display in Region	Results
Behavior
	Action	Submit Page
	Execute Validations	Yes

Process

Name
	Button Name	P1174_THE_PROCEDURE
	Type	PL/SQL anonymous block
Process Point
	Sequence	10
	Process Point	On Submit - After Computations and Validations
	Run Process	Once Per Page Visit (default)
Source
	Process	declare l_result clob := empty_clob(); begin l_result := ted_p1174.cr_sql(p_app_id => :APP_ID ,p_page_id => :APP_PAGE_ID ,p_region_static_id => null); apex_util.set_session_state ('P1174_RESULTS', l_result); end;
Conditions
	When Button Pressed	P1174_RUN_PROCEDURE

Package

Package
	Name	TED_P1174
	Source	package ted_p1174 is -- get the SQL statement of a Classic Report function cr_sql(p_app_id in apex_application_page_regions.application_id%type := v('APP_ID') ,p_page_id in apex_application_page_regions.page_id%type := v('APP_PAGE_ID') ,p_region_static_id in apex_application_page_regions.static_id%type := null) return apex_application_page_regions.region_source%type; end ted_p1174;

Package Body

Package Body
	Name	TED_P1174
	Source	package body ted_p1174 is c_lf constant varchar2(2) := chr(13) || chr(10); -- get the details (region id, static id, etc.) of a Classic Report procedure cr_details#(p_app_id in apex_application_page_regions.application_id%type ,p_page_id in apex_application_page_regions.page_id%type ,p_region_static_id in apex_application_page_regions.static_id%type ,o_region_source out apex_application_page_regions.region_source%type ,o_region_id out apex_application_page_regions.region_id%type ,o_static_id out apex_application_page_regions.static_id%type ,o_workspace out apex_application_page_regions.workspace%type) is cursor c_regions is select reg.region_source ,reg.region_id ,reg.static_id ,reg.workspace from apex_application_page_rpt rpt join apex_application_page_regions reg on reg.region_id = rpt.region_id where rpt.application_id = p_app_id and rpt.page_id = p_page_id and (reg.static_id = p_region_static_id or p_region_static_id is null) order by reg.display_sequence; begin open c_regions; fetch c_regions into o_region_source ,o_region_id ,o_static_id ,o_workspace; close c_regions; end cr_details#; -- get the SQL statement of a Classic Report function cr_sql(p_app_id in apex_application_page_regions.application_id%type := v('APP_ID') ,p_page_id in apex_application_page_regions.page_id%type := v('APP_PAGE_ID') ,p_region_static_id in apex_application_page_regions.static_id%type := null) return apex_application_page_regions.region_source%type is -- get the USER sort order from the apex_workspace_preferences view -- note: for logged in users, even if the user did not select a sort order yet, this is already filled (by Apex itself) with the first column of the DEFAULT sort order; -- so to get the correct combination, we can first use the USER sort order_user and then append the DEFAULT sort order, -- because either USER has a different value and overrules DEFAULT, or USER is the same and will not do much (or even any) harm. -- not very elegant but a workable compromise... cursor c_awp(cp_workspace apex_application_page_regions.workspace%type ,cp_region_id apex_application_page_regions.region_id%type) is with iv_awp as (select awp.workspace_id ,awp.preference_name ,to_number(regexp_substr(awp.preference_name, 'FSP(\d+)_P(\d+)_R(\d+)', 1, 1, '', 1)) as app_id -- e.g. FSP102_P1174_R50214351949010292654_SORT --> 102 ,to_number(regexp_substr(awp.preference_name, 'FSP(\d+)_P(\d+)_R(\d+)', 1, 1, '', 2)) as page_id -- e.g. FSP102_P1174_R50214351949010292654_SORT --> 1174 ,to_number(regexp_substr(awp.preference_name, 'FSP(\d+)_P(\d+)_R(\d+)', 1, 1, '', 3)) as region_id -- e.g. FSP102_P1174_R50214351949010292654_SORT --> 50214351949010292654 ,awp.preference_value ,to_number(regexp_substr(awp.preference_value, 'sort_(\d+)_(.+)', 1, 1, '', 1)) as sort_column -- e.g. sort_4_asc --> 4 ,regexp_substr(awp.preference_value, 'sort_(\d+)_(.+)', 1, 1, '', 2) as sort_order -- e.g. sort_4_asc --> asc ,awp.user_name from apex_workspace_preferences awp where awp.preference_type = 'CLASSIC_REPORT' and awp.workspace_name = cp_workspace) select iv_awp.* from iv_awp where iv_awp.app_id = p_app_id and iv_awp.page_id = p_page_id and iv_awp.region_id = cp_region_id and iv_awp.user_name = v('APP_USER'); r_awp c_awp%rowtype; l_region_source apex_application_page_regions.region_source%type; l_region_id apex_application_page_regions.region_id%type; l_static_id apex_application_page_regions.static_id%type; l_workspace apex_application_page_regions.workspace%type; l_sort_order_user varchar2(32767); l_sort_order_default varchar2(32767); l_sort_order varchar2(32767); l_result apex_application_page_regions.region_source%type; begin -- get the report details cr_details#(p_app_id => p_app_id ,p_page_id => p_page_id ,p_region_static_id => p_region_static_id ,o_region_source => l_region_source ,o_region_id => l_region_id ,o_static_id => l_static_id ,o_workspace => l_workspace); -- get the USER sort order (if any) -- note: the Apex implementation of the USER sort order is a bit strange - see the c_awp note for details open c_awp(cp_workspace => l_workspace, cp_region_id => l_region_id); fetch c_awp into r_awp; if c_awp%found then l_sort_order_user := r_awp.sort_column || ' ' || r_awp.sort_order; end if; close c_awp; -- get the DEFAULT sort order (if any) with a as (select r.column_alias ,r.default_sort_sequence ,r.default_sort_direction from apex_application_page_rpt_cols r where r.region_id = l_region_id and r.default_sort_sequence <> 0) select ltrim(listagg(a.column_alias || ' ' || a.default_sort_direction, ', ') within group(order by a.default_sort_sequence) ,', ') into l_sort_order_default from a; -- create the complete sort order (user + default) l_sort_order := l_sort_order_user || (case when l_sort_order_user is not null then ', ' end) || l_sort_order_default; l_result := l_region_source -- || c_lf -- || (case when l_sort_order is not null then 'order by ' || l_sort_order else null end); return l_result; end cr_sql; end ted_p1174;