Reading Business Objects with Nested Collections

Define REST Data Source profile columns of type Array to work programmatically with nested collections of BOSS business objects.

When defining an array column, use the relative path from the parent array for the Selector. In the Remote Attribute Name field, enter the full path from the root if that is different from the selector. Then, you can use the APEX_EXEC package to work with the nested arrays by using the open_array() and close_array() functions to set the context of the current array to work with. :

For example, suppose you are working with an Order business object, that has a composed collection of OrderLineItem business objects, each of which has a composed collection of OrderLineFulfillment objects. The following tables shows some example data profile columns from an Orders with Details REST Data Source with static id orders_with_details:

Column Name / Parent Column	Data Type	Selector / Remote Attribute Name
ORDER_STATUS	VARCHAR2	orderStatus
TOTAL_AMOUNT	NUMBER	totalAmount
ORDERLINES	Array	orderLines.items
ORDERLINES_QUANTITY ORDERLINES	NUMBER	quantity orderLines.quantity
ORDERLINES_PRODUCTNAME ORDERLINES	VARCHAR2	product.productName orderLines.product.productName
FULFILLMENTS ORDERLINES	Array	fulfillments.items orderLines.fulfillments.items
FULLFILLMENTS_QUANTITY FULFILLMENTS	NUMBER	quantity orderLines.fulfillments.quantity
FULLFILLMENTS_WAREHOUSE FULFILLMENTS	VARCHAR2	warehousedProduct.warehouse.name orderLines.fulfillments.warehousedProduct.warehouse.name

To retrieve the three-level hierarchy of data for an order whose id is passed to a get_order() function, write code like the following example function.

function get_order_exec(
   p_order_id in number)
   return t_order
is
   l_order t_order;
   l_ctx apex_exec.t_context;
   l_additional varchar2(255);
   l_cur_line t_order_line;
   l_ord_line pls_integer;
   l_ful_line pls_integer;
begin
   -- get_order_columns() returns apex_exec.t_columns for all fields
   l_ctx := apex_exec.open_rest_source_query(
      p_static_id               => 'orders_with_details',
      p_columns                 => get_order_columns,
      p_external_filter_expr    => 'orderId = '||p_order_id);
   if apex_exec.next_row(l_ctx) then
      l_order.order_id := apex_exec.get_number(l_ctx, 'ORDERID');
      l_order.tax_amount := apex_exec.get_number(l_ctx, 'TAXAMOUNT');
      l_order.total_amount := apex_exec.get_number(l_ctx, 'TOTALAMOUNT');
      l_order.order_date := apex_exec.get_date(l_ctx, 'ORDERDATE');
      l_order.order_status := apex_exec.get_varchar2(l_ctx, 'ORDERSTATUS');
      l_order.customer_first_name := apex_exec.get_varchar2(l_ctx, 'CUSTOMER_FIRSTNAME');
      l_order.customer_last_name := apex_exec.get_varchar2(l_ctx, 'CUSTOMER_LASTNAME');
      l_order.customer_phone := apex_exec.get_varchar2(l_ctx, 'CUSTOMER_PHONE');
      l_order.customer_email := apex_exec.get_varchar2(l_ctx, 'CUSTOMER_EMAILADDRESS');
      l_order.customer_loyalty_level := apex_exec.get_varchar2(l_ctx, 'CUSTOMER_LOYALTYLEVEL');
      l_additional := apex_exec.get_varchar2(l_ctx, 'SHIPTO_ADDITIONAL');
      l_order.ship_address := apex_exec.get_varchar2(l_ctx, 'SHIPTO_ADDRESS')
                            || case when l_additional is not null then ', ' end
                            || l_additional;
      l_order.ship_city := apex_exec.get_varchar2(l_ctx, 'SHIPTO_CITY');
      l_order.ship_state := apex_exec.get_varchar2(l_ctx, 'SHIPTO_STATE');
      l_order.ship_zip := apex_exec.get_varchar2(l_ctx, 'SHIPTO_ZIP');
      -- open & loop over Order Lines for current order
      apex_exec.open_array(l_ctx,'ORDERLINES');
      while apex_exec.next_array_row(l_ctx) loop
          l_ord_line := l_order.order_lines.count + 1;
          l_order.order_lines(l_ord_line).quantity
               := apex_exec.get_number(l_ctx, 'ORDERLINES_QUANTITY');
          l_order.order_lines(l_ord_line).list_price
               := apex_exec.get_number(l_ctx, 'ORDERLINES_LISTPRICE');
          l_order.order_lines(l_ord_line).amount
               := apex_exec.get_number(l_ctx, 'ORDERLINES_AMOUNT');
          l_order.order_lines(l_ord_line).product_name
               := apex_exec.get_varchar2(l_ctx, 'ORDERLINES_PRODUCTNAME');
          -- open & loop over Order Line Fulfillments for current order line
          apex_exec.open_array(l_ctx,'FULFILLMENTS');
          while apex_exec.next_array_row(l_ctx) loop
              l_ful_line := l_order.order_lines(l_ord_line).fulfillments.count + 1;
              l_order.order_lines(l_ord_line).fulfillments(l_ful_line).quantity
                  := apex_exec.get_number(l_ctx, 'FULFILLMENTS_QUANTITY');
              l_order.order_lines(l_ord_line).fulfillments(l_ful_line).warehouse_name
                  := apex_exec.get_varchar2(l_ctx, 'FULFILLMENTS_WAREHOUSE');
          end loop;
          apex_exec.close_array(l_ctx);
      end loop;
      apex_exec.close_array(l_ctx);
  end if;
  apex_exec.close(l_ctx);
  return l_order;
