Missing keyword oracle ошибка

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's user avatar

Justin Cave

226k23 gold badges364 silver badges382 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 Cave's user avatar

Justin CaveJustin Cave

226k23 gold badges364 silver badges382 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 the SELECT...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 Keim's user avatar

Uwe KeimUwe Keim

39.3k56 gold badges174 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

Rene's user avatar

ReneRene

10.4k5 gold badges33 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

logixologist's user avatar

logixologistlogixologist

3,6844 gold badges28 silver badges46 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

ManhKM's user avatar

Late answer, but I just came on this list today!

CREATE TABLE assignment_20101120 AS SELECT * FROM assignment;

Does the same.

Taras's user avatar

Taras

2666 silver badges23 bronze badges

answered Nov 10, 2010 at 14:14

David's user avatar

DavidDavid

111 bronze badge

0

I am getting ORA-00905: missing keyword error when trying to run the following query in Oracle:

select distinct MONTH_ID
from    DSS_MAIN.DIM_TIME a1
where   Case When TO_CHAR(SYSDATE,'MM') < 3
Then TO_CHAR(a1.MONTH_START_DATE,'YYYYMM') Between (TO_CHAR(SYSDATE,'YYYY')-1)||'03' And TO_CHAR(SYSDATE,'YYYYMM')
Else TO_CHAR(a1.MONTH_START_DATE,'YYYYMM') Between TO_CHAR(SYSDATE,'YYYY')||'03' And TO_CHAR(SYSDATE,'YYYYMM')
End;

The individual Between clauses work fine, so I am assuming there is something wrong with the Case syntax.

Any ideas?

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.

  1. Create Table
  2. Create Index
  3. Create View
  4. Alter Tablespace
  5. 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.

ORA-00905

ORA-00905: потеряно ключевое слово

Причина:

Требуемое ключевое слово потеряно.

Действие:

Проверьте синтаксис команды и ваш спеллинг, поставьте потерянное ключевое слово, если необходимо выполните оператор снова.

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;

Понравилась статья? Поделить с друзьями:
  • Missing equal sign ошибка
  • Missing closing quote python ошибка
  • Missing associated label input ошибка
  • Missing 1 required positional argument self python ошибка
  • Mivue j60 ошибка карты памяти что делать