OraFAQ Forum: SQL & PL/SQL » Problem in creating table with user defined data type

Home » SQL & PL/SQL » SQL & PL/SQL » Problem in creating table with user defined data type

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Problem in creating table with user defined data type [message #267422]

Thu, 13 September 2007 04:37

shilpa.rajput
Messages: 31
Registered: May 2006
Location: Pune

Member

Hello All,
Our developer is facing error (ORA-02331: cannot create constraint on column of datatype Named Table Type ) while creating following table based on User define data type;

If 'not null' constraint is removed in front of User define data type ,it gives (ORA-22913: must specify table name for nested table column or attribute) error.
The type definition is all ready exist in database definition in:

CREATE  TYPE TAB_PLANTS
  AS TABLE OF VARCHAR2(3); /

Following is the Table script

CREATE TABLE PLE_CONFIGURATIONS ( 
  ID                        NUMBER         NOT NULL, 
  CONFIGURATION_ID          NUMBER         NOT NULL, 
  CONFIGURATION_NO          VARCHAR2 (20)  NOT NULL, 
  ENGINE_FAMILY_NO          VARCHAR2 (3)   NOT NULL, 
  ENGINE_FAMILY             VARCHAR2 (20)  NOT NULL, 
  INTRODUCTORY_DATE         DATE           NOT NULL, 
  MATURITY_DATE             DATE           NOT NULL, 
  IS_ENABLED                VARCHAR2 (1)   NOT NULL, 
  REF_CONFIGURATION_NO      VARCHAR2 (20)  NOT NULL, 
  REF_ENGINE_FAMILY_NO      VARCHAR2 (3)   NOT NULL, 
  REF_ENGINE_FAMILY         VARCHAR2 (20)  NOT NULL, 
PLE_CONFIGURATION_PLANTS  TAB_PLANTS     NOT NULL, 
  REF_CONFIGURATION_PLANTS  TAB_PLANTS     NOT NULL, 
  NOTES                     VARCHAR2 (4000)NOT NULL, 
  LAST_MODIFIED_BY          VARCHAR2 (50)  NOT NULL, 
  LAST_MODIFIED_DATE        DATE           NOT NULL, 
  PRIMARY KEY ( ID ) 
    USING INDEX 
     TABLESPACE PROD_DATA PCTFREE 10
     STORAGE ( INITIAL 2097152 NEXT 2097152 PCTINCREASE 0 ))
   TABLESPACE PROD_DATA
   PCTFREE 10
   PCTUSED 40
   INITRANS 1
   MAXTRANS 255
  STORAGE ( 
   INITIAL 2097152
   NEXT 2097152
   PCTINCREASE 0
   MINEXTENTS 1
   MAXEXTENTS 2147483645
   FREELISTS 1 FREELIST GROUPS 1 )
   NOCACHE;

Any clues?
Thanks in advance.

Regards,
Shilpa

[Updated on: Thu, 13 September 2007 05:00]

Report message to a moderator

Re: Problem in creating table with user defined data type [message #267424 is a reply to message #267422]

Thu, 13 September 2007 04:45

Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Why there are so many blank lines in your post?
Please modify it to limit line size to 80 characters.

Regards
Michel

Report message to a moderator

Re: Problem in creating table with user defined data type [message #267447 is a reply to message #267422]

Thu, 13 September 2007 05:23

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

Firstly, your SQL doesn't work even if you remove the NN constraints on the nested column tables. You need to add some NESTED TABLE clauses to the bottom of the statement.

Secondly, from the documentation:

Oracle Database does not support constraints on columns or attributes whose type is a user-defined object, nested table, VARRAY, REF, or LOB, with two exceptions:

NOT NULL constraints are supported for a column or attribute whose type is user-defined object, VARRAY, REF, or LOB.

NOT NULL, foreign key, and REF constraints are supported on a column of type REF.

So, if you change tyour nested table to a VARRAY:

CREATE or replace TYPE TAB_PLANTS AS varray (n) of VARCHAR2(10);

then you can apply the not null constraint.

Report message to a moderator

Previous Topic:	Reference to uninitialized collection
Next Topic:	UNION RELATED

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Nov 13 17:34:47 CST 2025