1

The worst aspect of the Interactive Report (IR) is that you cannot create it using a PL/SQL returning SQL statement. I have gotten around this using two methods:

1) APEX_COLLECTION.CREATE_COLLECTION in the Before Header Process, which takes a SQL statement (that is constructed in PL/SQL in the process), and have the IR's source be select c001 alias1, c002 alias2 ... from apex_collections a where collection_name = '...'

2) Make a badass pipeline function with a parameter list as long as you need and then have the IR's source be select * from table(package_name.pipelined_function_name(:P1_parameter1, :P1_Parameter2))

Is there a performance difference? I originally used the first method but then ran into an occurrence where it was giving me a bug so I tried the pipelined function and found I just liked it better and have tended to use them ever since unless it was inappropriate to do so (namely when there is a large number of items to be passed to the parameter).

Matthew Moisen
  • 16,701
  • 27
  • 128
  • 231
  • Hey Matthew, i also use the collection variant but i ran into an other problem. If i use the 1st method i can not make charts and calculations, because all cXXX(query returns more than 20 cols) are varchars. Is this behavior also included when i use the pipeline function ? – Mario May 28 '13 at 12:10
  • 1
    @Mario Nope, the pipelined function will return the correct datatype. First you can make a row, then a type table of those rows, and then the pipelined function returns a table of that type. Pipelined functions have been very helpful to me and I suggest you use them for this use case. – Matthew Moisen May 29 '13 at 17:47

1 Answers1

0

First method gives you opportunity to cache data by re-creating the collection only when you need it. Using n00X and d00X columns will give you some additional performance and right column types for the report definition. You can also create a view based on that collection with type casting and column aliases to add more convenience: 

create or replace view apx_my_report
  as
  select n001 id, c001 data, d001 some_date
    from apex_collections
   where collection_name = 'MY_REPORT'
/

In that case you report source will be like that:

select id, data, some_date from apx_my_report
/

On the other hand, when you need to execute an ad-hoc query every time when page is rendered, it leads to the unavoidable re-creation of a such collection, therefore the performance goes down because of unwanted transaction maintaining: undo, redo etc.

So, it depends.

suPPLer
  • 479
  • 3
  • 12