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
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).
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,
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…
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.
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.
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
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:
Steven Feuerstein said
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
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.
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).
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
Dinesh Bhat said
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.
Log Buffer #86: a Carnival of the Vanities for DBAs said
[…] 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 […]
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…
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 😡 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;
/
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.
nikon d700 said
nikon d700…
[…]PL/SQL Associative Arrays « Tyler Muth’s Blog[…]…
Plsql array | Austinbeachvol said
[…] 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
Log Buffer #86: A Carnival of the Vanities for DBAs said
[…] 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 […]
Paige said
Thanks for finally writing about >PL/SQL Associative
Arrays Tyler Muths Blog <Liked it!
blogs online said
Thankfulness to my father who shared with me on the topic of this weblog, this webpage
is genuinely remarkable.
vinay said
why associative array ( index by table ) is required and when to use ?
TimofeyDah said
http://shadymarket.pw/ Покупка и продажа чистых дебетовых карт и других счетов для ваших нужд. банковские карты без паспорта. ccv. продажа банковских карт. купить дебетовые карты. купить дебетовую карту. дебетовую карту купить
TihonFub said
http://cash-forum.pw/ Готовые ООО и ИП , фирмы для ваших нужд.
TrofimGluth said
http://rucrime.pw/ Покупка и продажа чистых дебетовых карт и других счетов для ваших нужд. банковские карты без паспорта. ccv. продажа банковских карт. купить дебетовые карты. купить дебетовую карту. дебетовую карту купить
Alenabiz said
Правильное использование хлореллы в свиноводстве – на каком поголовье ее использование рентабельно
Roberthib said
more feel
RogerThamy said
стикботы
Наборы для творчества Oonies
MUP said
Всем привет на одном из сайтов нашел дестелятор теперь хочу подарить другу, только вот не знаю про эту фирму ничего, как вы считаете стоит взять
купить самогонный аппарат от добрый жар профи
Eduardosip said
пенка для лица ла прери
анисия
JeffreyZiz said
Slimlex
Dietonica
DennisGlart said
интернет магазин стройматериалов
сколько стоит пенопласт
MichaelTem said
Фрезы пилотные
КОЛОКОЛ 207 ЛК 178-130
ncf8101 said
Nonchuan.com
Stevenflelp said
Зимняя рыбалка
Jerrypew said
http://top-mods.info/
Itasiom said
TRUCOS PEREZOSOS DE LIMPIEZA || Consejos fГЎciles para limpiar rГЎpidamente con 123 GO! Spanish No es guay si no sГєper guay https://co.video-video.lv/video/Vd6UxWMh+h8bb8w=.html
Williamsboona said
Drug Treatment Near Me
RichardLam said
мастерский вебсайт http://partnerskie-programmy.net/385-beseed-oplata-za-prosmotry-video.html