Home » SQL & PL/SQL » SQL & PL/SQL » Problem in creating table with user defined data type
Problem in creating table with user defined data type [message #267422] Thu, 13 September 2007 04:37 Go to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Problem in creating table with user defined data type [message #267447 is a reply to message #267422] Thu, 13 September 2007 05:23 Go to previous message
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.
Previous Topic: Reference to uninitialized collection
Next Topic: UNION RELATED
Goto Forum:
  


Current Time: Sat Dec 10 07:11:12 CST 2016

Total time taken to generate the page: 0.09844 seconds