PL/SQL datastructure like Hashmap

Question

Is there a way in Oracle pl/sql that I can create a datastructure like java Hashmap which can have a record type as value and String as index.

For example;

 type emp_rec_type is record (emp_id number,emp_salary number);
 emp_rec emp_rec_type;

 type emp_assoc_array is table of emp_rec_type indexed by varchar2(30);
 emp_map emp_assoc_array;

I would like to be able to do the below

emp_rec.emp_id := 1;
   emp_rec.salary := 1000; 
   emp_map('Rohan') := emp_rec;

Can this be achieved? I cannot use nested tables since I can only index it by integer. With associative arrays, I cannot use object type as attributes.

Also would like to do this without having to create an object or schema datatype. Can you please suggest something?

You already have the answer in your example. Minor correction. Not `indexed by` but rather `index by` and you are good to go. — Nick Krasnov, Sep 27 '13 at 16:22

Vincent Malgrat · Accepted Answer · 2013-09-30T08:19:57.397

In PL/SQL you can define associative arrays indexed by VARCHAR2 (since 10g I think):

SQL> DECLARE
  2     TYPE map_varchar IS TABLE OF VARCHAR2(30) INDEX BY VARCHAR2(30);
  3     l map_varchar;
  4  BEGIN
  5     l('A') := 'alpha';
  6     l('B') := 'beta';
  7     dbms_output.put_line(l('A'));
  8  END;
  9  /

alpha

Internally, I think the structure is more like a binary-tree map than an hashmap.

Of course you can store PL/SQL records or SQL objects:

SQL> DECLARE
  2     TYPE emp_rec_type IS RECORD (emp_id NUMBER,emp_salary NUMBER);
  3     TYPE map_varchar IS TABLE OF emp_rec_type INDEX BY VARCHAR2(30);
  4     l_emp emp_rec_type;
  5     l_map map_varchar;
  6  BEGIN
  7     l_emp.emp_id := 1;
  8     l_emp.emp_salary := 1000;
  9     l_map('A') := l_emp;
 10     -- you can also affect record values directly
 11     l_map('B').emp_id := 2;
 12     l_map('B').emp_salary := 2000;
 13     dbms_output.put_line(l_map('A').emp_salary);
 14  END;
 15  /

1000

PL/SQL procedure successfully completed

Yes,but I need to store record type as value instead of VARCHAR2.Can that be achieved using associative arrays? — Rohan, Sep 27 '13 at 16:13
@viveksinghggits Use the collection methods FIRST and NEXT to navigate a sparse collection, see for example: http://docs.oracle.com/database/121/LNPLS/composites.htm#BABCDABC — Vincent Malgrat, Jul 21 '16 at 09:14

PL/SQL datastructure like Hashmap

1 Answers1

Linked