end get_order_exec;

The code above references a get_order_columns() function shown below. It returns the apex_exec.t_columns table of apex_exec.t_column records defining the columns participating in the query:

function get_order_columns
return apex_exec.t_columns
is
    l_columns apex_exec.t_columns;
begin
    apex_exec.add_column( /* orderId */
        p_columns         => l_columns,
        p_column_name     => 'ORDERID',
        p_data_type       => apex_exec.c_data_type_number);
    apex_exec.add_column( /* orderDate */
        p_columns         => l_columns,
        p_column_name     => 'ORDERDATE',
        p_data_type       => apex_exec.c_data_type_date);
    -- etc. (other top-level columns here)
    apex_exec.add_column( /* orderLines.items */
        p_columns         => l_columns,
        p_column_name     => 'ORDERLINES',
        p_data_type       => apex_exec.c_data_type_array);
    apex_exec.add_column( /* orderLines.product.productName */
        p_columns         => l_columns,
        p_column_name     => 'ORDERLINES_PRODUCTNAME',
        p_data_type       => apex_exec.c_data_type_varchar2,
        p_parent_column_path => '"ORDERLINES"');
    apex_exec.add_column( /* orderLines.quantity */
        p_columns         => l_columns,
        p_column_name     => 'ORDERLINES_QUANTITY',
        p_data_type       => apex_exec.c_data_type_number,
        p_parent_column_path => '"ORDERLINES"');
    apex_exec.add_column( /* orderLines.listPrice */
        p_columns         => l_columns,
        p_column_name     => 'ORDERLINES_LISTPRICE',
        p_data_type       => apex_exec.c_data_type_number,
        p_parent_column_path => '"ORDERLINES"');
    apex_exec.add_column( /* orderLines.amount */
        p_columns         => l_columns,
        p_column_name     => 'ORDERLINES_AMOUNT',
        p_data_type       => apex_exec.c_data_type_number,
        p_parent_column_path => '"ORDERLINES"');
    apex_exec.add_column( /* orderLines.fulfillments.items */
        p_columns         => l_columns,
        p_column_name     => 'FULFILLMENTS',
        p_data_type       => apex_exec.c_data_type_array,
        p_parent_column_path => '"ORDERLINES"');
    apex_exec.add_column( /* orderLines.fulfillments.warehousedProduct.warehouse.name */
        p_columns         => l_columns,
        p_column_name     => 'FULFILLMENTS_WAREHOUSE',
        p_data_type       => apex_exec.c_data_type_varchar2,
        p_parent_column_path => '"ORDERLINES"."FULFILLMENTS"');
    apex_exec.add_column( /* orderLines.fulfillments.quantity */
        p_columns         => l_columns,
        p_column_name     => 'FULFILLMENTS_QUANTITY',
        p_data_type       => apex_exec.c_data_type_number,
        p_parent_column_path => '"ORDERLINES"."FULFILLMENTS"');
    return l_columns;
end get_order_columns;