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.

Why indexing nested tables is so good

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

Last week i got a call from a customer who was complaining because a procedure exporting a file was taking a very long time. After some investigation it turned out that, for some reason, during a recent application upgrade, an index on a nested table had not been (re)created.

The symptoms were clear: a procedure that normally took seconds, was now taking 20 minutes to execute.
I could quickly find out the real reason for this huge performance problem after looking at the execution plan of an inner query, that is a query executed several thousands times because it's performed inside an outer explicit cursor:


If it is certainly true that in Oracle (as for any other know database) there is no FAST=TRUE parameter, but it must be said that when a procedure is very slow, either the procedure is poorly written or some index is missing or both... ;-)
If you are lucky, then it's enough to create the right index and voilá, it's almost like turning on that magic switch.
Indeed that was my case as it was enough to (re)create the missing index on the nested table to fix the problem, as follows:

CREATE INDEX IDX_TOTES_CNT ON NESTED_CNT(NESTED_TABLE_ID,MAT_ID);

Note: NESTED_TABLE_ID is a pseudo-column provided by Oracle containing the primary key of the nested table.
Soon the execution plan started looking much better:


The success was confirmed by the fact that the export procedure now took a few seconds again, as it was before the upgrade.
But why do i need an index on a nested table in the first place?
Here is a stripped down version of the inner query. Value par_tote is a cursor parameter that is populated by the outer cursor. This value uniquely identifies the record containing the nested table to be processed. Thereafter, as you can see, i need to filter out certain values (in green) that come from nested table cnt:

select
a.mat_id,
...
b.ean
from table(select cnt
            from totes
           where tote = par_tote) a,
    exits b
where a.mat_id != info_const.conMat_ID_NRR
and a.mat_id = b.mat_id (+)
order by a.mat_id;

With an index on the nested table, the optimizer can perform the table unnesting more efficiently, selecting the recordset with a common NESTED_TABLE_ID (having the same cardinality as tote) and filtering the data basing on mat_id, without having to perform a full table scan on each iteration, which explains why the procedure was taking that outrageous amount of time to execute.

ORA-22913: must specify table name for nested table column or attribute

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

This error is returned when you omit the NESTED TABLE clause from a CREATE TABLE or ALTER TABLE statement and you specified a nested table column, as in the following case:

create or replace
type column_type as object (
col_name     varchar2(30),
col_comment  varchar2(4000)
);
/

create or replace type tab_column_type as table of column_type;
/

create table test_tab (
table_name        varchar2(30),
table_columns     tab_column_type);

ORA-22913: must specify table name for nested table column or attribute

Similarly, with ALTER TABLE:

create table test_tab (table_name        varchar2(30) );

alter table test_tab
add (table_columns tab_column_type);

ORA-22913: must specify table name for nested table column or attribute

Nested table columns require that you specify the NESTED TABLE clause to the ddl statement:

drop table test_tab;

create table test_tab (
table_name        varchar2(30)
table_columns     tab_column_type))
nested table table_columns store as nested_tab return as value;

or

drop table test_tab;

create table test_tab (
table_name        varchar2(30));

alter table test_tab
add (table_columns tab_column_type)
nested table table_columns store as nested_tab return as value;

Note also that you can specify multiple nested tables if necessary:

drop table test_tab;

create table test_tab (
table_name        varchar2(30));

alter table test_tab
add (table_columns tab_column_type, table_columns2 tab_column_type)
nested table table_columns store as nested_tab return as value
nested table table_columns2 store as nested_tab2 return as value;

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

ORA-02303: cannot drop or replace a type with type or table dependents

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

Updated june 8, 2009: see the comments section for an example of ALTER TYPE statement that works on Oracle 9i or better.

In spite of all enhancements that oracle database underwent over the years, when it comes to replacing an existing user defined object type referenced by other objects with a newer version containing different attributes, the developer or the DBA is basically left to his own fate, so he/she must take appropriate actions in order to successfully carry out the upgrade.
Failure to do so, will most likely lead to the following error message:

ORA-02303: cannot drop or replace a type with type or table dependents

Let's build a simple test case to see what happens and how we can circumvent the problem.
By the way, this is also a good opportunity to see the famous CAST/MULTISET functions at work.

Initially we'll build a single table containing the table names in the schema along with their column names and column comments, stored as a nested table.

