
How to use Map in Oracle PL/SQL
Maps are great
#When I program in languages like Go or JavaScript I like to use Maps to store data while processing. It is an easy-to-use in-memory data store where you can store any object and access it with a specific key. In JavaScript we can use them like in this example:
I think they are really easy to understand and straightforward. I caught myself wishing Maps existed in Oracle PL/SQL until I remembered that they are. They are just called ‘Collections’ or more specifically ‘Associative Arrays’.
And to remind me to use them more I decided to write this blogpost. Writing about something is actually the best way to remember something because you try not to solve a problem but to understand something (you should try that too!).
Maps in Oracle PL/SQL
#So here is a PL/SQL snippet on how to achieve the same result in Oracle:
Shorter syntax to init an entity
#The used syntax to declare a new entity is a little bit tedious for entities with many attributes. Luckily we can use some shorthand syntax:
Iterating through all elements
#For some ‘stupid’ functions that have no context of the data and therefore no knowledge of the used keys, it is useful to iterate through all entities.
For example in JavaScript we can write a function that logs every entry of a Map like this:
In PL/SQL we can use the functions first
and next
for an Associative Array to loop through all keys:
You can find the whole JavaScript code in action here (make sure to open the console). The complete PL/SQL example is shared here on LiveSQL.
When to use Associative Arrays
#By the way, Oracle has also put a guide on when to use Associative Arrays in their docs. I find myself needing something like this for somewhat complex data transformation scenarios where you process many rows.