How to insert a "Sequence Value" into a Number datatype? - SQL

Question

I'm trying to fill a table, and on my primary key value (number) I would like to insert a sequence, so I will make sure I won't be repetitive.

CREATE TABLE Student (
    Student_ID number(3) CONSTRAINT Student_ID_pk PRIMARY KEY,
    First_Name VARCHAR2(14) CONSTRAINT First_name_NN  NOT NULL,
    Last_Name VARCHAR(14) CONSTRAINT Last_name_NN NOT NULL
    );

CREATE SEQUENCE STUDENT_ID_SQ 
                INCREMENT BY 111
                START WITH 111
                NOMAXVALUE 
                NOCYCLE;


INSERT INTO Student (Student_ID,First_Name,Last_Name)
        VALUES (STUDENT_ID_SQ.NEXTVAL, "Jhon","Smith");

Error:

Error starting at line : 14 in command -
INSERT INTO Student (Student_ID,First_Name,Last_Name)
        VALUES (STUDENT_ID_SQ.NEXTVAL, "Jhon","Smith")
Error at Command Line : 15 Column : 47
Error report -
SQL Error: ORA-00984: column not allowed here
00984. 00000 -  "column not allowed here"

GMB · Accepted Answer · 2019-09-29T20:07:41.543

You immediate problem is that you have put double quotes around the strings that you want to insert. Hence Oracle considers them as column names instead of strings. Use single quotes instead:

INSERT INTO Student (Student_ID,First_Name,Last_Name) 
    VALUES (STUDENT_ID_SQ.NEXTVAL, 'Jhon','Smith');

Demo on DB Fiddle

Also, instead of invoking the sequence's next val for each insert command, I would recommend creating a trigger to handle that use case:

CREATE OR REPLACE TRIGGER TRG_STUDENT_ID 
BEFORE INSERT ON STUDENT
FOR EACH ROW 
BEGIN 
    IF :NEW.STUDENT_ID  IS NULL THEN
    SELECT STUDENT_ID_SQ.NEXTVAL INTO :NEW.STUDENT_ID FROM DUAL;
END IF;
END;
/

Then you can simply do:

INSERT INTO Student (First_Name,Last_Name) VALUES ('John','Smith');

The trigger will automatically invoke the sequence and assign its next value to Student_ID.

Demo on DB Fiddle

How to insert a "Sequence Value" into a Number datatype? - SQL

1 Answers1