January 8, 2006: On emulating tables with nested tables

In PL/SQL, tables can be emulated with nested tables. A nested table type is created with the

create type type_name as
      table of dependand_type_name

command. Basically, this command creates a type (here with the name type_name) that can store 0, 1 or more instances of dependand_type_name. To make theory a bit more clear, this is demonstrated in the following

I create a type that can store one number (a) and one varchar2(15) (b). This type is the dependend type for the nested table type I am going to create later.

As this type functions like a row in the nested table, I call the type row_type:

create type row_type as object (
  a number,
  b varchar2(15)
)
/

With this type, I am now ready to create the nested table type that is going to function like a table, hence the name table_type. It functions like a table because it can store 0, 1 or more instance of row_type.

create type table_type as table of row_type
/

The following procedure sum_group_by_b takes one argument whose name is t and whose type is table_type. The variable t can now be turned into a in memory table by putting the t into a table(t). This construct allows then to select from this in memory table as though it were an ordinary table. It must be noted, however, that, unfortunatly, only select statements can be performed on the table(t) construct, deletes, updates or insertes are not possible.

create procedure sum_group_by_b(t in table_type) is begin
  for r in (
    select sum(a) sum_a, b
      from table(t)
     group by b
  ) loop
    dbms_output.put_line(rpad(r.b, 15) || to_char(r.sum_a, '9999'));
  end loop;
end sum_group_by_b;
/

As can be seen, this procedure groups the values in t by b and sums a for each group and prints the sum along with b's value using dbms_output.

Let's see this procedure in action. I call the function and pass it an instance of table_type that is created with the table_type's constructor. A type's constructor has the same name as the type, so, the constructor looks like table_type( .... ). In the case of nested tables, a constructor takes any number of instances of the dependend type. Here, these instances, in turn, are created using the row_type constructor, that has, again, the same name as the type: namely row_type. Unlike nested tables, this constructor exactly takes two arguments that correspond to the types attributes a and b.

begin
  sum_group_by_b (
    table_type (
      row_type(  25, 'eighty'       ),
      row_type(   2, 'nine'         ),
      row_type(1000, 'four thousand'),
      row_type(   3, 'nine'         ),
      row_type(   4, 'nine'         ),
      row_type(3000, 'four thousand'),
      row_type(  35, 'eighty'       ),
      row_type(  20, 'eighty'       )
    )
  );
end;
/

The output of the procedure is:

eighty            80
nine               9
four thousand   4000

Emulating an insert

I have already mentioned that I can not do an insert on a nested table. This was a bit inaccurate, I can, but it's not so straightforward as it would be with ordinary tables and the SQL insert statement.

The following (ordinary heap) table stores the data that will be inserted (appended) to the nested table:

create table table_a_b_c (
  a number,
  b varchar2(15),
  c number
);

Some values are filled into the table:

insert into table_a_b_c values (1, '*', 1);
insert into table_a_b_c values (2, '*', 5);
insert into table_a_b_c values (3, '#', 1);
insert into table_a_b_c values (8, '?', 9);
insert into table_a_b_c values (9, '#', 2);
insert into table_a_b_c values (7, '!', 3);
insert into table_a_b_c values (4, '#', 2);
insert into table_a_b_c values (6, '#', 5);

Before I can go on, I have to create (or add) a map member to the row_type. It is unclear to me, why I have to do that, but if I don't, I get a:

14/3     PLS-00801: internal error [*** ASSERT at file pdw4.c, line 2076;
         Type 0x07B8B260 has no MAP method.;
         APPEND_FROM_TABLE__RENE__P__75470[14, 3]]

alter type row_type add map member function m  return number invalidate;

The following procedure receives a table_type to which some rows from the table table_a_b_c (actually the columns a and b only) are appended (or inserted). The parameter c specifies the select criteria for the rows.

It works in two parts. The first part fills t_temp with the selected rows using bulk collect. Again, the rows that are filled into t_temp are created using the constructor row_type(...).

The second part actually appends the data found in t_temp to t. The magical keywords are multiset union all.

t is an in out parameter, so the caller will find the result in its t passed to the procedure.

create procedure append_from_table (
  t in out table_type,
  c in     number
) 
is 
  t_temp table_type;
begin

  select row_type(a, b)
    bulk collect into t_temp
    from table_a_b_c t
   where t.c = append_from_table.c;
  
  t := t multiset union all t_temp;
  
end append_from_table;
/
show errors

Using this procedure, I append the rows from table_a_b_c whose c is equal to 1 or equal to 5 and pass the resulting nested table to sum_group_by_b:

declare
  t table_type := table_type();
begin
  append_from_table(t, 1);
  append_from_table(t, 5);

  sum_group_by_b(t);
end;
/

*                  3
#                  9

Links

More on Oracle

This is an on Oracle article. The most current articles of this series can be found here.


	René Nyffenegger's collection of things on the web
	René Nyffenegger on Oracle - Most wanted - Feedback - Follow @renenyffenegger