Thursday, October 31, 2013

Oracle: record vs object

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;