Previous Next JavaScript must be enabled to correctly display this content

## 18.5.8.8 Using BOSS REST Data Sources in PL/SQL

Use BOSS REST Data Sources to show data in any APEX region

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;

Note:

If you need to first fetch the row to reason over its attributes before deciding to delete it, then follow the update example above but use the delete operation instead of the update operation when adding the DML row. Finally, to process the pending changes call `apex_exec.execute_dml()`.

Parent topic: Working with Oracle Cloud Applications BOSS REST Services