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:

1const driverMap = new Map();
2
3driverMap.set('verstappen', {
4  fullName: 'Max Verstappen',
5  constructor: 'Red Bull',
6  age: 24,
7});
8driverMap.set('sainz', {
9  fullName: 'Carlos Sainz',
10  constructor: 'Ferrari',
11  age: 27,
12});
13
14if (driverMap.has('verstappen')) {
15  const driver = driverMap.get('verstappen');
16  console.log(
17    `${driver.fullName} is ${driver.age} years old and is a ${driver.constructor} driver.`
18  );
19  // Max Verstappen is 24 years old and is a Red Bull driver.
20}
21
22driverMap.delete('sainz');
23console.log(`${driverMap.size} driver(s) left in Map.`);
24// 1 driver(s) left in Map.
25

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:

1declare
2    -- first define the datatype that we want to store in the map
3    type driver_info is record (
4      full_name   varchar2(255 char)
5    , constructor varchar2(255 char)
6    , age         pls_integer
7    );
8
9    -- then create a table array of it -> this is our map definition
10    type driver_tab is table of driver_info index by varchar2(255);
11    -- you can use other data types like pls_integer as key
12
13    -- create instances of both types
14    l_driver_info driver_info;
15    l_driver_tab driver_tab;
16begin
17    l_driver_info.full_name := 'Max Verstappen';
18    l_driver_info.constructor := 'Red Bull';
19    l_driver_info.age := 24;
20
21    l_driver_tab('verstappen') := l_driver_info;
22
23    l_driver_info.full_name := 'Carlos Sainz';
24    l_driver_info.constructor := 'Ferrari';
25    l_driver_info.age := 27;
26
27    l_driver_tab('sainz') := l_driver_info;
28
29    -- use .exists() to check if an element exists (returns boolean)
30    if l_driver_tab.exists('verstappen') then
31        -- access element by key
32        l_driver_info := l_driver_tab('verstappen');
33        dbms_output.put_line(l_driver_info.full_name || ' is '
34          || l_driver_info.age || ' years old and is a '
35          || l_driver_info.constructor || ' driver.');
36        -- Max Verstappen is 24 years old and is a Red Bull driver.
37    end if;
38
39    -- remove element with .delete()
40    l_driver_tab.delete('sainz');
41    dbms_output.put_line( l_driver_tab.count || ' driver(s) left in the Associative Array.');
42    -- 1 driver(s) left in the Associative Array.
43end;
44

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:

1-- ...
2-- short syntax - sequence does not matter
3l_driver_info := driver_info(full_name => 'Nico Rosberg', constructor => 'Retired', age => 36);
4l_driver_tab('rosberg') := l_driver_info;
5
6-- even shorter - sequence does matter
7l_driver_info := driver_info('Fernando Alonso', 'Alpine', 40);
8l_driver_tab('alonso') := l_driver_info;
9
10dbms_output.put_line( l_driver_tab.count || ' driver(s) left in the Associative Array.');
11-- 3 driver(s) left in the Associative Array.
12-- ...
13

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:

1function logMap(map) {
2  // driverMap.keys() returns an iterator of all keys
3  const keys = Array.from(driverMap.keys());
4
5  const values = keys.map((k) => {
6    const driver = driverMap.get(k);
7    return {
8      key: k,
9      fullName: driver.fullName,
10      constr: driver.constructor, // naming a property 'constructor' is a terrible idea :)
11      age: driver.age,
12    };
13  });
14  console.table(values);
15}
16

In PL/SQL we can use the functions first and next for an Associative Array to loop through all keys:

1declare
2    -- ...
3    type driver_tab is table of driver_info index by varchar2(255);
4    l_key varchar2(255); -- same datatype as stated in 'indexed by'
5begin
6    -- ...
7    l_key := l_driver_tab.first(); -- returns the first key
8
9    while l_key is not null
10    loop
11        dbms_output.put_line( l_key || ' => ' || l_driver_tab(l_key).full_name );
12        l_key := l_driver_tab.next( l_key ); -- get the next key
13    end loop;
14

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.