"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.

score 7 · Accepted Answer · answered Apr 04 '13 at 14:00

7

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)

answered Apr 04 '13 at 14:00

nvoigt

75,013
26
93
142

Sweet! It worked like a charm! It there a way to delete or change the LAST_NUMBER column in user_sequences for the sequence IOWESQN to anything else? Say 6? It is at 11 now. Is that even possible? Do you want me to ask this question in a new post? – Anonymous Person Apr 04 '13 at 14:23
If I remember correctly you cannot reset this number. You can however, recreate the sequence with (for example) 6 as new starting value. – nvoigt Apr 04 '13 at 15:12

score 4 · Answer 2 · answered Jan 23 '20 at 22:43

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');

thank you this worked for me when using golang with godror library — arash rahimi, Aug 11 '21 at 11:55

Abhishek Kashyap · Answer 3 · 2016-05-22T20:22:19.240

2

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

edited May 22 '16 at 20:22

answered May 22 '16 at 20:16

Abhishek Kashyap

3,332
2
18
20

score 0 · Answer 4 · answered Dec 19 '17 at 18:22

0

This is because you have missed to mention the columns list

insert into table_name(1,'alskdjflasf')

it should be like this insert into table_name (id,name)values(1.'lakjsdflasdf')

answered Dec 19 '17 at 18:22

shashigura

133
1
2
8

score -1 · Answer 5 · answered Jan 26 '23 at 04:14

-1

JUST ADD SINGLE QUOTES IN THE COLUMN NAMES

enter image description here

answered Jan 26 '23 at 04:14

Govind Kumar

1
1

While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. - [From Review](/review/late-answers/33710583) – Jeremie Jan 30 '23 at 20:01

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

5 Answers5