When using BOSS REST Data Sources based on business object endpoints with form and interactive grid regions, you can also insert, update, and delete data. None of these capabilities requires writing any code to achieve, however sometimes you may need to interact with a BOSS endpoint programmatically using the APEX_EXEC package. Use cases include:

• Creating pipelined table functions that programmatically reshape BOSS data.
• Working programmatically with BOSS endpoints in APEX business logic, workflows, automations, or background execution chain processes.
• Arbitrary scripting tasks involving interacting with BOSS endpoints.

Each BOSS REST data source has a unique identifier that APEX calls its static id. For example, an APEX developer creating a REST Data Source for a BOSS endpoint ../v1/opportunities might configure its static id to be opportunities. This is the identifier you use when working with the functions in the APEX_EXEC package.

Retrieving Data from a BOSS Endpoint

To retrieve data from a BOSS endpoint, use the apex_exec.open_rest_source_query function to create an apex_exec.t_context object that works like a cursor, then use a while apex_exec.next_row(ctx) loop to iterate through the results. For each iteration, use the apex_exec.get_xxx() functions to access the attribute values from the current query result row. When done, call apex_exec.close() to close the context. For example:

declare
  l_ctx apex_exec.t_context;
  -- other declarations here
begin
  -- Process top five winnable opportunties for salesperson 12345 by amount
  -- get_opportunities_columns() returns apex_exec.t_columns for columns
  -- corresponding to fields: optyId,amount,customerName,closeDate
  l_ctx := apex_exec.open_rest_source_query(
             p_static_id              => 'opportunities',
             p_columns                => get_opportunities_columns,
             p_external_filter_expr   => 'resourceId = 12345 and winPct > 0.80',
             p_external_order_by_expr => 'amount:desc'
             p_max_rows               => 5);
  while apex_exec.next_row(l_ctx) loop
     l_amount := apex_exec.get_number(l_ctx,'AMOUNT');
     l_customer := apex_exec.get_varchar2(l_ctx,'CUSTOMERNAME');
     l_closes := apex_exec.get_date(l_ctx,'CLOSEDATE');
     -- Work with data here...
  end loop;
  apex_exec.close(l_ctx);
exception
  when others then
     apex_exec.close(l_ctx);
     raise;
end;

Inserting Data Using a BOSS Endpoint

To insert data using a BOSS endpoint, use the apex_exec.open_rest_source_dml_context function to create an apex_exec.t_context object that works like a temporary rowset. Then for each new row you want to add, call apex_exec.add_dml_row() and then use apex_exec.set_value() to set the value of each attribute necessary. Finally, to process the pending changes call apex_exec.execute_dml(). When done, call apex_exec.close() to close the context. For example:

declare
  l_ctx apex_exec.t_context;
  -- other declarations here
begin
  -- Insert two new opportunities
  -- get_opportunities_columns() returns apex_exec.t_columns for columns
  -- corresponding to fields: APEX$ResourceKey,optyId,amount,customerName,closeDate
  l_ctx := apex_exec.open_rest_source_dml_context(
             p_static_id             => 'opportunities',
             p_columns               => get_opportunities_columns,
             p_lost_update_detection => apex_exec.c_lost_update_none);
  apex_exec.add_dml_row(l_ctx, apex_exec.c_dml_operation_insert);
  apex_exec.set_value(l_ctx,'AMOUNT',45000);
  apex_exec.set_value(l_ctx,'CUSTOMERNAME','Tesla');
  apex_exec.set_value(l_ctx,'CLOSEDATE','2024-05-11');
  apex_exec.add_dml_row(l_ctx, apex_exec.c_dml_operation_insert);
  apex_exec.set_value(l_ctx,'AMOUNT',1500000);
  apex_exec.set_value(l_ctx,'CUSTOMERNAME','Apple');
  apex_exec.set_value(l_ctx,'CLOSEDATE','2024-06-30');
  -- Save the changes
  apex_exec.execute_dml(l_ctx);
  apex_exec.close(l_ctx);
exception
  when others then
     apex_exec.close(l_ctx);
     raise;
end;

