The ability of using SQL to operate on Associative Arrays or PL/SQL tables as they were known when I started working as a Database Developer is one of my favourite new features of 12c. I wasn’t aware of it until I read the post by Connor Mcdonald.

It’s announcement within the new features guide is easy to overlook:

“The table operator can now be used in a PL/SQL program on a collection whose data type is declared in PL/SQL. This also allows the data type to be a PL/SQL associative array. (In prior releases, the collection’s data type had to be declared at the schema level.)”

The following example was developed using the the pre-built Oracle Virtual Machine Database Database App Development image with the versions:

SQL Developer: 4.0.0.13

Oracle Database: 12.1.0.1.0

The first step is to create a package spec which will contain the record type and Associative Array.

CREATE OR REPLACE PACKAGE cake
IS

   TYPE cake_record IS RECORD (cake_name   VARCHAR2(100),
                               cake_price  NUMBER(4));

   TYPE cake_aa IS TABLE OF cake_record
      INDEX BY PLS_INTEGER;

END cake;

There should be nothing surprising with this code, a record type is defined (Lines 4 & 5) and then an Associative Array is defined (Lines 7 & 8) based on the record.

To demonstrate the new functionality I create the following anonymous block:

DECLARE

laa_cakes   cake.cake_aa;
ln_price    NUMBER(4);

BEGIN

   laa_cakes(1).cake_name := 'Eccles';
   laa_cakes(1).cake_price := 10;

   laa_cakes(2).cake_name := 'Cinnamon Bun';
   laa_cakes(2).cake_price := 40;

  SELECT c.cake_price
    INTO ln_price
    FROM table(laa_cakes) c
   WHERE c.cake_name = 'Cinnamon Bun';

   DBMS_OUTPUT.PUT_LINE('The price of the Cinnamon Bun is: '|| TO_CHAR(ln_price));                     

END;
/

Lines 3 – 12, I create a Associative Array of the type defined in the package and populate it with a couple of cakes and their prices. I then use SQL via the TABLE  pseudofunction to query the Associative Array to tell me the price of one of the cakes.

And once run, I see the following output:

Before 12c I would have had to start looping through the Associative Array to look for the item of interest.

It is important to note that in order to use the TABLE pseudofunction the Associative Array has to reside in a package spec.

Summary

In this post I have demonstrated how to use SQL with Associative Array of Records.

Acknowledgements

Connor Mcdonald