Oracle Apex Dynamic column Name

With the latest release of Application Express, Oracle have included a brand new reporting widget called Interactive Reports. They have managed to make it so simple to have fully functioning report where the only effort from the developer is creating the SQL query. Unfortunately Oracle have failed to allow the report to be based on a PLSQL function returning the query. This omission is a serious oversight by Oracle, as all my reports use this technique. Until Oracle update APEX to include this feature here is a workaround to display an interactive report based on a query stored in the database.

Step1.

Create a collection based on a query. The code for this should be placed in a before header process
APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY(
p_collection_name => ‘IR_TEST’,
p_query => function_returning_query );



Step 2.

Create an interactive report querying a collection.
Select *
From apex_collections
Where collection_name = ‘IR_TEST’;


Step 3.

You will notice that if you run the report that the column headers do not correspond to your dynamic query. This is because the IR is querying the collection and therefore using the column names from that. To resolve this, create hidden page items(eg P1_COL_HEAD1, P1_COL_HEAD2 etc) to hold the correct column name. Ensure that you create enough to hold the maximum expected columns. (I create 50 since this is the maximum a collection can hold). For each of these items replace the cxxx column header text in the report attributes for the relevant page item. eg. C001 for &P1_COL_HEAD1. and C002 for &P1_COL_HEAD2. (remember to include the dot).


Step 4.

Create a process that will populate the heading page items with the relevant column name values from the dynamic query. This can be done by splitting the select line using the commas and extracting the dynamic column headers.

Step 5.

Now that the column headers are displaying correctly we should hide any irrelevant columns. Because a collection has 50 main columns there can be many columns without any data, and because this report is meant to be dynamic we want the columns to dynamically display whenever there is any data in them. Go into the report attributes and go into every column starting cxxx and set a conditional display type of exists and enter the following SQL. Replacing c001 with the relevant column header.
Select 1
From apex_collections
Where c001 is not null;



Finally set the conditional display to never for the following columns collection_name, seq, clob001, md5_original.

And that’s it! The method outlined above does seem a bit extreme to allow an interactive report to display a dynamic query but currently it is the only workaround I know of.