The indexes of associative arrays, that is, not tables.
Table indexes are…well, what they are.
But when it comes to associative arrays, one of the three types of PL/SQL collections, you can choose between an integer index and a string index.
Which means that when you assign a value to an element in the collection, you specify either an integer value or a string value as its index or location in the array.
In episode of 3 of Feuertips, I discussed string-indexed arrays. Watch the full episode below:
String-indexed associative arrays are interesting and fun. They can also result in greatly simplified code when used properly.
Let’s run through a simple example that also nicely demonstrates the power of these types of collections.
Our users need to have unique “handles.” They type in something, we check it. If the handle is already taken, they are asked to try again. And I need to keep track of all the handles they’ve tried. Don’t ask me why. I just do.
So I built a package to keep track of the handles, or strings. It’s called the string tracker package, and here’s the body:
PACKAGE BODY string_tracker
IS
TYPE used_aat IS TABLE OF varchar2(1000) INDEX BY PLS_INTEGER;
g_names_used used_aat;
PROCEDURE mark_as_used (value_in IN varchar2)
IS
BEGIN
g_names_used (g_names_used.COUNT + 1) := value_in;
END mark_as_used;
FUNCTION string_in_use (value_in IN varchar2) RETURN BOOLEAN
IS
c_count CONSTANT PLS_INTEGER := g_names_used.COUNT;
l_index PLS_INTEGER := g_names_used.FIRST;
l_found BOOLEAN DEFAULT FALSE;
BEGIN
IF l_index IS NOT NULL
THEN
WHILE (NOT l_found AND l_index <= c_count)
LOOP
l_found := value_in = g_names_used (l_index);
l_index := l_index + 1;
END LOOP;
END IF;
RETURN l_found;
END string_in_use;
END string_tracker;
In this approach, I declare a collection of strings indexed by integer. When I want to mark a string (handle) as being used, I add it to the end of the list:
g_names_used (g_names_used.COUNT + 1) := value_in;
To find out if a string has already been used, I loop through the collection in search of a match:
WHILE (NOT l_found AND l_index <= c_count) LOOP l_found := value_in = g_names_used (l_index); l_index := l_index + 1; END LOOP;
That all makes sense, right? OK, but here are some things that do not make sense:
- The matching algorithm is not data neutral. By which I mean, as the list grows, performance decreases.
- Essentially, I am doing a “full table scan” of the collection.
- The way I use the collection implies that the order in which strings are added to the list is significant, but it is not.
- string_in_use is not the most complicated piece of code you’ll ever see, but it’s the sort of algorithm that can easily introduce boundary condition errors and suck all the time out of your day.
PACKAGE BODY string_tracker
IS
SUBTYPE who_cares_t IS BOOLEAN;
c_does_not_matter CONSTANT who_cares_t DEFAULT NULL;
TYPE used_aat IS TABLE OF who_cares_t INDEX BY varchar2(1000);
g_names_used used_aat;
PROCEDURE mark_as_used (value_in IN varchar2)
IS
BEGIN
g_names_used (value_in) := c_does_not_matter;
END mark_as_used;
FUNCTION string_in_use (value_in IN varchar2) RETURN BOOLEAN
IS
BEGIN
RETURN g_names_used.EXISTS (value_in);
END string_in_use;
END string_tracker;
So first thing to notice: my, oh my, how the string_in_use function has shrunk! Now it’s just one line. Wow! And how is that possible? String-indexed arrays. Let’s go back up to the top of the package body and see how that happened.
My list of strings is now defined differently:
SUBTYPE who_cares_t IS BOOLEAN; c_does_not_matter CONSTANT who_cares_t DEFAULT NULL; TYPE used_aat IS TABLE OF who_cares_t INDEX BY varchar2(1000); g_names_used used_aat;
First, I created a new type (a “subtype”) that is really just a renaming of Boolean. Why would I do that? Take a look at the type declaration. Two things of note:
- The collection is indexed by a string, not an integer.
- The type of data stored in the collection is “Who cares?”. My objective here is to self-document that the values stored in the collection are of no importance.
- Which means…hmmmm…it must be the index of the collection that’s important.
Sound familiar? Kind of like an index-organized table: ” Effectively, the data is stored within the primary key index.” Tim Hall, https://oracle-base.com/articles/8i/index-organized-tables.
The code to add a string to the array is still one line, just like in the original integer-indexed version. But now the string itself is the index value; I do not add sequentially to the end of the list:
g_names_used (value_in) := c_does_not_matter;
and that, dear friends, is the secret sauce. Since I use the string itself as the index value, I can now oh so quickly and easily check to see if the string has been used: the answer is “Yes, used” if an element exists in the collection for that string:
RETURN g_names_used.EXISTS (value_in);
Thank you, exists method. Is that delightful or what?
Hopefully, that gives you a sense of the power of string-indexed collections and how they can have a big impact on your code.
Some other things to remember about string-indexed associative arrays (SIAA):
- The methods first, last, next, prior return strings, not indexes.
- Table functions cannot return SIAAs
- You cannot bulk collect into an SIAA.
- SIAAs are always sparse, which means you cannot use a numeric for loop (such as “for I in 1 .. my_siaa.count”) to iterate through the contents of the collection. OK, you can, but you should not.
- The longer the index value, the slower it is to work with the collection.
- National Language Support (NLS) parameters can affect the ordering of elements in a string-indexed collection.
Time to Explore!
When it comes to collections in general and SIAAs in particular, there’s a lot to learn and more than a few details to sort out when it comes to applying them to your application development.
Here are some handy resources to continue your education:
Working with Collections, from my PL/SQL 101 series:
https://blogs.oracle.com/oraclemagazine/working-with-collections
Working with String-indexed Associative Arrays, from my YouTube channel:
https://www.youtube.com/watch?v=8w8jxc11FKA
Associative Array documentation in PL/SQL Language Reference:
LiveSQL Script for String Tracker
The simplest string tracker package, demonstrating use of string indexing alone.
https://livesql.oracle.com/apex/livesql/file/content_LJT3ARATKFF659S024JG4ED9Q.html
This script demonstrates the power not only of string indexing, but also nested collections.
https://livesql.oracle.com/apex/livesql/file/content_CZUXPDAJ7WYGB0HEVEHPOSLVS.html
