A huge warehouse with a lot of big shelves containing identical looking small boxes

How to use Map in Oracle PL/SQL

Maps (not cartography but the data store) are a powerful tool for storing and accessing data in memory. In Oracle PL/SQL they are available too under the name Associative Arrays.

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.


loading comments...