create or replace
type column_type as object (
col_name     varchar2(30),
col_comment  varchar2(4000)
);
/
create or replace type tab_column_type as table of column_type;
/
create table test_tab (
table_name        varchar2(30),
table_columns     tab_column_type)
nested table table_columns store as nested_tab return as value;

comment on column test_tab.table_name is 'name of table';
comment on column test_tab.table_columns is 'nested table containing all columns';


insert into test_tab
select
table_name,
cast(multiset(select cc.column_name, cc.comments
        from user_col_comments cc
        where cc.table_name = t.table_name)
as tab_column_type)
as table_columns
from user_tables t;

commit;

Now, you can query the custom table we just created to see what's in there using the classical nested table unnesting technique:

select t.table_name, tc.col_name, tc.col_comment
from test_tab t, table(t.table_columns) tc
where t.table_name = 'TEST_TAB';

TABLE_NAME    COL_NAME             COL_COMMENT                      
------------- -------------------- ---------------------------------------
TEST_TAB      TABLE_NAME           name of table                    
TEST_TAB      TABLE_COLUMNS        nested table containing all columns

2 rows selected

Ok, imagine that we want to add one more attribute to the column_type object (in green color).

create or replace
type column_type as object (
col_name     varchar2(30),
col_comment  varchar2(4000),
col_type     varchar2(255)
);
/

ORA-02303: cannot drop or replace a type with type or table dependents

As you see, Oracle complaints about the fact that there is nested table based on an object type (tab_column_type) that depends on column_type.

How do we move forward in this case?
There are at least two possibilities basing on the specific situation:

1. we don't need to migrate the existing data, we can rebuild the information from scratch. In this case we can drop the column containing the nested table and the dependent object type, then we can extend column_type, rebuild the nested table type and add it to the existing table.
2. we need to partially migrate the existing data as we cannot truncate the parent table containing the nested table, but we can rebuild the content of the nested table basing on the parent key.
   
3. we do need to migrate the existing data as we cannot afford to loose the current values. In this case we need to find a way to migrate the nested table content somewhere.

Let's begin with the simplest case (#1):

truncate table test_tab;

alter table test_tab drop column table_columns;

drop type tab_column_type;

-- extend the original type definition

create or replace
type column_type as object (
col_name     varchar2(30),
col_comment  varchar2(4000),
col_type     varchar2(255)
);
/

create or replace type tab_column_type as table of column_type;
/

-- create a new nested table column in the existing table

alter table test_tab
add (table_columns tab_column_type)
nested table table_columns store as nested_tab return as value;

comment on column test_tab.table_columns is 'nested table containing all columns';

insert into test_tab
select
table_name,
cast(multiset(select cc.column_name, cc.comments, tc.data_type
              from user_col_comments cc, user_tab_columns tc
              where tc.table_name = t.table_name
                and tc.table_name = cc.table_name
                and tc.column_name = cc.column_name) as tab_column_type)
as table_columns
from user_tables t;

commit;

select t.table_name, tc.col_name, tc.col_comment, tc.col_type
from test_tab t, table(t.table_columns) tc
where t.table_name = 'TEST_TAB';

TABLE_NAME      COL_NAME       COL_COMMENT                  COL_TYPE
--------------- -------------- ---------------------------- -------------
TEST_TAB        TABLE_NAME     name of table                VARCHAR2
TEST_TAB        TABLE_COLUMNS  nested table containing ...  TAB_COLUMN_TYPE

2 rows selected

Let's address case #2 now:

alter table test_tab drop column table_columns;

drop type tab_column_type;

create or replace
type column_type as object (
col_name     varchar2(30),
col_comment  varchar2(4000),
col_type     varchar2(255)
);
/

create or replace type tab_column_type as table of column_type;
/

alter table test_tab
add (table_columns tab_column_type)
nested table table_columns store as nested_tab return as value;

comment on column test_tab.table_columns is 'nested table containing all columns';

update test_tab t
set table_columns =
  cast(
   multiset(
     select cc.column_name, cc.comments, tc.data_type
       from user_col_comments cc, user_tab_columns tc
      where tc.table_name = t.table_name
        and tc.table_name = cc.table_name
        and tc.column_name = cc.column_name)
   as tab_column_type);

commit;

