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.
at

5 comments:

  1. PegasusOnlineWednesday, 03 August, 2011

    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?

    ReplyDelete
  2. Byte64Thursday, 04 August, 2011

    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;
    /

    ReplyDelete
  3. ReddyFriday, 05 August, 2011

    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;

    ReplyDelete
  4. Byte64Saturday, 06 August, 2011

    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.

    ReplyDelete
  5. ReddyMonday, 08 August, 2011

    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,

    ReplyDelete

I appreciate your comment however bear in mind that I might not have the time to reply soon.
Normally I do not reply to all comments but I am glad if you found something useful or if you learned something new, in that case I strongly encourage you to promote the article with the +1 google button.
Flavio