Saving JSON Using SQL Without Parsing

Save JSON directly through a duality view without parsing it into PL/SQL records.

By using the JSON type and the ACTION_ITEMS_DV duality view, your ACTION_ITEMS_DV_API package no longer needs to parse JSON to populate PL/SQL record structure or manage foreign keys. It also lets you insert, update, and delete the JSON with a single SQL statement. The SAVE procedure becomes simple and elegant as shown below.

-- in package actions_items_dv_api
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;