select t.table_name, tc.col_name, tc.col_comment, tc.col_type
from test_tab t, table(t.table_columns) tc
where t.table_name = 'TEST_TAB';

TABLE_NAME      COL_NAME       COL_COMMENT                  COL_TYPE
--------------- -------------- ---------------------------- -------------
TEST_TAB        TABLE_NAME     name of table                VARCHAR2
TEST_TAB        TABLE_COLUMNS  nested table containing ...  TAB_COLUMN_TYPE

2 rows selected

Finally, let's tackle the most complex scenario (#3):

create or replace
type old_column_type as object (
col_name     varchar2(30),
col_comment  varchar2(4000)
);
/

create or replace type old_tab_column_type as table of old_column_type;
/

create table mig_test_table (
table_name        varchar2(30),
table_columns     old_tab_column_type)
nested table table_columns store as old_nested_tab return as value;

-- migrate the data to the staging table

insert into mig_test_table
select
t.table_name,
cast(
 multiset(
  select col_name, col_comment
    from table(t.table_columns))
 as old_tab_column_type)
from test_tab t;

alter table test_tab drop column table_columns;

drop type tab_column_type;

create or replace
type column_type as object (
col_name     varchar2(30),
col_comment  varchar2(4000),
col_type     varchar2(255)
);
/

create or replace type tab_column_type as table of column_type;
/

alter table test_tab
add (table_columns tab_column_type)
nested table table_columns store as nested_tab return as value;

comment on column test_tab.table_columns is 'nested table containing all columns';

-- put back the original data and update the new attribute at the same time

update test_tab t
set table_columns =
  cast(
   multiset(
     select cc.col_name, cc.col_comment, tc.data_type
       from mig_test_table m, table(m.table_columns) cc, user_tab_columns tc
      where m.table_name = t.table_name
        and m.table_name = tc.table_name
        and cc.col_name = tc.column_name)
   as tab_column_type);

commit;

select t.table_name, tc.col_name, tc.col_comment, tc.col_type
from test_tab t, table(t.table_columns) tc
where t.table_name = 'TEST_TAB';

TABLE_NAME      COL_NAME       COL_COMMENT                  COL_TYPE
--------------- -------------- ---------------------------- -------------
TEST_TAB        TABLE_NAME     name of table                VARCHAR2
TEST_TAB        TABLE_COLUMNS  nested table containing ...  TAB_COLUMN_TYPE

2 rows selected

Final considerations:
in these examples i did not address problems like invalid triggers or depending procedures that become invalid when the column is dropped.
In a real life scenario this is very likely to happen and in most cases, i guess you'll have to put the database in restricted mode just to be sure that nobody is accessing the objects while the migration is underway.
Clearly you'll have to check also any dependent objects and fix the failing SQL statements or extend them to support the newly added object attribute(s) or remove the references to the old ones.

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

ORA-06532: Subscript outside of limit

This error is returned if, for some reason, a subscript value is lower than 1 (one) or greater than the declared upper bound of a varray.
A negative or zero value subscript will also cause an error when working with nested tables.
Do not confuse the upper bound with the actual number of initialized elements in the varray or table. A varray may contain 5 elements out of a maximum of 10, so if you specify 6 as subscript, the error returned will be different, as explained in a previous article.

Let's look at a couple of simple situations:

DECLARE
TYPE array_type IS VARRAY(10) OF VARCHAR2(200);
my_array        array_type := array_type(null, null, null);
BEGIN
my_array(0) := 'a';
dbms_output.enable;
dbms_output.put_line(my_array.COUNT);
END;

Error report:
ORA-06532: Subscript outside of limit
ORA-06512: at line 5
06532. 00000 -  "Subscript outside of limit"
*Cause:    A subscript was greater than the limit of a varray
or non-positive for a varray or nested table.
*Action:   Check the program logic and increase the varray limit
if necessary.

Another case being:

DECLARE
TYPE array_type IS VARRAY(10) OF VARCHAR2(200);
my_array        array_type := array_type(null, null, null);
BEGIN
my_array(11) := 'a';
dbms_output.enable;
dbms_output.put_line(my_array.COUNT);
END;

Error report:
ORA-06532: Subscript outside of limit
ORA-06512: at line 5
06532. 00000 -  "Subscript outside of limit"
*Cause:    A subscript was greater than the limit of a varray
or non-positive for a varray or nested table.
*Action:   Check the program logic and increase the varray limit
if necessary.

