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 replaceSimilarly, with ALTER TABLE:
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
create table test_tab (table_name varchar2(30) );Nested table columns require that you specify the NESTED TABLE clause to the ddl statement:
alter table test_tab
add (table_columns tab_column_type);
ORA-22913: must specify table name for nested table column or attribute
drop table test_tab;or
create table test_tab (
table_name varchar2(30)
table_columns tab_column_type))
nested table table_columns store as nested_tab return as value;
drop table test_tab;Note also that you can specify multiple nested tables if necessary:
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;
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:
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?
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;
/
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;
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.
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,
Post a Comment