Missing values keyword ошибка sql - Решение и исправление самых разных ошибок на TopOshibok.ru

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

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 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 KeimUwe Keim

39.6k57 gold badges175 silver badges291 bronze badges

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

Источник

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

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

You could add a sub-select as follows:

INSERT INTO supplier
(supplier_id, supplier_name)
SELECT account_no, name
FROM customers
WHERE city = 'Newark';

Источник