CODEWITHSUNDEEP
oracle-databasesequence
Steffi Das
Steffi Das

Reputation: 1

sql script not running

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??

Upvotes: 0

Views: 76

Answers (1)

MT0
MT0

Reputation: 167981

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:

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

Upvotes: 2

Related Questions