Coordinating the API Helper Code

Coordinate JSON parsing, pre-save logic, table changes, post-save checks, and response generation.

The INSERT_OBJECT, UPDATE_OBJECT, and DELETE_OBJECT procedures coordinate the flow. The insert and update routines have the same sequence. They call:

• ACTION_ITEM_JSON_TO_RECORD to populate the PL/SQL record structures

• BEFORE_SAVE to default and validate anything not requiring aggregate SQL checks

• SAVE to store the action item and its action item team members into tables

• AFTER_SAVE to validate conditions requiring aggregate SQL checks

• GET_OBJECT to return the latest state of the action item to the client.

-- in package action_items_api
function insert_object(
    p_object in json_object_t)
return json_object_t
is
    l_action_item_rec action_items%rowtype;
    l_action_item_team_members t_action_item_team_members;
begin
    **action_item_json_to_record**(
        p_object                   => p_object,
        p_action_item              => l_action_item_rec,
        p_action_item_team_members => l_action_item_team_members);
    **before_save**(l_action_item_rec,l_action_item_team_members,c_operation_insert);
    **save**(l_action_item_rec,l_action_item_team_members,c_operation_insert);
    **after_save**(l_action_item_rec,l_action_item_team_members,c_operation_insert);
    return **get_object**(l_action_item_rec.id);
end insert_object;

Tip: The UPDATE_OBJECT is identical, but passes the c_operation_update constant instead.

The DELETE_OBJECT routine receives no action item JSON, so there is no need to populate PL/SQL records. Instead, it creates an action item record with only the primary key ID field set. While this particular example contains no delete-time before or after save logic, if used it needs to rely only on accessing this primary key value. Finally, since the delete REST API operation requires no response JSON, the DELETE_OBJECT procedure does not call GET_OBJECT at the end.

-- in package action_items_api
procedure delete_object(
    p_id in number)
is
    l_action_item_rec action_items%rowtype;
    l_action_item_team_members t_action_item_team_members;
begin
    l_action_item_rec.id := p_id;
    **before_save**(l_action_item_rec,l_action_item_team_members,c_operation_delete);
    **save**(l_action_item_rec,l_action_item_team_members,c_operation_delete);
    **after_save**(l_action_item_rec,l_action_item_team_members,c_operation_delete);
end delete_object;