PL/SQL Access to Saved Report Data

Question

Using 4.1 (latest version).

I have an Interactive Report page in my app. Users are free to create and save public and private reports setting any filter conditions they choose. What I need to do is loop through these reports and "process" some data based on a column value that matches the filter condition (something like an EMPLOYEE_ID).

What I would like to do is package this functionality into a PL/SQL procedure that is scheduled using DBMS_SCHEDULER.

Other than trying to reverse engineer this from the APEX views, I am stuck. Any help is greatly appreciated.

So if i understand correctly: what you want to do is to retrieve data through (pl)sql as you would through using the interactive report page? — Tom
Yes. What I really need access to is the WHERE part of the query that the IR page is using. — RMAN Express

Tom Tom · Accepted Answer · 2012-06-08T14:33:49

The bad news: there is no built-in way to get the query of an interactive report.

(I hope you can program PLSQL, otherwise you've hit a dead end.)

However, i have a package which does most of the work and is indeed processing the application metadata for IRs. It can handle both column and row filters, and also columns which have an lov laid on top of them. It doesn't handle computations or aggregates. You'd have to take the code and adjust it somewhat though, as my goal was returning some data through json back to the browser, but you won't have to write the query-rebuilding part anymore. I'll refer you to my blog post i made about my package and why i made it, so that might clear up some of the usage of it for you. You can get the zip, and you'll need the APEX_IR package. (at time of writing, it still contains a stupid oversight in that it ignores the dis/enabled state of filters)

PL/SQL Access to Saved Report Data

1 Answers