Skip to content
Advertisement

Does oracle support nested result sets?

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?

Advertisement

Answer

You can use CURSOR expressions:

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:

Then:

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:

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:

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

User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement