Oracle can easily compare data items of scalar data types (those that hold a single data value with no internal components — e.g. NUMBER, DATE or VARCHAR2). So, if a and b are two NUMBER variables, all you need to do to check whether they are the same or not, is test "if (a=b)".
Can we extend this simple equality check to instances of PL/SQL collections or object types (UDTs)? Let’s find out.
{ 3 comments }
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.
{ 2 comments }
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.
{ 0 comments }
Oratable is for Oracle newbies. You'll find here tips, tricks and tutorials about cool things you can do with the Oracle database.
