Here’s my contribution to the ODC Appreciation Day.
Overview
Last week I had the privilege to participate in the EOUC Database ACES Share Their Favorite Database Things session at Oracle OpenWorld, so I think that the best topic to write about, as part of the ODC Appreciation Day, is the one I talked about in this session.
My 5-minute presentation was about Collections in SQL.
Collections are very useful in PL/SQL development. This is a well-known fact, and collections are indeed used a lot by PL/SQL developers.
But it is less known that collections can be very useful also in SQL statements, not only in PL/SQL. The Oracle database supports user-defined collection types (Nested Tables and Varrays) and built-in functions that operate on collections, and they add yet more power to the already-powerful SQL language.
Examples
Here are some links to blog posts that I’ve written in the past, in which I used collections in SQL.
This post includes an example for a PL/SQL procedure that gets a collection parameter as input and needs to perform some manipulation on it. The implementation of this procedure becomes easy using Collection Unnesting – simply writing a SQL statement (MERGE in this case), that treats the collection parameter as if it were a simple table.
In this post we take advantage of the PowerMultiSet built-in function for creating a Kakuro cheat-sheet.
In this post we observe subtle differences between the two types of collections – Nested Tables and Varrays – with regard to the SQL functions that can be applied on them.
This post shows how sometimes collection functions such as SET and CARDINALITY enable solving a problem in a more concise and simple way than the alternatives.
And here is a simple solution for some problem that was asked on Ask TOM, using the SQL function with probably the longest name – POWERMULTISET_BY_CARDINALITY.