ORA-22908: reference to NULL table value

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

This exception is raised when an attempt of updating the rows of an atomically null nested table column is made.
For instance:

create type complex_number as (real_part number, imaginary_part number);

create or replace type complex_tab as table of complex_number;

create table complex_sets (
set_id     number,
set_name   varchar2(50),
set_values complex_tab)
nested table set_values store as nested_complex_set_values;

insert into COMPLEX_SETS (SET_ID,SET_NAME,SET_VALUES)
values (1,'first set',null);

update table(select set_values
             from complex_sets
             where set_id = 1)
 set real_part = real_part + 1
where real_part = 0;

ORA-22908: reference to NULL table value

The nested table underlying the column set_values for row having set_id = 1 has not been initialized, in other words it is said to be atomically null.
Let's repeat the last steps in order to initialize the nested table, ORA-22908 will disappear:

delete from complex_sets where set_id = 1;

insert into COMPLEX_SETS (SET_ID,SET_NAME,SET_VALUES)
values (1,'first set',complex_tab(complex_number(0,0), complex_number(0,1)));

update table(select set_values
              from complex_sets
              where set_id = 1)
  set real_part = real_part + 1
where real_part = 0;
--

2 rows updated

select a.set_id, rownum, b.real_part, b.imaginary_part
 from complex_sets a, table(a.set_values) b
where set_id = 1;

SET_ID   ROWNUM   REAL_PART   IMAGINARY_PART
------   ------   ---------   --------------
1        1        1           0
1        2        1           1

I initialized the nested table with two rows, each containing a complex_number (user-defined) type, note however that it is possible to initialize the nested table to an empty set by specifying the nested table type constructor:

delete from complex_sets where set_id = 1;

insert into COMPLEX_SETS (SET_ID,SET_NAME,SET_VALUES)
values (1,'first set',complex_tab());

update table(select set_values
              from complex_sets
              where set_id = 1)
  set real_part = real_part + 1
where real_part = 0;

--
0 rows updated

Whether it is preferable to have atomically null nested tables or nested tables initialized to an empty set depends on your application. If most nested table columns are going to remain null, then it can make sense to keep them atomically null, otherwise you might consider initializing the nested table to an empty set either using the default clause for the column or inside a before insert trigger or handle ORA-22908 at the application code level, depending on performance considerations.

-- initialize the nested table to an empty set using the DEFAULT column clause

drop table complex_sets cascade constraints;

create table complex_sets (
 set_id     number,
 set_name   varchar2(50),
 set_values complex_tab default complex_tab())
 nested table set_values store as nested_complex_set_values;

insert into COMPLEX_SETS (SET_ID,SET_NAME)
values (1,'first set');

update table(select set_values
              from complex_sets
              where set_id = 1)
  set real_part = real_part + 1
where real_part = 0;
--

0 rows updated

-- handling ORA-22908 inside the application
declare
 l_set  number := 1;
 null_table exception;
 pragma exception_init(null_table, -22908);
begin
 update table(select set_values
              from complex_sets
              where set_id = l_set)
    set real_part = real_part + 1
 where real_part = 0;
exception
when null_table then
 update complex_sets
    set set_values = complex_tab()
  where set_id = l_set;
end;
/

See message translations for ORA-22908 and search additional resources.