Oracle PL/SQL Tutorial Part 12 - Using Associative Arrays

This PL/SQL tutorial continues the introduction to the use of collections by looking at associative arrays.

Oracle PL/SQL Collections

We've already seen that there are 3 different types of collections: • nested tables - covered in part 10 of this Oracle PL/SQL tutorial • varrays (varying arrays) - covered in part 11  • associative arrays - covered in this tutorial

PL/SQL Associative Arrays

Associative arrays were known as index-by tables or PL/SQL tables in previous versions of Oracle and this gives us a clue as to their purpose and functionality - they have an index. In earlier versions of Oracle, PL/SQL tables could only be indexed by BINARY INTEGERs, in Oracle 9i Release 2 and above they can be indexed (associated) with BINARY INTEGER or VARCHAR2 constants or variables. In Oracle 10g and above, the PLS_INTEGER and BINARY_INTEGER types are identical. Associative arrays are much like PL/SQL nested tables in that they are: • one-dimensional; • unbounded; • homogeneous (every element must be of the same type). The difference between associative arrays and PL/SQL nested tables is that nested tables are initially dense whereas associative arrays are sparse. Let's look at a couple of examples.. First we declare the table type: TYPE phone_no_tab IS TABLE OF VARCHAR2(20) INDEX BY PLS_INTEGER; Next we declare a variable of that type: phone_nos phone_no_tab; This creates an array (table) of variable length character strings that is available within our PL/SQL session. The index into the array must be a variable or constant of type PLS_INTEGER/BINARY_INTEGER. Inserting and retrieving data is done in the same way as with nested tables except there is no need (or way) to initialise or extend the array. Instead we just access the cells we want, whether we want to insert data or retrieve data from the array, as in the following examples: SQL> declare 2   TYPE phone_no_tab IS TABLE OF VARCHAR2(20) INDEX BY PLS_INTEGER; 3   phone_nos phone_no_tab; 4 begin 5   phone_nos(1) := '+44 (0) 117 942 2508'; 6   phone_nos(11) := '+44 (0) 117 944 4400'; 7   dbms_output.put_line('phone_no(1) is '||phone_nos(1)); 7   dbms_output.put_line('phone_no(11) is '||phone_nos(11)); 8 end; 9 / phone_no(1) is +44 (0) 117 942 2508 phone_no(11) is +44 (0) 117 944 4400 As you can see from the above example the elements (cells) of the array are created automatically by Oracle. Line 3 acts as as the declaration and constructor of the array. However if we access a cell that doesn't have any data in it, we will generate a NO_DATA_FOUND exception as in the following example: SQL> DECLARE 2   TYPE phone_no_tab IS TABLE OF VARCHAR2(20) INDEX BY PLS_INTEGER; 3   phone_nos phone_no_tab; 4 BEGIN 5   phone_nos (21) := '0117 942 2508'; 6   DBMS_OUTPUT.PUT_LINE('phone_no(1) is '||phone_nos(1)); 7 END; 8 / declare * ERROR at line 1: ORA-01403: no data found ORA-06512: at line 6 That's demonstrated the use of associative arrays when indexed by PLS_INTEGERs, but our example is not very practical. It is much more useful to be able to access a table of phone numbers by type (home,office,mobile) rather than by position. Let's continue our PL/SQL tutorial with an example of associating elements of the array with character strings (VARCHAR2 datatype). SQL> declare 2     TYPE pnt IS TABLE OF VARCHAR2(20) INDEX BY VARCHAR2(30); 3     phone_nos pnt; 4 begin 5     phone_nos('office') := '+44 (0) 845 942 2508'; 6     DBMS_output.put_line('phone_no(office) is '||phone_nos('office')); 7 end; 8 / phone_no(office) is +44 (0) 117 942 2508 We'll finish off this tutorial by having a look at other methods we can use on PL/SQL associative arrays. COUNT: This function returns the number of elements (cells) in the collection DELETE: This procedure with no parameters deletes all the elements in the collection, as with nested tables,. We can also specify a start and end point to say which element(s) is (are) to be deleted. After deleting a cell or the whole collection, any subsequent attempt to read that cell in the collection generates "ORA-01403: no data found". However you can write to any element without any errors as this re-creates the element if it’s been deleted. EXISTS(n) : used to determine if the specified element has been created and not deleted, returns TRUE if the element exists, FALSE if not. The index variable can be either a number of type BINARY_INTEGER/PLS_INTEGER or a character string of type VARCHAR2 FIRST : returns the subscript of the first element in the PLSQL associative array LAST : returns the subscript of the last element in the PL/SQL associative array PRIOR(n): returns the subscript of the previous element in the PL/SQL associative array or NULL if if no more elements exist NEXT(n) : returns the subscript of the next element in the PLSQL associative array or NULL if no more elements exist. TRIM  / EXTEND: Not appropriate for associative arrays - generates a compilation error The next tutorial looks at the use of multi-level collections in PL/SQL PL/SQL tutorial Part 11- varrays                                                            PL/SQL tutorial Part 13  --------------------------------------- Looking to sky-rocket productivity, save time and reduce costs? Training is a highly cost-effective and proven method of boosting productivity. Smartsoft offers instructor- led training in Oracle and related technologies on or off site in cities across the UK as well as self-study online training. See our scheduled Oracle training courses, or let us know your requirements. Looking for more Oracle tips and tricks? For no frills, no fluff, just solid, reliable technical information, take a short cut now and subscribe to our newsletter. Jam-packed full of tips and tricks, it will help you make your Oracle systems faster and more reliable and save you hours searching for information. Subscribe today - there's no charge - and your first issue will soon be winging its way to your mailbox. Smartsoft Computing Ltd, Bristol, England Tel: 0845 003 1320 Need help with Oracle? Contact Us View our privacy policy This site uses woopra.com to gather statistical information about our visitors. This data is aggregated to show industry trends (such as browser share). However, this data shall be the average of many thousands of visits and is in no way linked to individuals. View woopra privacy policy.  Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners. © Copyright Smartsoft Computing Ltd 2014. All rights reserved.