Hello guys,
We use
Apex version 4.2.5
Database 11.2.0.4
My team and I are facing a problem.
We have the need to generate interactive reports on several pages using PL/SQL function. We came to a solution using collection following this blog post : Vincent Deelen: Interactive Report based on Dynamic SQL .
It works !
But we loose all the column types because of the collection, all the columns are in VARCHAR now, so all the functionnalities based on NUMBERS or DATES as computations, charts etc. available using IR do not work.
We have determined these work-arounds :
- Using create_collection_from_query_b2 instead of create_collection_from_query_b :
- We'll be able to have 5 DATE columns and 5 NUMBER columns.
- BUT only 5 for these time and sometimes we need more
- Cast the fields at the select statement when querying the collection. It works
- BUT too static ? Each time we'll modify the query, we'll have to modify the casts
- Generate a nested table instead of using a collection, we'll not loose the types
- BUT too static ? We need to declare all the fields for on row of the nested table, so same problem as above
Is there any completely dynamic solution for this, am I missing something ?
We plan to migrate to APEX 5 soon, is there any solution for using pl/sql function for populating an interactive report in this version ?
Thanks a lot,