Tyler Muth’s Blog

Technology with a focus on Oracle, Application Express and Linux

PL/SQL Associative Arrays

Posted by Tyler Muth on February 21, 2008

I’ve known of PL/SQL Associative Arrays for quite a while (doc for 11.1, 10.2, 10.1, 9.2), but never really used them before. Wow, I had no idea what I was missing! They’re particularly useful for name-value pair type arrays where you want to look up the value of a particular element without looping over the entire array. To quote the 11g documentation “It is like a simple version of a SQL table where you can retrieve values based on the primary key”.  As with any array in PL/SQL, they are most efficient when used with a small number of rows, such as simple lookup tables.  If you find yourself loading tens of thousands of rows into an array, you’re probably doing something wrong.

I always learn best from examples, but I didn’t really find a lot of sample code for Associative Arrays, which is why I wanted to include a few here based on some data from the Apollo Space Program. Lets start with a classic name-value pair example:

declare
    type assoc_arr is table of varchar2(255) index by varchar2(255);
    apollo_commanders assoc_arr;
begin
    apollo_commanders('Apollo 11') := 'Neil Armstrong';
    apollo_commanders('Apollo 12') := 'Pete Conrad';
    apollo_commanders('Apollo 13') := 'James Lovell';
    apollo_commanders('Apollo 14') := 'Alan Shepard';
    apollo_commanders('Apollo 15') := 'David Scott';
    apollo_commanders('Apollo 16') := 'John W. Young';
    apollo_commanders('Apollo 17') := 'Eugene A. Cernan';

    dbms_output.put_line(apollo_commanders('Apollo 11'));
    dbms_output.put_line(apollo_commanders('Apollo 14'));
end;
/

-- Results:
-- Neil Armstrong
-- Alan Shepard

Notice that I can simply access an element by name, without looping. The next example demonstrates how to loop over an Associative Array. This may seem like it’s contrary to the purpose of Associative Arrays, but I had a need for this and had a hard time finding examples of it:

declare
    type assoc_arr is table of varchar2(255) index by varchar2(255);
    apollo_commanders assoc_arr;
    l_current_mission   varchar2(255);
begin
    apollo_commanders('Apollo 11') := 'Neil Armstrong';
    apollo_commanders('Apollo 12') := 'Pete Conrad';
    apollo_commanders('Apollo 13') := 'James Lovell';
    apollo_commanders('Apollo 14') := 'Alan Shepard';
    apollo_commanders('Apollo 15') := 'David Scott';
    apollo_commanders('Apollo 16') := 'John W. Young';
    apollo_commanders('Apollo 17') := 'Eugene A. Cernan';

    l_current_mission := apollo_commanders.first;
    loop
        exit when l_current_mission is null;
        dbms_output.put_line('Mission: '||l_current_mission||', Commander: '||apollo_commanders(l_current_mission));
        l_current_mission := apollo_commanders.next(l_current_mission);
    end loop;
end;
/

-- Results:
-- Mission: Apollo 11, Commander: Neil Armstrong
-- Mission: Apollo 12, Commander: Pete Conrad
-- Mission: Apollo 13, Commander: James Lovell
-- Mission: Apollo 14, Commander: Alan Shepard
-- Mission: Apollo 15, Commander: David Scott
-- Mission: Apollo 16, Commander: John W. Young
-- Mission: Apollo 17, Commander: Eugene A. Cernan

Finally, a more complex example using a record that I’ve used a lot on a recent project:

declare
    type apollo_rec is record(
        commander   varchar2(100),
        launch      date);
    type apollo_type_arr is table of apollo_rec index by varchar2(100);
    apollo_arr apollo_type_arr;
begin
    apollo_arr('Apollo 11').commander := 'Neil Armstrong';
    apollo_arr('Apollo 11').launch := 	to_date('July 16, 1969','Month dd, yyyy');
    apollo_arr('Apollo 12').commander := 'Pete Conrad';
    apollo_arr('Apollo 12').launch := 	to_date('November 14, 1969','Month dd, yyyy');
    apollo_arr('Apollo 13').commander := 'James Lovell';
    apollo_arr('Apollo 13').launch := 	to_date('April 11, 1970','Month dd, yyyy');
    apollo_arr('Apollo 14').commander := 'Alan Shepard';
    apollo_arr('Apollo 14').launch := 	to_date('January 31, 1971','Month dd, yyyy');  

    dbms_output.put_line(apollo_arr('Apollo 11').commander);
    dbms_output.put_line(apollo_arr('Apollo 11').launch);
