14.9.4.5 Inspecting the Core 26ai Action Item API

Inspect the 26ai API package that saves JSON through a duality view.

The full code of the ACTION_ITEMS_DV_API package body appears below.

package body action_items_dv_api as

    subtype t_operation is pls_integer range 1..3;
    c_operation_insert      constant t_operation := 1;
    c_operation_update      constant t_operation := 2;
    c_operation_delete      constant t_operation := 3;

    ------------------------------------
    procedure before_save(
        p_action_item in out json,
        p_operation   in     t_operation)       
    is
    begin
        if p_operation = c_operation_insert then
            -- Providing an ID with insert not allowed
            app_common.report_if_error(
                action_items_common.ensure_null_id_for_new_action(
                    json_value(p_action_item,'$."_id"')));
        end if;
        if p_operation in (c_operation_insert,c_operation_update) then
            -- Ensure role values are legal
            for k in (select team_member 
                        from json_table(p_action_item,'$.team[*]' 
                             columns (team_member json path '$')))
            loop
                app_common.report_if_error(
                    action_items_common.is_valid_role(
                        json_value(k.team_member,'$.role')));
            end loop;        
            -- Default role to member if not supplied
            select json_transform(p_action_item,  
                        set '$.team[*]?(@.role == null ||
                                         !exists(@.role)).role' = 'MEMBER')
            into p_action_item;             
        end if;
    end before_save;
    ------------------------------------
    procedure save(
        p_action_item in out json,
        p_operation   in     t_operation)       
    is
    begin
        case p_operation
            when c_operation_insert then
                insert into action_items_dv(data)
                values (p_action_item)
                returning data into p_action_item;
            when c_operation_update then
                update action_items_dv
                   set data = p_action_item
                 where json_value(data,'$."_id"')
                       = json_value(p_action_item,'$."_id"');
            when c_operation_delete then
                delete from action_items_dv
                 where json_value(data,'$."_id"')
                       = json_value(p_action_item,'$."_id"');
        end case;
    end save;
    ------------------------------------
    procedure after_save(
        p_action_item in json,
        p_operation   in t_operation)       
    is
        l_error varchar2(4000);
        l_id number := json_value(p_action_item,'$."_id"');
    begin
        if p_operation in (c_operation_insert,c_operation_update) then    
            app_common.report_if_error(
                action_items_common.no_duplicate_team_members(l_id));
            app_common.report_if_error(
                action_items_common.one_lead_per_action_team(l_id));
        end if;
    end after_save;
    ------------------------------------
    function get_object(
        p_id in number)
    return json
    is
        l_ret json;
    begin
        app_common.report_if_error(
            action_items_common.ensure_action_exists(p_id));    
        select data
          into l_ret
          from action_items_dv
          where json_value(data,'$."_id"') = p_id;
        return l_ret;
    end get_object;
    ------------------------------------    
    function get_objects(
        p_search in varchar2,
        p_offset in number default null,
        p_limit  in number default null)
    return json
    is
        l_offset pls_integer := nvl(p_offset,0);
        l_limit  pls_integer := nvl(p_limit,25);
        l_ret json_object_t := json_object_t();
        l_items json_array_t := json_array_t();
        l_row_count  pls_integer := 0;
        l_total_rows pls_integer := 0;
    begin
        for j in (select json_value(data,'$."_id"') id, 
                         count(*) over() as total_count
                    from action_items_dv
                   where upper(json_value(data,'$.name')) 
                         like upper('%'||p_search||'%')
                   order by upper(json_value(data,'$.name'))
                   offset l_offset rows
                   fetch first l_limit rows only)
        loop
            if l_total_rows = 0 then
                l_total_rows := j.total_count;
            end if;
            l_row_count := l_row_count + 1;
            l_items.append(json_object_t(get_object(j.id)));
        end loop;
        l_ret.put('items',l_items);
        l_ret.put('hasMore', l_offset + l_row_count < l_total_rows);
        l_ret.put('totalRows',l_total_rows);
        return l_ret.to_json;
    end get_objects;    
    ------------------------------------
    function insert_object(
        p_object in json)
    return json
    is
        l_object json := p_object;
    begin
        before_save(l_object,c_operation_insert);
        save(l_object,c_operation_insert);
        after_save(l_object,c_operation_insert);
        return get_object(json_value(l_object,'$."_id"'));
    end insert_object;
    ------------------------------------
    function update_object(
        p_object in json)
    return json
    is
        l_object json := p_object;
    begin
        before_save(l_object,c_operation_update);
        save(l_object,c_operation_update);
        after_save(l_object,c_operation_update);
        return get_object(json_value(l_object,'$."_id"'));
    end update_object;    
    ------------------------------------
    procedure delete_object(
        p_id in number)
    is
        l_id_only_object json_object_t := json_object_t();
        l_object json;
    begin
        l_id_only_object.put('_id',p_id);
        l_object := l_id_only_object.to_json;
        save(l_object,c_operation_delete);
    end delete_object;
end action_items_dv_api;