It should be clear that in both situations the subscript is out of range.
A slightly different situation is the following one:

DECLARE
TYPE array_type IS VARRAY(10) OF VARCHAR2(200);
my_array        array_type := array_type();
BEGIN
my_array.EXTEND(11);
dbms_output.enable;
dbms_output.put_line(my_array.COUNT);
END;

Error report:
ORA-06532: Subscript outside of limit
ORA-06512: at line 5
06532. 00000 -  "Subscript outside of limit"
*Cause:    A subscript was greater than the limit of a varray
  or non-positive for a varray or nested table.
*Action:   Check the program logic and increase the varray limit
  if necessary.

In this case it's easy to spot that we tried to initialize the collection to a larger number of elements that it can hold, but there can be subtler situations as follows:

DECLARE
TYPE array_type IS VARRAY(10) OF VARCHAR2(200);
my_array        array_type := array_type(null);
BEGIN
my_array.EXTEND(10);
dbms_output.enable;
dbms_output.put_line(my_array.COUNT);
END;

It would be fine to extend the varray by 10 elements if we hadn't already initialized one element at declaration time (the null element above), indeed if you remove the null from the type constructor, the program will run without errors.

If you are populating the collection by means of some iterative process where you extend (that is initialize) the elements one at a time, then you must ensure that you do not extend the varray beyond its limits.
As already explained, you can check the limits with the collection method LIMIT, provided you have initialized the collection.
-----------------------------------------------
ORA-06532: Indice inferiore fuori dal limite
ORA-06532: Subíndice fuera del límite
ORA-06532: Subscript fora de límit
ORA-06532: Indice hors limites
ORA-06532: Index außerhalb der Grenzen
ORA-06532: Δείκτης εκτός ορίου
ORA-06532: Subscript uden for begrænsning
ORA-06532: Indexvariabel utanför gränsvärdet
ORA-06532: Subskript utenfor grense
ORA-06532: Alikomento ylitti rajan
ORA-06532: Határon kívüli index
ORA-06532: Indicele este în afara limitei
ORA-06532: Subscript ligt buiten limiet.
ORA-06532: Subscrito além do limite
ORA-06532: Subscrito fora do limite
ORA-06532: Индекс превышает пределы
ORA-06532: Dolní index přesahuje limit
ORA-06532: Dolný index mimo limitu
ORA-06532: Indeks (współrzędna elementu tablicy) spoza zakresu
ORA-06532: İndis sınırın dışında
ORA-06532: Subscript outside of limit

See message translations for ORA-06532 and search additional resources

PLS-00306: wrong number or types of arguments in call to 'DELETE'

This compilation error occurs when you specify a parameter for the DELETE method, as follows:

DECLARE
TYPE array_type IS VARRAY(10) OF VARCHAR2(200);
my_array        array_type := array_type();
BEGIN
my_array.EXTEND(10);
my_array.DELETE(4);
dbms_output.enable;
dbms_output.put_line('total:'||my_array.COUNT);
END;

Error report:
ORA-06550: line 6, column 1:
PLS-00306: wrong number or types of arguments in call to 'DELETE'
ORA-06550: line 6, column 1:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

The DELETE method doesn't accept parameters when it's applied to varrays.
When dealing with varrays you can only clear the whole array by specifying the DELETE method without any parameters.
If you want to remove the last n elements, use the TRIM(n) method instead.

DECLARE
TYPE array_type IS VARRAY(10) OF VARCHAR2(200);
my_array        array_type := array_type();
BEGIN
my_array.EXTEND(10);
my_array.TRIM(4);
dbms_output.enable;
dbms_output.put_line('total:'||my_array.COUNT);
END;

total:6

One or two numeric parameters are allowed in DELETE when the collection is defined as TABLE, as in this example of sparse nested table:

DECLARE
TYPE table_type IS TABLE OF VARCHAR2(200);
my_table        table_type := table_type();
BEGIN
my_table.EXTEND(10);
my_table.DELETE(1,10);
my_table(5) := 'a';
dbms_output.enable;
dbms_output.put_line('total:'||my_table.COUNT);
dbms_output.put_line('first subscript:'||my_table.FIRST);
END;

total:1
first subscript:5

See other articles on collection tips and techniques.