How to create report with dynamic query in Oracle APEX?

In Oracle APEX, most of the time you create a report based on a table or a SQL query. For both these options, the structure of the query has been decided at the time of creating the report. But sometimes it's not possible to define the query while creating the report and you need to build the query at the runtime depending on the user's input. You can create a report based on a dynamic SQL by setting the source type of the report to be Function Body returning SQL Query. The source of such a report is a function that computes the SQL query and returns it as a string.

Consider the report page shown below. It has two regions. The top region enables a user to select the desired table and select the columns to display. Clicking the Generate Report button displays the report in the bottom region.

The top region Specify your Report is a static region that has two items and a button. The first item named P51_TABLE is a select list that displays the name of the tables. It is defined by the following query.

SELECT table_name as displayval,
       table_name as returnval
FROM user_tables
ORDER BY displayval;

The second item named P51_COLUMNS is a Shuttle item. It displays the column of the table that the user select using the first item. It is defined using the below query.

SELECT column_name as displayval,
       column_name as return val
FROM user_tab_columns
WHERE table_name = :P51_TABLE
ORDER BY column_id;

This query references P51_TABLE item to display columns according to the table selected in P51_TABLE items. To get this list of columns to update when P51_TABLE changes, set the Parent(s) item property in the Cascading List of Values section to P51_TABLE as shown in the below image.

Finally, the Generate Report button executes the submit action when pressed and saves the values of these two items into the session state.

The second region Report on &P51_TABLE. is a classic report and the source type is set to Function Body returning SQL Query. The below listing shows the code for this report region.

declare
  v_table varchar2(50)  := :P51_TABLE;
  v_cols  varchar2(5000) := replace(:P51_COLUMNS, ':', ',');
begin
  return 'select ' || v_cols || ' from ' || v_table ;
end;

One important property to note here is the Use Generic Column Names. Column definitions of a report are derived from the SQL query which requires the query to be parsed while creating the report. Since we are using the dynamic query to generate the reports, the query cannot be parsed at the time of creating the report. Setting this property enables reports to use generic column names and produces placeholder column definitions. 

Also, specify the maximum number of generic report columns in the Generic Column Count property. Reports that use generic column names are parsed at runtime only, so the columns of the reports are not known until runtime. Using this property you can tell APEX that how many columns to create for this report. In this report, I specified a column count of 50, which means that APEX will generate 50 columns named COL01, COL02, COL03,... up to COL50 for the report as shown in the below image. This is the maximum number of columns that APEX generates for the report. At the runtime, if the report has fewer columns then other columns will be ignored.

If you run the report at this time it runs completely fine as shown in the below image and displays the data for the selected table. But here we got a problem. The heading of the columns are all the generic names and it's not clear what the data is all about. 

You can dynamically set the column header using the Heading Type property that you can find in the Attributes tab in the report property section as shown in below image. Select the heading type PL/SQL Function Body and define the PL/SQL code in the PL/SQL Function Body textarea that will return the column's name in a colon-separated string. Here item P51_COLUMNS holds column names in a colon-separated string and that is what you need at the moment. So your code is simply to return this item.    

Now when you run your report, it will display the proper column heading names.