In oracle apex popup page, I created collection.Based on the table name, It will show the data.
In **Create Collection,**I written below code.
declare
L_COLLECTION_NAME APEX_COLLECTIONS.COLLECTION_NAME%TYPE;
allColumns VARCHAR2(4000);
exceedColumnFlag VARCHAR2(50);
l_vc_arr2 APEX_APPLICATION_GLOBAL.VC_ARR2;
TNAME VARCHAR2(100);
l_query VARCHAR2(4000);
rcount number;
l_pgm_count number;
l_SELECTED_DATA varchar2(1200);
begin
SELECT :P28_TABLE_NAME INTO TNAME FROM DUAL;
L_COLLECTION_NAME:='DYNAMIC_REPORT';
IF APEX_COLLECTION.COLLECTION_EXISTS(L_COLLECTION_NAME) THEN
APEX_COLLECTION.DELETE_COLLECTION(P_COLLECTION_NAME => L_COLLECTION_NAME);
END IF;
select listagg('"' || column_name || '"',',') WITHIN GROUP (order by column_name) INTO allColumns from user_tab_cols where upper(table_name) = upper(TNAME) AND upper('"' || COLUMN_NAME || '"')<>upper(:P28_COL_NAME);
allColumns:=:P28_COL_NAME||','||allColumns;
SELECT COUNT(*) INTO RCOUNT FROM user_tab_cols where upper(table_name) = upper(TNAME);
IF RCOUNT>50 THEN
select SUBSTR(allColumns,1,(INSTR(allColumns,',',1,49)-1)) into allColumns from dual;
END IF;
l_vc_arr2 := APEX_UTIL.STRING_TO_TABLE(allColumns,',');
FOR z IN 1..50 LOOP
APEX\_UTIL.set\_session\_state(p\_name => 'P28\_COL\_HEAD\_C0'||lpad(z,2,0), p\_value => '');
END LOOP;
FOR z IN 1..l_vc_arr2.count LOOP
APEX\_UTIL.set\_session\_state(p\_name => 'P28\_COL\_HEAD\_C0'||lpad(z,2,0), p\_value => replace(l\_vc\_arr2(z),'"',''));
END LOOP;
Commit;
:P28_DEFAULT_COL:= allColumns;
L_QUERY :='SELECT UNIQUE ' || allColumns || ' FROM ' || TNAME;
IF :P28_WHERE_CLAUSE IS NOT NULL THEN
IF :P28_SELECTED_DATA IS NOT NULL THEN
L\_QUERY := L\_QUERY || ' where ' || :P28\_WHERE\_CLAUSE|| ' order by field';
ELSE
L\_QUERY := L\_QUERY || ' where ' || :P28\_WHERE\_CLAUSE|| ' order by 1';
END IF;
ELSE
IF :P28_SELECTED_DATA IS NOT NULL THEN
L\_QUERY := L\_QUERY || ' order by field';
ELSE
L\_QUERY := L\_QUERY || ' order by 1';
END IF;
END IF;
EXECUTE IMMEDIATE L_QUERY;
APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY_B (
p\_collection\_name => L\_COLLECTION\_NAME,
p\_query => L\_QUERY);
END;
But one of table is having more than one 1 million records.When I opened dynamic popup page, Page loading very slow.For few seconds please wait icon showing.After it is showing white screen.How to show the please wait icon until content is fully loaded.