14.8.2.3.1 Populating PL/SQL Records from JSON

Parse action item JSON into PL/SQL records for the parent row and team members.

The package passes action item data around in a PL/SQL record variable of type ACTION_ITEMS%ROWTYPE. Similarly, it passes action item team member data around in a T_ACTION_ITEM_TEAM_MEMBERS type, defined as a table of ACTION_ITEM_TEAM_MEMBERS%ROWTYPE. The ACTION_ITEM_JSON_TO_RECORD procedure populates the PL/SQL record structures with the action item JSON payload data, using built-in functions on the JSON_OBJECT_T and JSON_ARRAY_T types to access the data.

-- in package action_items_api
procedure action_item_json_to_record(
    p_object        in json_object_t,
    p_action_item  out action_items%rowtype,
    p_action_item_team_members out t_action_item_team_members)
is
    l_team_members_arr json_array_t;
    l_team_member_obj  json_object_t;
    l_team_member_rec  action_item_team_members%rowtype;
    l_team_member_name staff.name%type;
begin
    p_action_item.id     := p_object.get_number('_id');
    p_action_item.name   := p_object.get_string('name');
    p_action_item.status := p_object.get_string('status');
    p_action_item_team_members := t_action_item_team_members();
    l_team_members_arr := p_object.get_array('team');
    for j in 0..l_team_members_arr.get_size - 1 loop
        -- Fill in foreign key attribute from parent
        l_team_member_rec := null;
        l_team_member_rec.action_id := p_action_item.id;
        l_team_member_obj := treat(l_team_members_arr.get(j) as json_object_t);
        l_team_member_rec.id      := l_team_member_obj.get_number('team_assignment_id');
        l_team_member_rec.user_id := l_team_member_obj.get_string('staff_id');
        -- if user_id is null, try to use name to look it up since name is unique
        if l_team_member_rec.user_id is null then
            l_team_member_name := l_team_member_obj.get_string('name');
            if l_team_member_name is not null then
                begin
                    select id
                      into l_team_member_rec.user_id
                      from staff
                     where name = l_team_member_name;
                exception
                    when no_data_found then
                        app_common.report_error_key(
                            'TEAM_MEMBER_NOT_FOUND',
                            apex_t_varchar2('name',l_team_member_name));
                end;
            end if;
        end if;
        l_team_member_rec.role := substr(l_team_member_obj.get_string('role'),1,6);
        p_action_item_team_members.extend;
        p_action_item_team_members(p_action_item_team_members.count) 
            := l_team_member_rec;
    end loop;
end action_item_json_to_record;