Previous Next JavaScript must be enabled to correctly display this content

## 18.5.8.10 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;

Parent topic: Working with Oracle Cloud Applications BOSS REST Services