How to use Map in Oracle PL/SQL

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!).

So here is a PL/SQL snippet on how to achieve the same result in Oracle:

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:

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.

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.