Error pops up while creating the table

Question

While creating this table am getting the error, can you help with this :

CREATE TABLE FIPR_MASTER ( 
   FIPR_Id   INT NOT NULL GENERATED ALWAYS 
      AS IDENTITY 
      (START WITH 100 
       INCREMENT BY 1 
       MINVALUE 100 
       NO MAXVALUE 
       NO CYCLE 
       NO CACHE 
       ORDER) PRIMARY KEY, 

   FIPR_NAME VARCHAR2(40) NOT NULL, 
   BU_NAME    VARCHAR2(40) NOT NULL, 
   LOCATION VARCHAR2(40) NOT NULL, 
   RECORD_SOURCE_NAME   VARCHAR2(200) NOT NULL, 
   PRIORITY_QUEUE  VARCHAR2(40) NOT NULL, 
   STATUS  VARCHAR2(40) NOT NULL,
   INDEXING_TYPE VARCHAR2(40) NOT NULL,
   SOURCE_PATH  VARCHAR2(200) NOT NULL,
   DESTINATION_PATH  VARCHAR2(200) NOT NULL,
   COMMENTS VARCHAR2(200) NOT NULL
   )

Error :

CREATE TABLE FIPR_MASTER ( 
   FIPR_Id   INT NOT NULL GENERATED ALWAYS 
      AS IDENTITY 
      (START WITH 100 
       INCREMENT BY 1 
       MINVALUE 100 
       NO MAXVALUE 
       NO CYCLE 
       NO CACHE 
       ORDER) , 

   FIPR_NAME VARCHAR2(40) NOT NULL, 
   BU_NAME    VARCHAR2(40) NOT NULL, 
   LOCATION VARCHAR2(40) NOT NULL, 
   RECORD_SOURCE_NAME   VARCHAR2(200) NOT NULL, 
   PRIORITY_QUEUE  VARCHAR2(40) NOT NULL, 
   STATUS  VARCHAR2(40) NOT NULL,
   INDEXING_TYPE VARCHAR2(40) NOT NULL,
   SOURCE_PATH  VARCHAR2(200) NOT NULL,
   DESTINATION_PATH  VARCHAR2(200) NOT NULL,
   COMMENTS VARCHAR2(200) NOT NULL, PRIMARY KEY(FIPR_Id)
   )
Error report -
SQL Error: ORA-00907: missing right parenthesis
00907. 00000 -  "missing right parenthesis"
*Cause:    
*Action:

Which Oracle version? If I remember well, you can use this from 12c on — Aleksej, Jan 03 '17 at 11:09
Possible duplicate of [create table with sequence.nextval in oracle](http://stackoverflow.com/questions/10613846/create-table-with-sequence-nextval-in-oracle) — XING, Jan 03 '17 at 11:15
If you want auto increment id, check this link `http://stackoverflow.com/questions/11296361/how-to-create-id-with-auto-increment-on-oracle` — XING, Jan 03 '17 at 11:16
and when am removing the sequence creation part ..then am able to create the table ..but without making the FIPR_Id as a sequence . — Theodore, Jan 03 '17 at 18:25

Boneist · Answer 1 · 2017-01-03T12:32:09.917

You've a few issues with your DDL statement:

The identity column must be declared before any inline constraints (not null counts as an inline constraint)
Seeing as you're declaring this as a primary key (of which, one of the definitions is that the pk columns are not null), there is no need to explicitly state not null anyway!
There should be no gaps between the NO and the MAXVALUE, CYCLE, and CACHE.

The following statement will work for you, assuming you are on 12.1 or above:

CREATE TABLE FIPR_MASTER ( 
   FIPR_Id   INT GENERATED ALWAYS 
      AS IDENTITY 
      (START WITH 100 
       INCREMENT BY 1 
       MINVALUE 100 
       NOMAXVALUE 
       NOCYCLE 
       NOCACHE 
       ORDER) PRIMARY KEY, 

   FIPR_NAME VARCHAR2(40) NOT NULL, 
   BU_NAME    VARCHAR2(40) NOT NULL, 
   LOCATION VARCHAR2(40) NOT NULL, 
   RECORD_SOURCE_NAME   VARCHAR2(200) NOT NULL, 
   PRIORITY_QUEUE  VARCHAR2(40) NOT NULL, 
   STATUS  VARCHAR2(40) NOT NULL,
   INDEXING_TYPE VARCHAR2(40) NOT NULL,
   SOURCE_PATH  VARCHAR2(200) NOT NULL,
   DESTINATION_PATH  VARCHAR2(200) NOT NULL,
   COMMENTS VARCHAR2(200) NOT NULL
   );

Error pops up while creating the table

1 Answers1