"ORA-00928: missing SELECT keyword" error while using sequence function

Question

Sorry I am new to SQLPlus stuffs!

So here, I have a table called iowe, i have a four records pre-loaded into it. This is how it looks like:

NAME           AMOUNT Serial Number
---------- ---------- -------------
Praveen         20500             1
Roshan           5000             2
Rohit            5000             3
Shashi           8000             4

Until I entered these four records, I did not know about the sequence function in SQL. So I tried implying it into this table. I wanted to input a new record, say "XXX" in name, 500 in Amount, and using the sequence command, i wanted the "Serial Number" to be auto incremented.

So I created a sequence called iowesqn, which looks like this, when i select * from user_sequences:

SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ---------- ---------- ------------ - - ---------- -----------
SQN                                     1          5            2 N N          0           3
IOWESQN                                 1 1.0000E+27            1 N N          0           7

Please disregard the sequence SQN

To insert the sequence IOWESQN, I used this command: "insert into iowe(name, amount, "Serial Number") values('XXX', 500, iowesqn.nextval)"

Everything works fine. The column Serial Number increments fine by 1 on every entry. However, when i try "insert into iowe ('&name', '&amount', "Serial Number") value(iowesqn.nextval));", it asks me fr the name, and the amount but right then (after the amount is input), it throws an error. It reads: "ORA-00928: missing SELECT keyword".

This is whole thing that comes up after the amount it input:

old 1: insert into iowe ('&name', '&amount', "Serial Number") value(iowesqn.nextval)) new 1: insert into iowe ('ret', 'ert', "Serial Number") value(iowesqn.nextval)) insert into iowe ('ret', 'ert', "Serial Number") value(iowesqn.nextval)) * ERROR at line 1: ORA-00928: missing SELECT keyword

Please tell me what I am (or (highly unlikely) it is) doing wrong.

Thanks in advance.

Answer 1

Your statement is wrong. Simple as that. With this fixed statement:

insert into iowe(name, amount, "Serial Number") values('XXX', 500, iowesqn.nextval)

You probably meant to replace your values by variables, not your fields?

insert into iowe(name, amount, "Serial Number") values('&name', &amount, iowesqn.nextval)

Answer 2

I just found another case where I get "missing SELECT keyword". I tried to insert with the column names in quotes, like this:

insert into subscription ('SUBSCRIPTION_ID','SUBSCRIPTION_NAME','CREATED_DATE') values ('558768','','20-JAN-20 10.37.47.901000000 PM');

Once I removed the quotes around the column names, it worked:

insert into subscription (SUBSCRIPTION_ID,SUBSCRIPTION_NAME,CREATED_DATE) values ('558768','','20-JAN-20 10.37.47.901000000 PM');

Answer 3

Other users may have been using value in stead of values. This is another case when you may face this problem.