Error in query INSERT INTO

Question

Error in query INSERT INTO

Asked 7 years ago

Viewed 394 times

1

I’m new using Oracle SQL Developer and I’m trying to make the query run but this appearing error, see photo.

See if your table ID is auto_incremented, if it is not, you will always need to add the ID to the Insert.

– cido18

2017/11/25 at 21:41
cido18 put the ID and did not give

– Diana Madeira

2017/11/25 at 23:13

1 answer

Browser other questions tagged sql oracle sqldeveloper

You are not signed in. Login or sign up in order to post.

by pss1suporte • **1,629** points · Answer 1 · 2017-11-25T22:43:17+00:00

First it is necessary to check if the SEQUENCE of the PERSON table already exists. It is interesting to use a SEQUENCE to generate the values for primary key.

Use the CREATE SEQUENCE statement to create a sequence, which is a database object from which multiple users can generate unique integers. You can use sequences to automatically generate primary key values. [SQL Language Reference, 2016, 1.173 p.]

When a sequence number is generated, the sequence is incremented regardless of whether the transaction is committed or reversed. If two users simultaneously increment the same sequence, the sequence numbers that each user acquires may have gaps, because the sequence numbers are being generated by the other user. A user can never acquire the sequence number generated by another user. After a sequence value is generated by a user, that user can continue to access this value regardless of whether the sequence is incremented by another user. [SQL Language Reference, 2016, 1.173 p.]

CREATE SEQUENCE person_seq 
    START WITH 1000 
    INCREMENT BY 2
    NOCACHE
    NOCYCLE;

So you can test:

SELECT person_seq.nextval FROM DUAL;

Now yes you can perform the insert in the table:

INSERT INTO PERSON (PERSON_ID, PERSON_NAME, AGE)
    VALUES (person_seq.nextval,'Diana','28');

_{NOTE: Important to understand that there are many ways to do this. Create a Trigger and link the SEQUENCE with the INSERT and/or UPDATE table actions.}

Reference:
_{[SQL Language Reference, 2016], Copyright 1996, 2016, Oracle and/or its Affiliates, Oracle Database SQL Language Reference: 11g Release 2 (11.2). Available at: < https://docs.oracle.com/cd/E11882_01/server.112/e41084.pdf >. Accessed on 25 Nov. 2017}
_{[Java Developer’s Guide, 2009], Copyright 1996, 2016, Oracle and/or its Affiliates, Oracle Database Java Developer’s Guide: , 11g Release 1 (11.1) B31225-05. Available at: < https://docs.oracle.com/cd/B28359_01/java.111/b31225.pdf >. Accessed on 25 Nov. 2017}