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

 

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.