After the post on models for storing XML data in Oracle and examples of binary XML storage, here’s a detailed look at the rigorous and performant structured XML storage or object-relational storage.
Collections
PL/SQL collections are the equivalent of arrays in Oracle. A PL/SQL collection is a composite data type consisting of elements accessible through an index value.
The Smart Way to Check if an Element Exists in a Collection
When you need to find out if a specific element exists in a collection or not, how do you go about it? Do you loop through the list till you find the value?
If that’s what you do, this post is for you.
How to Perform MULTISET Operations on Nested Table of Objects
We’ve seen the easy working of MULTISET operations on nested tables of simple types (i.e. collections of text, numbers). And we’ve seen the problem with extending MULTISET operations to nested tables of complex types (i.e. those based on OBJECTs with multiple attributes).
PL/SQL code when run with MULTISET operations on complex collections throws up the error:
PLS-00306: wrong number or types of arguments in call to 'MULTISET_<operation type>'
There is a workaround though, which lets us use MULTISET operations successfully with complex types. Here’s how.
MULTISET Operations: Combining Nested Tables Made Easy
Set operators (UNION, INTERSECT, MINUS) have long been available in basic SQL to process data in tables, but for data in PL/SQL nested tables, we’d earlier have to go through the ritual of traversing through the collections in a loop, doing a row-by-row comparison.
Oracle 10G onwards, MULTISET features have made possible single-step set operations on nested tables.
Here is a demo with scripts for performing MULTISET operations on nested tables of strings.
An Easy Guide to PL/SQL Collections
A collection is a list of elements of the same type. Each element in the list has a unique position number or label, called the "subscript".
To give a logical analogy, consider a list of colors = {red, blue, green, yellow}. This list has four elements, all names of colors. There is a unique position of each element in the list. If we number the positions sequentially starting from one, we could say:
color[1] = red, color[2] = blue, and so on.
Here, color is the name of the collection, and the numbers within [] are the subscripts.
PL/SQL has three collection types. In this article, we’ll look at a chart for comparing the three, their advantages and limitations, and which one to use for your needs.