Updating Existing Row Using a BOSS Endpoint

To update data using a BOSS endpoint, use the apex_exec.open_rest_source_dml_context function to create an apex_exec.t_context object that works like a temporary rowset. Then for each row you want to update, call apex_exec.add_dml_row() with the update operation constant and then use apex_exec.set_value() to set the value of each attribute necessary. Finally, to process the pending changes call apex_exec.execute_dml(). Often you will preface this with a query to retrieve the row first to consider its existing values, before updating some of them. In that case you can call apex_exec.set_values() to copy all the row values from the queried row into the row to be updated. For example:

declare
  l_ctx apex_exec.t_context;
  l_dml_ctx apex_exec.t_context;
  -- other declarations here
begin
  -- Retrieve and update opportunity with resource key 98765
  -- to increase its amount by 10% and close date by one week
  -- get_opportunities_columns() returns apex_exec.t_columns for columns
  -- corresponding to fields: optyId,amount,customerName,closeDate
  l_ctx := apex_exec.open_rest_source_query(
            p_static_id               => 'opportunities',
            p_columns                 => get_opportunities_columns,
            p_external_filter_expr    => 'optyId = 98765'
            p_max_rows                => 1);
  if not apex_exec.next_row(l_ctx) then
    raise_application_error(-20001,'Opportunity 98765 not found');
  end if;
  l_dml_ctx := apex_exec.open_rest_source_dml_context(
                 p_static_id             => 'opportunities',
                 p_columns               => get_opportunities_columns,
                 p_lost_update_detection => apex_exec.c_lost_update_implicit);
  apex_exec.add_dml_row(l_ctx, apex_exec.c_dml_operation_update);
  apex_exec.set_row_version_checksum(
    p_context   => l_dml_context,
    p_checksum  => apex_exec.get_row_version_checksum(l_ctx));
  -- Copy the values of the fetched opportunity into the update row
  apex_exec.set_values(
      p_context         => l_dml_ctx,
      p_source_context  => l_ctx );
  -- Adjust the amount and closeDate appropriately
  apex_exec.set_value(l_dml_ctx,'AMOUNT',
                      apex_exec.get_number(l_dml_ctx,'AMOUNT') * 1.1);
  apex_exec.set_value(l_dml_ctx,'CLOSEDATE',
                      apex_exec.get_date(l_dml_ctx,'CLOSEDATE') + 7);
  -- Save the changes
  apex_exec.execute_dml(l_dml_ctx);
  apex_exec.close(l_dml_ctx);
  apex_exec.close(l_ctx);
exception
  when others then
    apex_exec.close(l_dml_ctx);
    apex_exec.close(l_ctx);
      raise;
end;

Deleting an Existing Row Using a BOSS Endpoint

To delete data using a BOSS endpoint, use use the apex_exec.open_rest_source_dml_context function to create an apex_exec.t_context object that works like a temporary rowset. Then for each row you want to delete, call apex_exec.add_dml_row() with the delete operation constant and then use apex_exec.set_value() to set the value of the resource key column APEX$RESOURCEKEY. Finally, to process the pending changes call apex_exec.execute_dml(). For example:

declare
  l_dml_ctx apex_exec.t_context;
  -- other declarations here
begin
  -- Delete opportunity with resource key 98765
  -- get_resource_key_columns returns apex_exec.t_columns for columns
  -- corresponding to fields: APEX$ResourceKey
  l_dml_ctx := apex_exec.open_rest_source_dml_context(
                 p_static_id => 'opportunities',
                 p_columns => get_resource_key_columns,
                 p_lost_update_detection => apex_exec.c_lost_update_implicit);
  apex_exec.add_dml_row(l_dml_ctx, apex_exec.c_dml_operation_delete);
  -- Set the resource key of the row to be deleted
  apex_exec.set_value(l_dml_ctx,'APEX$RESOURCEKEY', '98765');
  -- Save the changes
  apex_exec.execute_dml(l_dml_ctx);
  apex_exec.close(l_dml_ctx);
exception
   when others then
     apex_exec.close(l_dml_ctx);
      raise;
end;