end;
/

-- Results:
-- Neil Armstrong
-- 16-JUL-69
About these ads

21 Responses to “PL/SQL Associative Arrays”

  1. dombrooks said

    They sure are cool, but they sure have been around for a long time.

    The more recent ability to index by varchar2 is great, I find, for building linked lists – i.e. for this “key” tell me the entry (or entries) in my other SQL type collection (say a much bigger collection that I’m manipulating as part of a big bit of logic and that was populated by bulk collect so is indexed by binary_integer).

  2. mathewbutler said

    Hi Tyler

    These would also be useful if you have a core exception handling package. They provide an quick and efficient means to associate and lookup exception codes and messages.

    Regards,

  3. Tim Hall said

    Hi.

    Collections are held in memory, so be careful not to hold too many row in a collection or you may be hogging all your server memory. For small collections like the ones demonstrated here they work well. It gets a bit scary when people start putting a million rows into one through. :)

    Cheers

    Tim…

  4. Yas said

    This is especially useful for lookup tables like a table holding currency codes. I have tuned many plsql programs using this. If the application queries the same codes millions of times over and over you can use associative arrays to cache those rows in the process memory.

  5. Jonathan Taylor said

    In the second example, why is the code to get the next index not just:-


    l_current_mission := apollo_commanders.next(l_current_mission);

    This seems to work fine – .NEXT will return NULL if there is not NEXT – there is no need to check next element.

  6. Tyler Muth said

    Wow, I woke up to a bunch of great comments!

    Mathew, Tim, and Yas, I’ve added a few sentences of guidance to the first paragraph to hopefully keep people from loading their entire table into an array to process it. Jonathan, you were correct, so I simplified the second example based on your suggestion.

    I welcome these types of comments, as I never claim to be 100% correct and I learn a great deal from others in the community critiquing my work. I can then correct or amend my examples so that others who visit this blog can benefit from these corrections.

    Thanks again,
    Tyler

  7. Tyler Muth said

    Arie Geller made an excellent point in this forum post that I should include the orginal code when I make corrections so others can learn from these corrections. Below is the original code from the 2nd example in this post:

    declare
        type assoc_arr is table of varchar2(255) index by varchar2(255);
        apollo_commanders assoc_arr;
        l_current_mission   varchar2(255);
    begin
        apollo_commanders('Apollo 11') := 'Neil Armstrong';
        apollo_commanders('Apollo 12') := 'Pete Conrad';
        apollo_commanders('Apollo 13') := 'James Lovell';
        apollo_commanders('Apollo 14') := 'Alan Shepard';
        apollo_commanders('Apollo 15') := 'David Scott';
        apollo_commanders('Apollo 16') := 'John W. Young';
        apollo_commanders('Apollo 17') := 'Eugene A. Cernan';
        
        l_current_mission := apollo_commanders.first;
        loop
            exit when l_current_mission is null;
            dbms_output.put_line('Mission: '||l_current_mission||', Commander: '||apollo_commanders(l_current_mission));
            if apollo_commanders.exists(apollo_commanders.next(l_current_mission)) then
                l_current_mission := apollo_commanders.next(l_current_mission);
            else
                l_current_mission := null;
            end if;
        end loop;
    end;
    /
  8. Tyler,

    I am very glad to see you spreading the word on collections (associative arrays being just one of three types of collections in PL/SQL). I strongly believe that every PL/SQL developer should be very familiar and comfortable with these structures. They have so many applications!

    I encourage visitors to this blog to check out my PL/SQL Obsession page, http://www.ToadWorld.com/SF, and in particular the “Trainings, Seminars, Presentations” page. You will find several presentations and many files (in the demo.zip) to help you come up to speed quickly on collections.

    Most helpful presentations:

    * Hands on Collections
    * 21st Century PL/SQL
    * Best of PL/SQL

    Warm regards,
    Steven Feuerstein

  9. Stew said

    Tyler,

    Steven also included some good examples in his seminal work (and my Bible) “Oracle PL/SQL Programming”. In the fourth edition, you’ll find a nice intro similar to what you started with on page 322.

  10. Marco said

    Great topic, Tyler. We’re using associative arrays, particularly the string-indexed variety, under the hood of the Interactive Reports feature in APEX 3.1.

    For random-access to table values, caching in PL/SQL collections can give you huge speed improvements, and reducing loops in your code by using associative arrays makes code much easier to read (and my code-legibility can always use some help).

  11. sival said

    Hi Tyler,

    I like your blog you provide some very useful comments in a concise manner. Thanks.

    We’re using both types of collections (indexed by integer and varchar2). They help a lot in processing arrays of information. I find them much more flexible and easy to use than varrays or SQL table types. Their syntax is more intuitive. I only wish that PL/SQL custom types have some object-oriented features.

    One example where we use collections indexed by varchar2 is to store our custom error messages. You can reference them by name. So they are very easy to access, understandable from a programmer’s point of view and the custom messages is only stored once. It makes our error messages more consistent.

    sival
    letstalkaboutoracle.wordpress.com

  12. A nice article.
    I tend to store all my metadata, however small it is in tables. This way it is (in my opinion) slightly easier to change and update and I would assume that Oracle would cache it if used often possibilities for extension. I have never used associative arrays and I would like to know if there are any adverse effects to exclusively storing metadata in tables and looking it up in PL/SQL.

  13. [...] another illuminating discussion on Tyler Muth’s blog, this one on PL/SQL associative arrays. “They’re particularly useful for name-value pair type arrays where you want to look up the [...]

  14. Stephane said

    I used them to implement string translation module for my PL/SQL application. A table stores all my translatable strings. I create an array based on my table record type, indexed by a text column in the table.

    I populate the array in the package init section of my package… mind you this stores the array, I believe in the PGA? So as mentioned, keep it fairly small…

    Works great! I simply reference my various text labels in the application with a call to pkg_gui_text.translate(‘welcome’)… and based on current application context for the session, it’ll return in proper language…

  15. DJ said

    I’m trying to use an associative array to perform dynamic sql.
    I’m looking to use bind variables, but I don’t know if this is possible using associative arrays.
    Please see the simplified example below.
    What I am looking for is something for the vColumnUpdate line that would parse this using the associative array record as bind variable. I know this does not work, just there as an example of what I would like to accomplish.
    Is there a way to tell oracle to prepare a variable string as though you were executing a dynamic sql statement. For example –> execute immediate ‘select :x from dual’ using 1.

    declare
    type assoc_arr is table of varchar2(255) index by pls_integer;
    db_col assoc_arr;
    field_val assoc_arr;
    vUpdateStatement varchar2(4000);
    vColumnUpdate varchar2(4000);
    vWhereClause varchar2(4000);
    begin
    db_col(0) := ‘ENAME’;
    db_col(1) := ‘JOB’;

    field_val(0) := ’1′;
    field_val(1) := ’2′;

    vUpdateStatement := ‘ update emp set’;
    vWhereClause := ‘ where empno = 9000′;

    for i IN db_col.FIRST .. db_col.LAST
    loop
    vColumnUpdate := db_col(i) || ‘ = :mybindvar ,’ using field_val(i);
    vUpdateStatement := vUpdateStatement || vColumnUpdate;
    end loop;

    –Remove the last comma
    vUpdateStatement := substr(vUpdateStatement, 1, instr(vUpdateStatement,’,’,-1) -1);

    execute immediate (vUpdateStatement || vWhereClause);

    end;
    /

  16. Gary said

    If you don’t know the number of columns you are going to update (and hence the number of bind variables you need to use), then you’ll need to use DBMS_SQL, not execute immediate.
    It will go something like this
    Loop through the array generating the SQL
    dbms_sql.Parse the generated SQL
    Loop through the array a second time, to DBMS_SQL.BIND_VARIABLE each variable
    Finally DBMS_SQL.EXECUTE the sql.

  17. nikon d700 said

    nikon d700…

    [...]PL/SQL Associative Arrays « Tyler Muth’s Blog[...]…

  18. [...] PL/SQL Associative Arrays « Tyler Muth’s BlogFeb 21, 2008 … I’ve known of PL/SQL Associative Arrays for quite a while (doc for 11.1, 10.2, 10.1 , 9.2), but never really used them before. Wow, I had no idea … [...]

    • Adelio Stevanato said

      I an using Oracle 9i.
      That is all well and good but what I want to be able to do is return the assiciative array as a cursor!
      Tried using pipelining, compliles OK but cannot call it, get “invalid datatype” error.

      If only oracle would let me dynaically create temporary tables like in SQL Server things would be a lot easier

  19. [...] another illuminating discussion on Tyler Muth’s blog, this one on PL/SQL associative arrays. “They’re particularly useful for name-value pair type arrays where you want to look up [...]

  20. Paige said

    Thanks for finally writing about >PL/SQL Associative
    Arrays Tyler Muths Blog <Liked it!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 89 other followers

%d bloggers like this: