APEX

Hello,

I've got a classic report (source: local view) with faceted search. In the WHERE clause i've got the following query:

id_application IN (
   with ids as (
      select /*+ MATERIALIZE */ *
      from   table(pkg_apx_cs_tab_func.f_apx_cs_v_applicationlist
                     (
                       p_from_page => :P50_FROM_PAGE_ALIAS,
                       p_from_workload => :P50_FROM_WORKLOAD,
                       p_from_status => :P50_FROM_STATUS,
                       p_from_migrationcluster => :P50_FROM_MIGRATIONCLUSTER
                     )
             )
   )
   select column_value from ids
)

This works great as long as I only enable “Compute Counts” on a single facet. When I enable more than one I get the following error:

Ajax call returned server error ORA-22905: cannot access rows from a non-nested table item

It obviously has something to do with the recounting of the facets.

The type that is returned from the pipelined function is created as a schema object:

create or replace TYPE t_apx_cs_appidlist IS TABLE OF NUMBER;

All objects reside in a single schema.

Any ideas?

I suppose a solution could be to make the source-type “SQL Query” and make that a parameterized pipelined function which returns all the columns instead of a list of PK's. But unfortunately that would cause quite a lot of rework and make it less flexible. And I don't know if that would even work (but I wouldnt hurt trying out..)

Thanks!