Using
APEX 5.2
Oracle DBAAS 12c
I'm trying to create an APEX collection using create_collection_from_query_b in a plsql procedure.
As you can see from the code below, my query string (l_query) references an oracle collection (declaration below) and I want to use this oracle collection (l_dates_tbl of type dates_tbl) to populate my APEX_COLLECTION Pattern_Dates
CREATE OR REPLACE TYPE dates_obj AS OBJECT
(
date_only TIMESTAMP(0),
generated_date VARCHAR2(50),
day_of_week VARCHAR2(10),
checked VARCHAR2(400),
comment VARCHAR2 (300)
);
/
CREATE OR REPLACE TYPE dates_tbl AS TABLE OF dates_obj;
/
In my procedure I populate the dates_tbl with WITH statements (this all works correctly) and I get the correct results
l_dates_tbl dates_tbl
...
BEGIN
....
WITH combined AS
(
--some select statements
)
SELECT dates_obj (date_only,
generated_date,
day_of_week,
checked,
comment )
BULK COLLECT INTO l_dates_tbl
FROM combined;
--Now I want to store l_dates_tbl in an APEX collection so I can keep using it inside my application and in other procedures.
apex_collection.delete_collection(p_collection_name => 'PATTERN_DATES';
l_query := 'SELECT date_only,
generated_date,
day_of_week,
checked,
instalment_comment
FROM TABLE(l_dates_tbl)';
Apex_collection.create_collection_from_query_b(p_collection_name => 'PATTERN_DATES',
p_query => l_query);
p_query => l_query)
My procedure compiles fine, however when I execute it from within the APEX application I get the following AJAX error and it seems as if l_dates_tbl is not recognise.
Ajax call returned server error ORA-20104: create_collection_from_query_b Error:ORA-20104: create_collection_from_query ParseErr:ORA-00904: "L_DATES_TBL": invalid identifier for Execute PL/SQL Code.
I also tried the following
l_query := 'SELECT date_only,
generated_date,
day_of_week,
checked,
instalment_comment
FROM TABLE(CAST(l_dates_tbl AS dates_tbl))';
But then I get the error:
Ajax call returned server error ORA-20104: create_collection_from_query_b Error:ORA-20104: create_collection_from_query ParseErr:ORA-00902: invalid datatype for Execute PL/SQL Code
Is it then not possible to build the query for an apex collection using an oracle collection? One possible solution I could think of, is just to assign the entire WITH statement to l_query, but that is going to be really hard to maintain and to read.
Any solutions would be greatly appreciated.
Annelize