Wednesday, June 25, 2008

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.

5 comments:

PegasusOnline said...

i have this problem:
CREATE OR REPLACE TYPE OBJECT1 AS OBJECT(
item_id Number ( 6 ),
descr varchar2(30 ),
quant Number ( 4,2)
);
/

CREATE OR REPLACE TYPE OBJECTS1 AS TABLE OF OBJECT1;
/


CREATE OR REPLACE TYPE OBJECT2 AS OBJECT(
ATTR VARCHAR2(1 BYTE),
ATTR2 OBJECTS1
);
/
CREATE TABLE TABLE_TEST_OBJECT(
OP VARCHAR2(8 BYTE),
ATTR OBJECT2
);
/

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

when i try to create TABLE_TEST_OBJECT

do you know, how can i resolve this problem?

Byte64 said...

here you go:

CREATE TABLE TABLE_TEST_OBJECT(
OP VARCHAR2(8 BYTE),
ATTR OBJECT2
) nested table attr.attr2 store as nested_tab return as value;
/

Reddy said...

Hi,
Can you help me here?
The following scripts, throws me the same error ORA-22913:....

CREATE OR REPLACE TYPE batch_att_param_obj_type AS OBJECT (
param_sequence NUMBER
,param_prompt VARCHAR2(80)
,param_type VARCHAR2(1) --default_type
,param_value VARCHAR2(2000) --default_value
);

CREATE OR REPLACE TYPE batch_att_param_tab_type AS TABLE OF batch_att_param_obj_type;

CREATE OR REPLACE TYPE batch_att_req_obj_type AS OBJECT (
prgm_sequence NUMBER
,cp_id NUMBER
,cp_name VARCHAR2(30)
,cp_parameters batch_att_param_tab_type
);

CREATE OR REPLACE TYPE batch_att_req_tab_type AS TABLE OF batch_att_req_obj_type;

CREATE TABLE batch_att_rs_stages_dim
(
row_wid NUMBER NOT NULL
,rs_id NUMBER
,rs_appln_id NUMBER
,stage_sequence NUMBER
,stage_name VARCHAR2(30)
,rs_stage_id NUMBER
,stage_reqs batch_att_req_tab_type
)--NESTED TABLE stage_reqs STORE AS batch_att_rss_reqs_tab
NESTED TABLE stage_reqs.cp_parameters STORE AS batch_att_cp_params_tab;

Byte64 said...

Hi Jithender,
although we might find a way of specifying the correct syntax (I can't check this right now, may be in three or four days), are you sure that this convoluted structure is really necessary?
When I find myself fighting against weird syntax errors I always happen to think that perhaps I just took it a bit too far.
In particular I see you have a nested table within another nested table, so I am wondering if you couldn't just use three (or two ordinary tables with one nested table) to represent this.

Is this unusual-multiple-nested-objects-structure really required to solve your problem?

Let me know, please.

Reddy said...

I thought this structure would be a way to represent the problem (and might look better, although it might be a little difficult to populate the table data).
But I was thinking about the alternates to use two different tables (today), if I don't get it the above way.

Thanks for trying... please let me know if you get that structure, it might be useful for future references if not this time.

Thanks,

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles