Search code examples
sequencesqlplusora-00928

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

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.

Solution

  • 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)