sql script not running

Question

Here is a code snippet of a sql script which is giving me error,I have to generate a sequence on the primary_key of the table without using triggers in oracle:

CREATE SEQUENCE t1_seq START WITH 1 INCREMENT BY 1;

DROP TABLE CPR_SOURCE_SYSTEM_METADATA;

CREATE TABLE CPR_SOURCE_SYSTEM_METADATA 

   (    
         SYSTEM_ID NUMBER(4) NOT NULL t1_seq.nextval,

         SYSTEM_NAME VARCHAR2(200),

         DATE_FORMAT VARCHAR2(200),

         CREATED_BY VARCHAR2(200),
         MODIFIED_BY VARCHAR2(200),
         CREATED_ON NUMBER(20),
         MODIFIED_ON NUMBER(20),
         IS_DELETED VARCHAR2(1),
         CONSTRAINT "CPR_SOURCE_SYSTEM_PK" PRIMARY KEY ("SYSTEM_ID")
 );

It is giving me the below error :

DROP TABLE CPR_SOURCE_SYSTEM_METADATA * ERROR at line 1: ORA-00942: table or view does not exist

SYSTEM_ID NUMBER(4) NOT NULL t1_seq.nextval, * ERROR at line 3: ORA-00907: missing right parenthesis

Not able to figure out the error,can anyone help??

You cannot specify sequence name directly in create table. In Oracle 12c you can also declare an identity column. For all other versions u can use triggers. Refer https://stackoverflow.com/questions/10613846/create-table-with-sequence-nextval-in-oracle — Kaushik Nayak, Sep 14 '17 at 12:23
Possible duplicate of [How to create id with AUTO\_INCREMENT on Oracle?](https://stackoverflow.com/questions/11296361/how-to-create-id-with-auto-increment-on-oracle) — Nitish, Sep 14 '17 at 12:24
that means we do not need to create any sequence specifically? — Steffi Das, Sep 14 '17 at 12:27
In 12c, if you wish to use an explicitly created sequence, instead of identity, one can default the sequence.nextval. In this case `SYSTEM_ID NUMBER(4) NOT NULL DEFAULT t1_seq.nextval`. http://www.oracle.com/technetwork/issue-archive/2013/13-sep/o53asktom-1999186.html — Shannon Severance, Sep 14 '17 at 15:02

score 2 · Answer 1 · answered Sep 14 '17 at 12:24

SYSTEM_ID NUMBER(4) NOT NULL t1_seq.nextval,

The t1_seq.nextval segment is not valid - you cannot specify an auto-incrementing column like that.

The SQL parser is expecting to see:

SYSTEM_ID NUMBER(4) NOT NULL,

and throws the exception as the comma is not where it expects.

In Oracle 12c you can use an identity column but in earlier versions you will either need to:

Use the sequence in the SQL insert statement;
Use a trigger to insert the correct sequence value; or
Create a stored procedure to handle inserts and manage the sequence through that (disallowing direct inserts that could bypass this).

sql script not running

1 Answers1