Varray

In a Oracle database, we can use define datatypes based on VARRAY, a sort of resizable vector with a fixed max size.

Let's say that we have to manage a kindergarten waiting list. We want to put in a table the kid names and the relative parents names. And we want also to be able to manage the case of parents who don't have currently any kid, but still they want to enter in the list (it's a fancy kindergarten with a very long waiting list).

Any kid could have 0, 1, or 2 parents. And this is how we create the type that describes their names:

create type parents_t is varray(2) of varchar2(100);

We could have 0 or 1 kid (twins are not supported):

create type child_t is varray(1) of varchar2 (100);

And now we use these new types for our family table:

create table family (
  surname varchar2(100),
  parents parents_t,
  kid child_t
);

Here is the PL/SQL code to put in the list Ann and Bill Smith, proud parents of Charlie Smith:

declare
  parents parents_t := parents_t();
  kid child_t := child_t();
begin
  parents.extend(2);
  parents(1) := 'Ann';
  parents(2) := 'Bill';

  kid.extend();
  kid(1) := 'Charlie';

  insert into family (surname, parents, kid)
    values('Smith', parents, kid);
end;

And here is the case of Dan Schmidt, single, no kids, but with lot of hopes in the future:

declare
  parents parents_t := parents_t();
  kid child_t := child_t();
begin
  parents.extend(1);
  parents(1) := 'Dan';

  insert into family (surname, parents, kid)
    values('Schmidt', parents, kid);
end;

Chapter 12 of Oracle PL/SQL Programming, fifth edition, by Steven Feuerstein is all about collections.