Working with Collections

This section shows a example of working with multi level collection:


CREATE OR REPLACE TYPE APP_USER.V_DATE_OBJ as object (v_date date);

CREATE OR REPLACE TYPE APP_USER.V_DATE_TAB as table of V_DATE_OBJ;

CREATE OR REPLACE TYPE HOLIDAY_INFO_OBJ AS OBJECT (
payee_id NUMBER (11),
holiday_model_id NUMBER (11),
start_date DATE,
end_date DATE,
saturday_holiday VARCHAR2 (10),
sunday_holiday VARCHAR2 (10),
holidays v_date_tab
);

CREATE OR REPLACE TYPE V_HOLIDAY_INFO_TAB IS TABLE OF HOLIDAY_INFO_OBJ;

********************************************************************************

DECLARE
v_holiday_date_tab v_date_tab;
v_payee_id NUMBER (11);
v_paym_account_id NUMBER (11);
v_holiday_model_id NUMBER (11);
v_start_date DATE;
v_end_date DATE;
l_holiday_info_tab v_holiday_info_tab := v_holiday_info_tab ();
BEGIN
l_holiday_info_tab.EXTEND;

SELECT v_date_obj (holiday_date)
BULK COLLECT INTO v_holiday_date_tab
FROM financial_holiday;

SELECT p.payee_id,
paym.paym_account_type_id,
DECODE (paym.paym_account_type_id,
1, DECODE (bp.check_holiday_model_id, 2, 2, 1),
2, 2
) holiday_model,
TO_DATE (TO_CHAR (TRUNC (SYSDATE, 'YYYY'), 'dd/mm/yyyy'),
'dd/mm/yyyy'
) start_date,
LAST_DAY (ADD_MONTHS (TRUNC (SYSDATE, 'YYYY'), 11)) end_date
INTO v_payee_id,
v_paym_account_id,
v_holiday_model_id,
v_start_date,
v_end_date
FROM payee p, paym_account paym, usl_service usl, biller_param bp
WHERE payee_id = 10146579
AND p.default_paym_account_id = paym.paym_account_id
AND p.usl_service_id = usl.usl_service_id
AND usl.sum_info_id = bp.sum_info_id(+);

l_holiday_info_tab (1) :=
holiday_info_obj (v_payee_id,
v_holiday_model_id,
v_start_date,
v_end_date,
'true',
'true',
v_holiday_date_tab
);
DBMS_OUTPUT.put_line (l_holiday_info_tab.COUNT);
DBMS_OUTPUT.put_line (l_holiday_info_tab (1).holidays.COUNT);
DBMS_OUTPUT.put_line (l_holiday_info_tab (1).holidays (25).v_date);
END;
/