Excuting the line of SQL:
SELECT *
INTO assignment_20081120
FROM assignment ;
against a database in oracle to back up a table called assignment gives me the following ORACLE error:
ORA-00905: Missing keyword
Justin Cave
228k24 gold badges368 silver badges384 bronze badges
asked Nov 20, 2008 at 15:06
1
Unless there is a single row in the ASSIGNMENT
table and ASSIGNMENT_20081120
is a local PL/SQL variable of type ASSIGNMENT%ROWTYPE
, this is not what you want.
Assuming you are trying to create a new table and copy the existing data to that new table
CREATE TABLE assignment_20081120
AS
SELECT *
FROM assignment
answered Nov 20, 2008 at 15:12
Justin CaveJustin Cave
228k24 gold badges368 silver badges384 bronze badges
First, I thought:
«…In Microsoft SQL Server the
SELECT...INTO
automatically creates
the new table whereas Oracle seems to
require you to manually create it
before executing theSELECT...INTO
statement…»
But after manually generating a table, it still did not work, still showing the «missing keyword» error.
So I gave up this time and solved it by first manually creating the table, then using the «classic» SELECT
statement:
INSERT INTO assignment_20081120 SELECT * FROM assignment;
Which worked as expected. If anyone come up with an explanaition on how to use the SELECT...INTO
in a correct way, I would be happy!
answered Sep 28, 2009 at 6:34
Uwe KeimUwe Keim
39.6k57 gold badges175 silver badges291 bronze badges
1
You can use select into inside of a PLSQL block such as below.
Declare
l_variable assignment%rowtype
begin
select *
into l_variable
from assignment;
exception
when no_data_found then
dbms_output.put_line('No record avialable')
when too_many_rows then
dbms_output.put_line('Too many rows')
end;
This code will only work when there is exactly 1 row in assignment. Usually you will use this kind of code to select a specific row identified by a key number.
Declare
l_variable assignment%rowtype
begin
select *
into l_variable
from assignment
where ID=<my id number>;
exception
when no_data_found then
dbms_output.put_line('No record avialable')
when too_many_rows then
dbms_output.put_line('Too many rows')
end;
answered Sep 28, 2009 at 10:51
ReneRene
10.4k5 gold badges34 silver badges46 bronze badges
Though this is not directly related to the OP’s exact question but I just found out that using a Oracle reserved word in your query (in my case the alias IN
) can cause the same error.
Example:
SELECT * FROM TBL_INDEPENTS IN
JOIN TBL_VOTERS VO on IN.VOTERID = VO.VOTERID
Or if its in the query itself as a field name
SELECT ..., ...., IN, ..., .... FROM SOMETABLE
That would also throw that error. I hope this helps someone.
answered Oct 10, 2017 at 20:27
logixologistlogixologist
3,7144 gold badges28 silver badges47 bronze badges
If you backup a table in Oracle Database. You try the statement below.
CREATE TABLE name_table_bk
AS
SELECT *
FROM name_table;
I am using Oracle Database 12c.
answered Nov 2, 2020 at 10:25
Late answer, but I just came on this list today!
CREATE TABLE assignment_20101120 AS SELECT * FROM assignment;
Does the same.
Taras
2666 silver badges23 bronze badges
answered Nov 10, 2010 at 14:14
DavidDavid
111 bronze badge
0
I am getting the following error and I am not sure why
INSERT INTO
PRODUCT_TYPE, PANELIST_PROD_TYPE_DETAIL
(TYPE_DESC, PRODUCT_TYPE_NUM)
VALUES('JR' , 0)
Error
Missing VALUES keyword
I am not sure why I am getting this error as VALUES
is in the SQL statement.
TYPE_DESC
is in table PRODUCT_TYPE
whereas PRODUCT_TYPE_NUM
is in table PANELIST_PROD_TYPE_DETAIL
.
Can someone please tell me what is wrong with my SQL statement?
marc_s
734k176 gold badges1332 silver badges1460 bronze badges
asked Aug 29, 2012 at 13:28
0
You need to insert to each table individually like this:
INSERT into PRODUCT_TYPE(TYPE_DESC) VALUES('JR');
INSERT INTO PANELIST_PROD_TYPE_DETAIL(PRODUCT_TYPE_NUM) VALUES(0);
answered Aug 29, 2012 at 13:31
Mahmoud GamalMahmoud Gamal
78.3k17 gold badges140 silver badges164 bronze badges
You need to create one insert statement per table:
INSERT into PRODUCT_TYPE(TYPE_DESC) VALUES('JR');
INSERT into PANELIST_PROD_TYPE_DETAIL (PRODUCT_TYPE_NUM) VALUES(0);
answered Aug 29, 2012 at 13:31
Daniel HilgarthDaniel Hilgarth
171k40 gold badges335 silver badges443 bronze badges
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.
The ORA-00905: missing keyword error occurs when the Oracle parser expects a keyword in the sql query but it is missing. The error is displayed to signify a malformed statement, in which the Oracle parser indicates that a keyword is missing from a statement. The syntax and format of the SQL Statement should be reviewed. If any keywords are missing from the SQL query, they should be added to resolve the error ORA-00905: missing keyword.
The sql statement must be written in the proper syntax and structure. If a keyword is missing from the SQL Statement, an error message ORA-00905: missing keyword will be displayed. The cause might be incorrect SQL Statement use or syntax that Oracle does not support. The Oracle parser anticipates a reserved keyword that is not present in the SQL query. The keyword must be identified and included in the SQL statement.
When this ORA-00905 error occurs
If an incorrect SQL Statement is executed, or if the SQL Statement contains syntax that Oracle does not support, an error message ORA-00905: missing keyword will be displayed. Oracle may allow SQL Statements in formats other than the standards. Oracle standards should be followed when writing the sql statement.
select * into manager from emp;
ORA-00905: missing keyword
00905. 00000 - "missing keyword"
*Cause:
*Action:
Error at Line: 15 Column: 15
Root Cause
Before executing the SQL Statement, Oracle parses it. The Oracle parser anticipates the presence of a reserved keyword in the SQL Statement. Oracle was unable to interpret the SQL Statement further and hence could not execute it. To rectify the issue ORA-00905: missing keyword, the missing keyword should be added to the SQL Statement.
Solution 1
If the SQL Statement is written in a format that Oracle does not accept, the SQL Statement should be modified to conform to Oracle standards. Oracle might have used a different format. The SQL Statement should be prepared in the format that the Oracle parser expects. To fix this issue ORA-00905: missing keyword, the SQL Statement format should be corrected.
Problem
select * into manager from emp;
ORA-00905: missing keyword
00905. 00000 - "missing keyword"
Solution A
create table manager as select * from emp;
Solution B
insert into manager select * from emp;
Solution 2
If you run a SQL statement that contains PL/SQL code, the SQL statement will fail in Oracle. Before running the SQL Statement, the PL/SQL code should be deleted. Alternatively, the SQL query should be performed within a PL/SQL statement block. Within the POL/SQL block, the Oracle parser may parse the sql statement.
Problem
select * into manager from emp;
ORA-00905: missing keyword
00905. 00000 - "missing keyword"
Solution
Declare
manager assignment%rowtype
begin
select *
into manager
from assignment;
exception
when no_data_found then
dbms_output.put_line('No rows available')
when too_many_rows then
dbms_output.put_line('More than one row found')
end;
Learn the cause and how to resolve the ORA-00926 error message in Oracle.
Description
When you encounter an ORA-00926 error, the following error message will appear:
- ORA-00926: missing VALUES keyword
Resolution
The option(s) to resolve this Oracle error are:
Option #1
Try adding the missing VALUES keyword or use a sub-select. Then re-execute the statement.
For example, if you tried to execute the following:
INSERT INTO suppliers;
You would receive the following error message:
You could correct this error either by adding the missing VALUES keyword, as follows:
INSERT INTO suppliers (supplier_id, supplier_name) VALUES (1000, 'IBM');
OR
You could add a sub-select as follows:
INSERT INTO supplier (supplier_id, supplier_name) SELECT account_no, name FROM customers WHERE city = 'Newark';