Oracle APEX Interactive report based on PLSQL function

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.

Create a collection based on a query. The code for this should be placed in a before header process

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.

4 thoughts on “Oracle APEX Interactive report based on PLSQL function

  1. Harald

    Thank you very much for this tutorial, it helped me a great deal.

    I’ve noticed two things which might be useful to others:

    APEX throws an error when a collection is created which already exists. This happens
    to me when I run the application a second time. Adding and IF-clause solved the issue.

    p_collection_name => ‘IR_TEST’,
    p_query => function_returning_query );
    END IF;

    The conditional display sql statement needs to take the collection into account.
    This modified statement seems to work:

    SELECT 1
    FROM apex_collections
    WHERE collection_name = ‘IR_TEST’ AND c001 IS NOT NULL;

  2. Keith Teal

    Instead of hitting the database for all those column checks you could just make sure that your header text page items (P1_COL_HEAD1) are not null.
    Condition: Item/Column not null
    Expression 1: P1_COL_HEAD1

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>