Missing keyword oracle ошибка - Решение и исправление самых разных ошибок на 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

Источник

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

Источник

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;

Источник

The «ORA-00905: Missing keyword» error in Oracle SQL occurs when the SQL statement is missing a required keyword, such as «FROM», «SELECT», or «WHERE». This error can occur for a variety of reasons, such as a typo in the SQL statement, a missing parenthesis, or a missing comma between two clauses in the SQL statement. In order to resolve this issue, there are several methods that can be followed.

Method 1: Check the SQL Statement for Typos and Missing Keywords

To fix the Oracle error ORA-00905: Missing keyword, you can check the SQL statement for typos and missing keywords. Here are the steps:

Check the SQL statement for any typos or missing keywords. Make sure all keywords are spelled correctly and all necessary keywords are included.

SELECT column1, column2 FROM table1 WHERE column3 = 'value';

Check for any missing or extra commas in the SQL statement. Commas are often used to separate columns or values in a SQL statement.

SELECT column1, column2, column3 FROM table1 WHERE column4 = 'value';

Check for any missing or extra parentheses in the SQL statement. Parentheses are often used to group conditions or subqueries in a SQL statement.

SELECT column1, column2 FROM table1 WHERE (column3 = 'value' AND column4 = 'value');

Check for any missing or extra quotation marks in the SQL statement. Quotation marks are often used to enclose string values in a SQL statement.

SELECT column1, column2 FROM table1 WHERE column3 = 'value' AND column4 = 'value';

By following these steps, you can identify and fix any typos or missing keywords in your SQL statement and resolve the ORA-00905 error.

Method 2: Verify the Syntax of the SQL Statement

To verify the syntax of an SQL statement in Oracle, you can use the SQLPlus tool. This tool allows you to enter SQL statements and verify their syntax before executing them. Here’s how to use SQLPlus to verify the syntax of an SQL statement:

Open a command prompt or terminal window and navigate to the directory where SQL*Plus is installed.
Type «sqlplus» and press Enter to start SQL*Plus.
Enter your username and password when prompted.
Type «set errorlogging on» to enable error logging.
Type your SQL statement and press Enter.
If the statement contains a syntax error, Oracle will return an error message that includes the keyword that is missing or incorrectly used.
Correct the syntax error and re-run the SQL statement.

Here’s an example of how to use SQL*Plus to verify the syntax of an SQL statement:

sqlplus
Enter username: myusername
Enter password: ********
set errorlogging on
SELECT * FROM mytable WHERE id = 1 AND name = 'John'

If there is a syntax error in the SQL statement, Oracle will return an error message that includes the missing keyword. For example, if the SQL statement is missing a comma, Oracle will return an error message like this:

ORA-00905: missing keyword

In this case, you would need to correct the syntax error by adding the missing comma:

SELECT * FROM mytable WHERE id = 1, AND name = 'John'

Then, you can re-run the SQL statement to verify that the syntax is correct.

Method 3: Ensure Proper Usage of Commas and Parentheses

When encountering the Oracle error «ORA-00905: Missing keyword», it is often due to improper usage of commas and parentheses in the SQL statement. Here are some examples and solutions to fix this error using proper comma and parenthesis usage:

Missing comma between column names:

SELECT column1 column2 FROM table_name;

Solution: Add a comma between column1 and column2

SELECT column1, column2 FROM table_name;

Missing parenthesis in subquery:

SELECT column1 FROM (SELECT column2 FROM table_name WHERE column3 = 'value';

Solution: Add a closing parenthesis at the end of the subquery

SELECT column1 FROM (SELECT column2 FROM table_name WHERE column3 = 'value');

Missing parenthesis in CASE statement:

SELECT column1, CASE WHEN column2 = 'value' THEN 'yes' ELSE 'no' END FROM table_name;

Solution: Add a closing parenthesis at the end of the CASE statement

SELECT column1, CASE WHEN column2 = 'value' THEN 'yes' ELSE 'no' END FROM table_name;

Missing comma between table names in JOIN statement:

SELECT column1 FROM table1 JOIN table2 ON table1.column1 = table2.column2 WHERE column3 = 'value';

Solution: Add a comma between table1 and JOIN

SELECT column1 FROM table1, JOIN table2 ON table1.column1 = table2.column2 WHERE column3 = 'value';

By ensuring proper usage of commas and parentheses in your SQL statements, you can avoid the «ORA-00905: Missing keyword» error and execute your queries successfully.

Method 4: Consult Oracle Documentation for Further Guidance

To fix the Oracle error ORA-00905: Missing keyword, we can consult the Oracle documentation for further guidance. The error message indicates that a keyword is missing in the SQL statement. To resolve this error, we need to identify the missing keyword and add it to the SQL statement.

Here are the steps to follow:

Identify the SQL statement causing the error.
Check the syntax of the SQL statement to ensure that all keywords are present and in the correct order.
Consult the Oracle documentation for the correct syntax of the SQL statement.
Add the missing keyword to the SQL statement.

Here is an example of a SQL statement that could cause the ORA-00905 error:

SELECT column1, column2 FROM table1 WHERE column3 = 'value'

If the error message indicates that a keyword is missing, we need to check the syntax of the SQL statement to ensure that all keywords are present and in the correct order. For example, if the error message indicates that the keyword «FROM» is missing, we can consult the Oracle documentation for the correct syntax of the SELECT statement:

SELECT column1, column2 FROM table1 WHERE column3 = 'value' FROM table1

In this example, we can see that the keyword «FROM» is missing from the SQL statement. We can consult the Oracle documentation for the correct syntax of the SELECT statement and add the missing keyword:

SELECT column1, column2 FROM table1 WHERE column3 = 'value'

By consulting the Oracle documentation for further guidance, we can identify and fix the missing keyword in the SQL statement causing the ORA-00905 error.

Источник