14.8.2.2 Retrieving Action Items with Paging

Retrieve matching action items in a predictable order and return paged JSON results.

The GET_OBJECTS procedure accepts a search string, offset, and limit as parameters as shown below. Using a cursor FOR loop, it select the action item's primary key ID column. Interesting things to notice in the loop's query include:

• COUNT(*) OVER() analytic function computes the total number of matching rows
• WHERE clause filters action item name using search criteria (matching anything by default)
• ORDER BY UPPER(NAME) guarantees predictable order for paging
• OFFSET … ROWS skips indicated number of rows (skipping 0 by default)
• FETCH FIRST … ROWS retrieves up to indicated row limit page size (25 by default).

Inside the loop, for each matching action item it calls GET_OBJECT to generate the JSON action item to add to an items array. This ensures consistent results across contexts and keeps the JSON construction logic in one place. The final JSON object GET_OBJECTS returns includes these properties:

• items array containing the matching action item objects
• hasMore boolean indicating whether there are more rows to return
• totalRows number indicating how many action items the client could retrieve.

-- in package action_items_api
function get_objects(
    p_search in varchar2,
    p_offset in number default null,
    p_limit  in number default null)
return json_object_t
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 id, count(*) over() as total_count
                from action_items
               where upper(name) like upper('%'||p_search||'%')
               order by upper(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(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;
end get_objects;