Hi,
In one of my APEX reports, the list of values used in the SELECT query are populated through an APEX_COLLECTION.
This is the report query:
select * from bugs_info
where bug_id in
(SELECT c001 FROM apex_collections
WHERE collection_name = 'BUGS_COLL');
The BUS_COLL value is derived through an PLSQL (defined at PAGE_LOAD event)
declare
array apex_application_global.vc_arr2;
v_preflight_num number;
v_all_bugs varchar2(250);
begin
select PREFLIGHT_NUMBER into v_preflight_num
from PREFLIGHT_HISTORY
where PREFLIGHT_PK_ID = :P25_PREFLIGHT_PK_ID;
select listagg(related_bugs , ', ') within group (order by related_bugs ) all_bugs
into v_all_bugs
from preflight_overall_status
where preflight_number = v_preflight_num;
array := apex_util.string_to_table (v_all_bugs,',');
apex_collection.create_or_truncate_collection ('BUGS_COLL');
apex_collection.add_members ('BUGS_COLL', array);
end;
The issue i'm facing is, every time during the PAGE_LOAD, the value of the apex_collection BUGS_COLL is null.
Hence the report is returning NO_DATA for the first page load.
But if i reload/submit my page again, the BUGS_COLL value is getting derived correctly & my report is showing the records.
How can i resolve this issue & get the value of my APEX_COLLECTION populated correctly during the very first page_load itself ??
Please help/advice.
Thanks,
Sachin