While preparing for a whole different topic, I had to create a few demonstrations of using custom SQL Types and Collections in both SQL and PL/SQL. While nothing spectacular – and for anyone who attended our 7Up class pretty familiar stuff – I thought an example of these small demos of how to create and use Type and Table of Type with for example the TABLE operator (to cast the Table of Type Collection into a relationally accessible construct) and the Merge operation as well as the Collect aggregator for producing quickly a Collection from the result of a simple SQL Query, might be useful. If for no one else, than for me – as I always tend to forget the exact syntax.

Note: all scripts are in a file you can download at the end of this article. (more…)