Collections in 12c

Sometimes the best way to see the familiar is to go far from it – Wisdom of the desert In previous articles (collections I and collections II) I described the different types of collections, their possibilities and their limits. One of the limits was the possibility to use an Associative Array in a SQL statement. If you are building your

Sometimes the best way to see the familiar is to go far from it
– Wisdom of the desert

In previous articles (collections I and collections II) I described the different types of collections, their possibilities and their limits. One of the limits was the possibility to use an Associative Array in a SQL statement. If you are building your code from scratch, it’s easy enough to make the right choice, but if you are working with existing code and you need to make adjustment and/or you are not allowed to create type objects in the database, and still want to use the SQL power the database offers, since version 12c you can now use Associative Arrays to solve your problem.

If you have a rather large associative array in your program and you want this sorted one way or another you have a couple of options.

  • Write your own sort method. This is a rather cumbersome and complex option and chances are you will not get this to work flawlessly in the first couple of tries.
  • Copy all the records to a new collection using the field you want to sort on as the index. A problem might be duplicate index values. Using this method you will loose any duplicates. Check the results of the following script, displaying what happens when you have duplicates:

    Notice the number of records still available when sorting by salary. It is only 12 instead of the expected 14. This happens because there are duplicates and since we are using the SAL column as an index value, duplicates get overwritten. Only the last one survives.

  • Insert the records into a physical table, preferably a global temporary table since you won’t get the overhead of redo logs etc., and use SQL to sort the records. Of course you will use the bulk capabilities of the FORALL statement to insert the records in a single pass.

    First create a global temporary table to hold our data.

    Then check the following script:

    Notice that there are always the expected 14 records available. Of course you would run an insert-as-select statement to fill up the global temporary table, but for keeping the code similar in all scripts it is done this way.

  • Copy your data to a Nested Table and use that in a SQL statement. First make sure there is an object type and a nested table defined in the database:

    Then check out this code to see what needs to be done to sort the data:

    These last two approaches only work when you are allowed to create objects ((GLOBAL TEMPORARY) TABLES and TYPES) in the database.

With Oracle 12c you now have the possibility to use an Associative Array in a SQL statement where this was not possible before. There are a couple of things you should take into account when using this technique. First of all, the Associative Array should be declared in a package body. The Associative Array must be of a scalar type, a rowtype of an object in the database or an object type defined at schema level. You cannot base the Associative Array on a Record Type defined in your PL/SQL code.

By using either of these approaches you let the database do all the heavy lifting for you. Chances that you will write a better and faster sort algorithm than the one(s) already in the database are probably zero. But if you do, you are probably soon to be employed by Oracle.

Ref:
http://docs.oracle.com/database/121/LNPLS/release_changes.htm#LNPLS111