Does oracle support nested result sets?

Question

Forgive me if this doesn't make sense. I haven't done SQL in years and years. Getting my feet wet again so please correct me if my assumptions here are wrong.

From what I recall, SQL Server (or perhaps it was an ADO.NET thing) had a neat feature where you could nest a subset of 'child' rows in a parent-child relation along with their corresponding parent row, as a nested result set for that parent row.

For instance, if you had the following three tables...

• Orders (ID, Name)
• Products (ID, Name)
• OrderDetails (Order_ID, Product_ID)

So as an example, say you had ten orders, each with say five items. A standard join would return fifty rows.

What I'm referring to is a feature that would give me back ten rows--one per order--then as you were cursoring through those results, you would ask for the nested results for that row (i.e. essentially a second cursor over just the products in that order.)

Does Oracle have any such feature/capability?

Answer 1

You can use CURSOR expressions:

SELECT id AS orderid,
       CURSOR(
         SELECT p.id,
                p.name
         FROM   OrderedProducts op
                INNER JOIN Products p
                ON ( op.products_id = p.id )
         WHERE  op.orders_id = o.id
       )
FROM   orders o

Cursor expressions are not supported by many interfaces but you should be able to make it work using Java through JDBC and maybe some others.

---

Another alternative is to use Collections and Object types:

CREATE TYPE product_type AS OBJECT(
  id   NUMBER,
  name VARCHAR2(200)
);
/

CREATE TYPE product_table AS TABLE OF product_type;
/

Then:

SELECT id AS Order_Id,
       CAST(
         MULTISET (
           SELECT  product_type( P.id, P.name )
           FROM    OrderDetails OD
           JOIN    Products P
           ON      OD.Product_Id = P.Id
           WHERE   OD.Order_Id   = O.Id
         )
         AS product_table
       ) AS products
FROM   Orders O;

db<>fiddle (db<>fiddle successfully runs the query; although it doesn't know how to display the collection in final result set so it doesn't show any rows)

---

Or you could use XML:

SELECT id AS order_id,
       ( SELECT XMLELEMENT(
                  "Products",
                  XMLAGG(
                    XMLElement(
                      "Product",
                      XMLFOREST(
                        p.id AS "ProductID",
                        p.name AS "ProductName"
                      )
                    )
                  )
                )
         FROM   OrderDetails OD
         JOIN   Products P
         ON     OD.Product_Id = P.Id
         WHERE  OD.Order_Id   = O.Id
       ) AS products
FROM   Orders o

Which outputs:

ORDER_ID | PRODUCTS                                                                                                                                                                                                                                                      
-------: | :-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
       1 | <Products><Product><ProductID>202</ProductID><ProductName>car</ProductName></Product></Products>                                                                                                                                                              
       2 | <Products><Product><ProductID>201</ProductID><ProductName>orange</ProductName></Product><Product><ProductID>202</ProductID><ProductName>car</ProductName></Product><Product><ProductID>203</ProductID><ProductName>airplane</ProductName></Product></Products>

Or JSON:

SELECT id AS order_id,
       ( SELECT JSON_ARRAYAGG(
                  JSON_OBJECT(
                    'id' VALUE p.id,
                    'name' VALUE p.name
                  )
                )
         FROM   OrderDetails OD
         JOIN   Products P
         ON     OD.Product_Id = P.Id
         WHERE  OD.Order_Id   = O.Id
       ) AS products
FROM   Orders o

Which outputs:

ORDER_ID | PRODUCTS                                                                         
-------: | :--------------------------------------------------------------------------------
       1 | [{"id":202,"name":"car"}]                                                        
       2 | [{"id":201,"name":"orange"},{"id":202,"name":"car"},{"id":203,"name":"airplane"}]

db<>fiddle here

Answer 2

Maybe I'm wrong, but - it looks like an ordinary join. See an example:

with 
-- sample data
orders (id, name) as
  (select 1, 'Order 1' from dual union all
   select 2, 'Order 2' from dual 
  ),
products (id, name) as
  (select 200, 'apple' from dual union all
   select 201, 'orange' from dual union all
   select 202, 'car' from dual union all
   select 203, 'airplane' from dual
  ),
orderedproducts (orders_id, products_id) as
  (select 1, 202 from dual union all
   select 2, 201 from dual union all
   select 2, 202 from dual union all
   select 2, 203 from dual
  )
-- query you need (I think)  
select o.id oid, o.name oname, p.id, p.name
from orders o join orderedproducts op on op.orders_id = o.id
              join products p on p.id = op.products_id
where 
  -- get everything for order ID = 2
  o.id = 2
order by o.id, p.id;

        ID NAME            ID NAME
---------- ------- ---------- --------
         2 Order 2        201 orange
         2 Order 2        202 car
         2 Order 2        203 airplane

If that's not it, could you post what you really expect from it? You know - desired result?

---

If you want to skip repeating values from the orders table, that depends on tool you use. In SQL*Plus, you'd BREAK on those columns, e.g.

SQL> break on oid on oname
SQL>
SQL> with
  2  -- sample data
  3  orders (id, name) as
  4    (select 1, 'Order 1' from dual union all
<snip>
 23  where
 24    -- get everything for order ID = 2
 25    o.id = 2
 26  order by o.id, p.id;

       OID ONAME           ID NAME
---------- ------- ---------- --------
         2 Order 2        201 orange
                          202 car
                          203 airplane

SQL>

Or, if you use some reporting tool (such as Oracle Reports Builder or even Apex' Interactive or Classic reports), they also have such a capability.

---

If that's still not "it", is PL/SQL an option for you? Some call it "stored procedures" (though, that's not exactly what it is, but "procedure" is easier to understand than "PL/SQL"). There you have different options, such as cursors and loops.

Answer 3

SQL Server had no such a feature. The only way to return "shaped" data from a SQL Server query is to use XML or JSON as the output.

You can return multiple, separate resultsets from a single batch request or stored procedure call.

So the batch

select * from orders where id = @id
select * from order_details where order_id = @id

will simply return two seperate resultsets, which the client sees in-order.

Oracle does have nested cursors, but I'm not sure how widely used they are or which client APIs support them.

It was a nested result set that you could access through the returned objects. I'm wondering if it was a feature of ADO.NET and not the database.

ADO.NET has the DataSet which stores related data from multiple tables in-memory. But the data is always loaded and written back with separate queries per table. And both ADO.NET DataSets, and the newer .Net data access API Entity Framework work with Oracle.

Going even further back into the dark ages, there was ADO Data Shaping.