Oracle multi-level Collection in PL/SQL

Question

I have a multi-level collection defined as below.

declare
     type t_addr_lines is varray(4) of varchar2(60);
     type t_addr_entry is table of t_addr_lines index by varchar2(10);
     type t_student    is record
     (
         last_name      varchar2(20),
         first_name     varchar2(20),
         l_addr_entry   t_addr_entry
     );

     type t_students is table of t_student; 

     l_students t_students;

     begin

         l_students := t_students();
         l_students.extend();

     end;

/

Essentially the structure is :

 a. a student can have different types of addresses ( 'HOME', 'VACATION' )
 b. each address can have maximum of 4 lines

I am wondering how to address and populate the different components of the collection.

Answer 1

You are mixing several collection types so it's a bit messy, but I guess that's the point. You can refer to each record in your varray structure either by its position (a number) or using first/last; you then directly assign to the record elements:

begin
  l_students := t_students();

  l_students.extend();
  -- explicit carray entry number 
  l_students(1).first_name := 'Bruce';
  l_students(1).last_name := 'Wayne';
  l_students(1).l_addr_entry('Home') := t_addr_lines('1007 Mountain Drive', 'Gotham');
  l_students(1).l_addr_entry('Work') := t_addr_lines('The Batcave', '1007 Mountain Drive', 'Gotham');

For the address entries you specify which entry you are assigning, using a 10-char value as the key, e.g. Home or Work. You then assign a new instance of a t_addr_lines table, populated with up to for strings.

And then for a second student, extend and populate again:

  l_students.extend();
  -- last entry in varray
  l_students(l_students.last).first_name := 'Clark';
  l_students(l_students.last).last_name := 'Kent';
  l_students(l_students.last).l_addr_entry('Work') := t_addr_lines('The Daily Planet', 'Metropolis');

To get the data back out you can iterate over the l_students entries:

  for i_stud in l_students.first..l_students.last
  loop
    ...
  end loop;

The addresses are a bit trickier, particularly if you want to know the key value. You need to get the first key value, and assign it to a variable, which you have to have declared earlier:

   i_addr := l_students(i_stud).l_addr_entry.first;

and then loop, incrementing that key value:

    loop
      ...
      i_addr := l_students(i_stud).l_addr_entry.next(i_addr);
    end loop;

Then within that loop (!), a further loop over that entry's address lines:

      for i_line in l_students(i_stud).l_addr_entry(i_addr).first
                  ..l_students(i_stud).l_addr_entry(i_addr).last
      loop
        ...
      end loop;

So putting that together and just dumping the values out with dbms_output:

declare
  type t_addr_lines is varray(4) of varchar2(60);
  type t_addr_entry is table of t_addr_lines index by varchar2(10);

  type t_student    is record
  (
    last_name      varchar2(20),
    first_name     varchar2(20),
    l_addr_entry   t_addr_entry
  );

  type t_students is table of t_student; 

  l_students t_students;

  -- index for address entries
  i_addr varchar2(10);

begin
  l_students := t_students();

  l_students.extend();
  -- explicit carray entry number 
  l_students(1).first_name := 'Bruce';
  l_students(1).last_name := 'Wayne';
  l_students(1).l_addr_entry('Home') := t_addr_lines('1007 Mountain Drive', 'Gotham');
  l_students(1).l_addr_entry('Work') := t_addr_lines('The Batcave', '1007 Mountain Drive', 'Gotham');

  l_students.extend();
  -- last entry in varray
  l_students(l_students.last).first_name := 'Clark';
  l_students(l_students.last).last_name := 'Kent';
  l_students(l_students.last).l_addr_entry('Work') := t_addr_lines('The Daily Planet', 'Metropolis');

  for i_stud in l_students.first..l_students.last
  loop
    dbms_output.put_line('Student: '
      || l_students(i_stud).last_name ||', '|| l_students(i_stud).first_name);

    -- get index value of first address table entry
    i_addr := l_students(i_stud).l_addr_entry.first;
    -- loop over addresses starting from that index
    while i_addr is not null
    loop
      dbms_output.put_line('Address (' || i_addr || '):');
      -- loop over lines in this address
      for i_line in l_students(i_stud).l_addr_entry(i_addr).first
                  ..l_students(i_stud).l_addr_entry(i_addr).last
      loop
        dbms_output.put_line('  ' || l_students(i_stud).l_addr_entry(i_addr)(i_line));
      end loop;
      i_addr := l_students(i_stud).l_addr_entry.next(i_addr);
    end loop;
  end loop;
end;
/

which gets:

Student: Wayne, Bruce
Address (Home):
  1007 Mountain Drive
  Gotham
Address (Work):
  The Batcave
  1007 Mountain Drive
  Gotham
Student: Kent, Clark
Address (Work):
  The Daily Planet
  Metropolis


PL/SQL procedure successfully completed.

db<>fiddle