Dynamic Table Type and Bulk Collection

Here the V_sql gets any sql. It will go into collection from collection how we are getting into table using bulk collection and Dynamic Table type .

DECLARE


TYPE t_cols IS TABLE OF VARCHAR2(100);

TYPE t_rows IS TABLE OF t_cols; -- table of table elements

TYPE t_ref_cur IS REF CURSOR;
c1 t_ref_cur;
vntb1 t_rows := t_rows(t_cols());
v_fields VARCHAR2(4000);
n_fields NUMBER;
n_rows NUMBER;
v_tmp VARCHAR(100);
n_itemid NUMBER := :P41_ITEM_ID;
v_sql VARCHAR2(4000) := pkg_daily_quality.dq_ln_vals_sql(n_itemid);
i INTEGER;
v_col_nm VARCHAR2(25) := 'DQI_'
n_itemid;
a_pos INTEGER;
v_msp number;
v_dq number;

V_tot_cols number;

BEGIN
IF APEX_COLLECTION.COLLECTION_EXISTS (v_col_nm)
THEN
APEX_COLLECTION.DELETE_COLLECTION (v_col_nm);
END IF;

APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY(v_col_nm,v_sql );
n_rows := APEX_COLLECTION.COLLECTION_MEMBER_COUNT(v_col_nm);
--htp.p('number of rows in collection: '
n_rows
'
');
v_fields := SUBSTR(v_sql,46,INSTR(v_sql, ' from')-46);
v_fields := ','
REPLACE(REPLACE(REPLACE(v_fields,'L.'),'I.'),'T.')
',';
n_fields := LENGTH(v_fields)-LENGTH(REPLACE(v_fields,','))+1;
--htp.p(v_sql
'
');

--htp.p('
starting field population...
');

vntb1.EXTEND(n_fields-1,1);

FOR f IN 1..n_fields
LOOP
a_pos := 2;
v_sql := 'SELECT c'
lpad(f,3,0)

'
FROM APEX_collections
WHERE collection_name = '''
v_col_nm
'''';
--htp.p(v_sql);

execute immediate v_sql bulk collect into vntb1(f);

vntb1(f).EXTEND;

END LOOP;

-- POPULATE FIRST ROW OF 2D ARRAY WITH FIELD NAMES

FOR f IN 1..n_fields
LOOP
vntb1 (f) (n_rows+1) := 'SOURCE';
elsif f= 2 then
vntb1 (f) (n_rows+1) := 'LOAD_DT';
else
v_tmp := SUBSTR (v_fields, 2, INSTR (v_fields, ',',2) - 2);

vntb1 (f) (n_rows+1) := v_tmp;

v_fields :=REPLACE (v_fields, ',' v_tmp ',',',');
end if;

-- for i IN 1..vntb1(f).count
-- loop
-- htp.p('
'i':'vntb1(f)(i));
-- end loop;

END LOOP;

--HTP.p ('before msp ....');

v_sql := 'select count(*) from APEX_collections WHERE collection_name = ''' v_col_nm ''' and c001 = ''1'' ';
execute immediate v_sql into v_msp;

--HTP.p ('after msp ....');

v_sql := 'select count(*) from APEX_collections

WHERE collection_name = ''' v_col_nm''' and c001 = ''2'' ';


execute immediate v_sql into v_dq;
V_tot_cols := n_rows +1;

HTP.p ('
<><><>'); HTP.p (' <><><>'); for i IN 2..n_fields loop htp.p (' <><><>'); for j IN 1..n_rows loop if i = 2 then htp.p (' <><><>'); else htp.p (' <><><>'); end if; end loop; htp.p(' <><><>'); end loop; APEX_COLLECTION.DELETE_COLLECTION (v_col_nm); htp.p('

Fields	MSP Yesterday	MSP Boarded
'initcap(replace(vntb1(i)(n_rows+1), '_', ' '))'	'to_char(to_date(vntb1(i)(j), 'DD-MON-YY'), 'MM/DD/YYYY')'	'vntb1(i)(j)'

');
END;