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: 18.104.22.168
Oracle Database: 22.214.171.124.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.
In this post I have demonstrated how to use SQL with Associative Array of Records.