If those are supposed to be values you’re providing as substitution values, then you’re missing the values keyword:
insert into certf values (&certificate_id,&certificate_name,&certificate_content);
But you need the string values to be in quotes:
insert into certf values (&certificate_id,'&certificate_name','&certificate_content');
and you should supply the column names too:
insert into certf (certificate_id,certificate_name,certificate_content)
values (&certificate_id,'&certificate_name','&certificate_content');
With you current code the parser is seeing that first list of — possible, but actually invalid in this case — identifiers, i.e column names; because it hasn’t seen that values keyword yet. It’s treated as something like:
insert into certf (42,some_name,some_content);
And having done that, and when it still doesn’t see a values keyword or values list, it’s expecting this to be an insert ... select construct instead. You could do it that way:
insert into certf (certificate_id,certificate_name,certificate_content)
select &certificate_id,'&certificate_name','&certificate_content' from dual;
But you aren’t doing that. So it doesn’t see the select either, and it throws the error you see.
ORA-00928: missing SELECT keyword error occurs when the select or values keyword is missing in the insert statement. If the SELECT keyword is missing while creating a view, the “ORA-00928: missing SELECT keyword” error occurs. In the insert statement, either the select or values keywords should be used. The values keyword is used to insert the table with data. Use the select keyword to insert values from other tables. The error ORA-00928: missing SELECT keyword occurs if none of the keywords are present in the insert statement.
The insert statement should include the value keyword followed by the data to be saved in the table. The error will occur if the insert statement lacks the values keyword or misspells the values keyword. In the insert statement, Oracle looks for the values keyword. If the values keyword is not found, the select keyword is searched. If none of the keywords are found, the error ORA-00928: missing SELECT keyword is thrown.
The Problem
An insert statement is created without the use of the select or values keywords. The insert statement should include either the select or values keywords. The error will be thrown if none of the keywords are available. The inserted data should be added to the values block as comma separated data. If the values block is missing, the insert command will be unable to insert data into the table. The select keyword is used to retrieve data from one table and insert it into another.
insert into employee('id','name') values (1,'test');Error
insert into employee('id','name') values (1,'test')
Error at Command Line : 5 Column : 22
Error report -
SQL Error: ORA-00928: missing SELECT keyword
00928. 00000 - "missing SELECT keyword"
*Cause:
*Action:Solution 1
The insert statement specifies a column name and the values to be stored in that column. The column name must not be entered as a string. The error ORA-00928: missing SELECT keyword will be resolved if the enclosed quotes are removed from the column name. The data to be entered into the value should be surrounded by quotes. The column name should not be surrounded by quotes.
Error
insert into employee('id','name') values (1,'test');Solution
insert into employee(id,name) values (1,'test');Solution 2
The error ORA-00928: missing SELECT keyword will occur if the values keyword is not present in the insert statement. The values keyword is used to add data in a comma separated format. If the values keyword is not present in the insert statement, the insert statement will be unable to locate the inserted data from the statement. Oracle will generate an error.
Error
insert into employee (1,'test');Solution
insert into employee values (1,'test');Solution 3
In the insert statement, the keyword values is misspelt. In the insert statement, look for the values keyword. The inserted data should be placed in the VALUES block as a series of comma-separated values. The error ORA-00928: missing SELECT keyword will occur if the VALUES keyword is misspelt.
Error
insert into employee valus (1,'test');Solution
insert into employee values (1,'test');Solution 4
If the select statement is missing from the insert statement, include it in the insert. The select statement retrieves data from one or more tables and inserts it into another table using the insert statement in conjunction with the select statement. The syntax for inserting data from another table using the select statement is demonstrated in the following example.
insert into employee select * from manager;Solution 5
When creating a view in the database, the select keyword is used to get data from one or more tables. If the select keyword is missing from the CREATE VIEW statement, the view cannot be created because the select sql to fetch the data could not be found. Check the select keyword in the CRATE VIEW statement if you’re creating a view. In the CREATE VIEW statement, include the SELECT keyword. This will resolve the error ORA-00928: missing SELECT keyword.
Error
create view empview as employees.first_name, employees.last_name from employees;Solution
create view empview as select employees.first_name, employees.last_name from employees;I’m using the following code to insert data. But I’m receiving an error as "ORA-00928: missing SELECT keyword"
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
java.sql.Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@xxx.xxx.x.xxx:xxxx:xxxx", "xxxx", "xxxx");
String query="insert into offer1('RCODE','OFFERNO','DAT') values(?,?,?)";
PreparedStatement ps=conn.prepareStatement(query);
ps.setString(1,r_code);
ps.setString(2,offerno);
ps.setDate(3,sqlDate);
ResultSet rs=ps.executeQuery();
out.println("data inserted");
}catch(Exception e)
{
out.println(e);
}
I can’t see any errors in this code. If someone finds, please tell me what is the mistake and how to solve it?
Vlad Mihalcea
143k71 gold badges566 silver badges915 bronze badges
asked Feb 6, 2013 at 5:53
1
single quotes are for string literals not for identifiers only so you should remove it around the columnNames.
INSERT INTO offer1 (RCODE,OFFERNO,DAT) VALUES (?,?,?)
and use executeUpdate since you are not retrieving records which results a resultset.
from DOCS
boolean execute()
- Executes the SQL statement in this PreparedStatement object, which may be any kind of SQL statement.
ResultSet executeQuery()
- Executes the SQL query in this PreparedStatement object and returns the ResultSet object generated by the query.
int executeUpdate()
- Executes the SQL statement in this PreparedStatement object, which must be an SQL INSERT, UPDATE or DELETE statement; or an SQL statement that returns nothing, such as a DDL statement.
answered Feb 6, 2013 at 5:56
John WooJohn Woo
259k69 gold badges498 silver badges492 bronze badges
Without the single quotes, try
String query="insert into offer1(RCODE,OFFERNO,DAT) values(?,?,?)";
Linga
10.4k10 gold badges52 silver badges105 bronze badges
answered Feb 6, 2013 at 5:54
OrangecrushOrangecrush
1,9702 gold badges15 silver badges26 bronze badges
1
Please try this
String query="insert into offer1(RCODE,OFFERNO,DAT) values(?,?,?)";
answered Feb 6, 2013 at 5:56
MadhavMadhav
2,2953 gold badges17 silver badges16 bronze badges
1
I was running the same issue, and in my case the query was like this:
insert into Address (number, street, id) values (?, ?, ?)
The problem was caused by the number column name since number is a reserved keyword in Oracle, and the exception was «ORA-00928: missing SELECT keyword».
So, the number column name must be escaped, like this:
insert into Address ("number", street, id) values (?, ?, ?)
and everything works fine now.
answered Apr 27, 2016 at 8:57
Vlad MihalceaVlad Mihalcea
143k71 gold badges566 silver badges915 bronze badges
0
Try Statement.executeUpdate instead of executeQuery.
answered Feb 6, 2013 at 5:56
yshavityshavit
42.3k7 gold badges87 silver badges124 bronze badges
0
ResultSet executeQuery()
Executes the SQL query in this PreparedStatement object and returns the ResultSet object generated by the query.
int executeUpdate()
Executes the SQL statement in this PreparedStatement object, which must be an SQL INSERT, UPDATE or DELETE statement; or an SQL statement that returns nothing, such as a DDL statement.
So instead of using ResultSet rs = ps.executeQuery(); use int result = ps.executeUpdate();
it returns either the row count for INSERT, UPDATE or DELETE statements, or 0 for SQL statements that return nothing
answered Feb 6, 2013 at 5:59
0
Learn the cause and how to resolve the ORA-00928 error message in Oracle.
Description
When you encounter an ORA-00928 error, the following error message will appear:
- ORA-00928: missing SELECT keyword
Resolution
The option(s) to resolve this Oracle error are:
Option #1
Correct the CREATE VIEW statement and re-execute it.
For example, if you had tried to create a view as follows:
CREATE VIEW sup_orders AS suppliers.supplier_id, orders.quantity, orders.price FROM suppliers INNER JOIN orders ON suppliers.supplier_id = orders.supplier_id WHERE suppliers.supplier_name = 'IBM';
You would receive the following error message:
You could correct the CREATE VIEW statement by including the SELECT keyword as follows:
CREATE VIEW sup_orders AS SELECT suppliers.supplier_id, orders.quantity, orders.price FROM suppliers INNER JOIN orders ON suppliers.supplier_id = orders.supplier_id WHERE suppliers.supplier_name = 'IBM';
ORA-00905 is a very broadly used error message, any expected keyword missing from its statement will result in ORA-00905. These are only cases we met.
ORA-00905 means that an expected keyword is missing from the statement at the specific position of statement, usually, it’s a syntax error.
In reality, this error has widely been seen in many statements if there’s any of the following problems:
- Missing keyword
- Misspelling keyword
SQL parser always knows what keyword should be there. If your case is neither of above problems, you may leave a comment to this post.
Let’s see some error patterns.
- Create Table
- Create Index
- Create View
- Alter Tablespace
- Grant Privilege
Create Table
NOT NULL
SQL> create table fruits (fruit_name varchar2(20) not, price number);
create table fruits (fruit_name varchar2(20) not, price number)
*
ERROR at line 1:
ORA-00905: missing keyword
In this case, we missed NULL keyword.
SQL> create table fruits (fruit_name varchar2(20) not null, price number);
Table created.
DOUBLE PRECISION
SQL> create table fruits (fruit_name varchar2(20) not null, price double);
create table fruits (fruit_name varchar2(20) not null, price double)
*
ERROR at line 1:
ORA-00905: missing keyword
In this case, we missed PRECISION keyword.
SQL> create table fruits (fruit_name varchar2(20) not null, price double precision);
Table created.
Create Index
SQL> create index birth_date_idx employees(birth_date);
create index birth_date_idx employees(birth_date)
*
ERROR at line 1:
ORA-00969: missing ON keyword
In this case, we missed ON keyword.
SQL> create index birth_date_idx on employees(birth_date);
Index created.
Create View
SQL> create view happy_employees select * from employees where salary >= 10000;
create view happy_employees select * from employees where salary >= 10000
*
ERROR at line 1:
ORA-00905: missing keyword
In this case, it turns out that we missed the keyword AS in the statement.
SQL> create view happy_employees as select * from employees where salary >= 10000;
View created.
Alter Tablespace
SQL> alter tablespace example add '/u01/app/oracle/oradata/ORCLCDB/ORCLPDB1/example02.dbf' size 10m autoextend on next 10m maxsize unlimited;
alter tablespace example add '/u01/app/oracle/oradata/ORCLCDB/ORCLPDB1/example01.dbf' size 10m autoextend on next 10m maxsize unlimited
*
ERROR at line 1:
ORA-00905: missing keyword
In this case, we missed the keyword DATAFILE in the statement.
SQL> alter tablespace example add datafile '/u01/app/oracle/oradata/ORCLCDB/ORCLPDB1/example02.dbf' size 10m autoextend on next 10m maxsize unlimited;
Tablespace altered.
Grant Privilege
SQL> grant select any table hr;
grant select any table hr
*
ERROR at line 1:
ORA-00905: missing keyword
In this case, we missed TO keyword.
SQL> grant select any table to hr;
Grant succeeded.
Keywords
To correctly use keywords, you can query the dynamic dictionary V$RESERVED_WORDS for sure.
Reserved Keywords
SQL> select keyword from v$reserved_words where reserved = 'Y' order by 1;
Oracle Keywords
SQL> select keyword from v$reserved_words where reserved = 'N' order by 1;
Don’t worry about the error ORA-00905 too much, it always points out the position where keyword missed. Another similar error ORA-02142 might also be thrown in ALTER TABLESPACE ADD DATAFILE statements.