Record version
In package:
TYPE PARAMETER_REC_TYPE IS RECORD (
PARAMETER_ID CRM_DB.HT_CO_ADDON_PARAMETER.PARAMETER_ID%TYPE,
PARAMETER_VALUE CRM_DB.HT_CO_ADDON_PARAMETER.PARAMETER_VALUE%TYPE
);
TYPE PARAMETER_ARRAY_TYPE IS TABLE OF PARAMETER_REC_TYPE;
In package body:
set serveroutput on;
declare
my_param_array PROV_DB.PROV_API.PARAMETER_ARRAY_TYPE;
my_param_record PROV_DB.PROV_API.PARAMETER_REC_TYPE;
g_sysdate date := sysdate;
CURSOR my_rz_addon_cursor(p_co_id CRM_DB.DT_CO.CO_ID%TYPE) is
SELECT 'RZ_ID[]' PARAMETER_ID,
HT_CO_ADDON_PARAMETER.PARAMETER_VALUE
FROM CRM_DB.HT_CO_ADDON,
CRM_DB.HT_CO_ADDON_PARAMETER,
CRM_DB.HT_CO_ADDON_STATUS
WHERE HT_CO_ADDON.CO_ID = p_co_id
AND HT_CO_ADDON.ADDON_ID LIKE 'ROAMING_ZONE_%'
AND HT_CO_ADDON.FROM_DATE <= g_sysdate AND HT_CO_ADDON.TO_DATE > g_sysdate
AND HT_CO_ADDON_PARAMETER.CO_ADDON_ID = HT_CO_ADDON.CO_ADDON_ID
AND HT_CO_ADDON_PARAMETER.FROM_DATE <= g_sysdate AND HT_CO_ADDON_PARAMETER.TO_DATE > g_sysdate
AND HT_CO_ADDON_STATUS.CO_ADDON_ID = HT_CO_ADDON.CO_ADDON_ID
AND HT_CO_ADDON_STATUS.STATUS_ID = CRM_DB.CO_API.STATUS_ADDON_ACTIVE
AND HT_CO_ADDON_STATUS.FROM_DATE <= g_sysdate AND HT_CO_ADDON_STATUS.TO_DATE > g_sysdate;
begin
OPEN my_rz_addon_cursor('000000000941');
FETCH my_rz_addon_cursor BULK COLLECT INTO my_param_array;
CLOSE my_rz_addon_cursor;
my_param_record.parameter_id := 'TOTO';
my_param_record.parameter_value := 'coin coin';
my_param_array.extend;
my_param_array(my_param_array.count) := my_param_record;
my_param_record.parameter_id := 'pim';
my_param_record.parameter_value := '1 2 3';
my_param_array.extend;
my_param_array(my_param_array.count) := my_param_record;
for i in 1..my_param_array.count loop
dbms_output.put_line(my_param_array(i).parameter_id||' = '||my_param_array(i).parameter_value);
end loop;
end;
Object version
As PROV_DB user:
create TYPE PARAMETER_OBJ_TYPE AS object (
PARAMETER_ID varchar2(20),
PARAMETER_VALUE varchar2(256)
);
/
create TYPE PARAMETER_OBJ_ARRAY_TYPE IS TABLE OF PARAMETER_OBJ_TYPE;
/;
GRANT EXECUTE ON PARAMETER_OBJ_TYPE TO public;
GRANT EXECUTE ON PARAMETER_OBJ_ARRAY_TYPE TO public;
In package body:
set serveroutput on;
declare
my_param_array PROV_DB.PARAMETER_OBJ_ARRAY_TYPE;
g_sysdate date := sysdate;
CURSOR my_rz_addon_cursor(p_co_id CRM_DB.DT_CO.CO_ID%TYPE) is
SELECT PROV_DB.PARAMETER_OBJ_TYPE('RZ_ID[]', HT_CO_ADDON_PARAMETER.PARAMETER_VALUE)
FROM CRM_DB.HT_CO_ADDON,
CRM_DB.HT_CO_ADDON_PARAMETER,
CRM_DB.HT_CO_ADDON_STATUS
WHERE HT_CO_ADDON.CO_ID = p_co_id
AND HT_CO_ADDON.ADDON_ID LIKE 'ROAMING_ZONE_%'
AND HT_CO_ADDON.FROM_DATE <= g_sysdate AND HT_CO_ADDON.TO_DATE > g_sysdate
AND HT_CO_ADDON_PARAMETER.CO_ADDON_ID = HT_CO_ADDON.CO_ADDON_ID
AND HT_CO_ADDON_PARAMETER.FROM_DATE <= g_sysdate AND HT_CO_ADDON_PARAMETER.TO_DATE > g_sysdate
AND HT_CO_ADDON_STATUS.CO_ADDON_ID = HT_CO_ADDON.CO_ADDON_ID
AND HT_CO_ADDON_STATUS.STATUS_ID = CRM_DB.CO_API.STATUS_ADDON_ACTIVE
AND HT_CO_ADDON_STATUS.FROM_DATE <= g_sysdate AND HT_CO_ADDON_STATUS.TO_DATE > g_sysdate;
begin
OPEN my_rz_addon_cursor('000000000941');
FETCH my_rz_addon_cursor BULK COLLECT INTO my_param_array;
CLOSE my_rz_addon_cursor;
my_param_array.extend;
my_param_array(my_param_array.count) := PROV_DB.PARAMETER_OBJ_TYPE('TOTO', 'coin coin');
my_param_array.extend;
my_param_array(my_param_array.count) := PROV_DB.PARAMETER_OBJ_TYPE('pim', '1 2 3');
for i in 1..my_param_array.count loop
dbms_output.put_line(my_param_array(i).parameter_id||' = '||my_param_array(i).parameter_value);
end loop;
end;