Sql error 905 42000 ora 00905 отсутствует ключевое слово - Исправление ошибок и поиск оптимальных решений проблем

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

225k23 gold badges360 silver badges377 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

225k23 gold badges360 silver badges377 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

39k56 gold badges175 silver badges289 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.3k5 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

logixologistlogixologist

3,6744 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

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

CREATE TABLE assignment_20101120 AS SELECT * FROM assignment;

Does the same.

Taras

2566 silver badges23 bronze badges

answered Nov 10, 2010 at 14:14

DavidDavid

111 bronze badge

Источник

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;

Источник

Reserved terms and keywords are two words in Oracle that have unique meanings within the software.

When coding in Oracle, the error ORA-00905 is likely to occur frequently. Fortunately, it’s much less complicated and time-consuming to fix than any of the other ORA errors you’ll encounter while working with Oracle. The action for this mistake, according to Oracle documentation, is to “correct the syntax.” The solution to ORA-00905 entails determining which keyword is missing and where it should be inserted. The ORA-00905 error code is used to denote a malformed sentence in which the Oracle parser detects a missing keyword.

(Namespaces are another term with a special significance in Oracle, but they have little to do with the topic of ORA-00905. Reserved terms are those that can’t be redefined and thus can’t be used to describe database objects like columns or tables. Oracle has predefined these terms and they will all have the same meaning. Keywords are words that have a significant significance for Oracle but are not reserved terms and can therefore be redefined. Some keywords, on the other hand, could become reserved keywords in the future, so they should be used with caution when used as variable or function names. A list of keywords can be found here.

Quick Navigation

1 The Problem
2 ORA-00905: a keyword is missing.
3 The Solution
4 You can examine the execution plan of a SQL statement using one of two methods:
5 The following are the reasons for DBMS XPLAN.DISPLAY:
6 Formatting
- 6.1 Section of Notes
- 6.2 Bind Peeking
7 Explanation of the EXPLAIN PLAN
8 Changes in Execution Plans
- 8.1 Various Schemas
- 8.2 Various Rates
9 Conclusion

The Problem

When a required keyword is missing, the ORA-00905 error occurs. The following will be the error message

As indicated by the message, the code lacks a keyword where one is required for the question to run successfully

The Solution

Most DBAs, SQL developers, and performance experts are familiar with generating and viewing the execution plan of a SQL statement because it provides them with details on the statement’s performance characteristics. The steps required to execute a SQL statement are shown in detail in an execution plan. These steps are encapsulated in a set of database operators that consume and generate rows. The query optimizer uses a mixture of query transformations and physical optimization techniques to determine the order of the operators and how they are implemented.

The plan is tree-shaped, even though it is commonly displayed in a tabular format. Consider the following query base as an example.

When reading a plan tree, begin at the bottom and work your way up. Begin by looking at the access operators in the preceding example (or the leaves of the tree). In this case, absolute table scans are used to enforce the access operators. The join operator will consume the rows generated by these table scans. The join operator is a hash-join in this case (other alternatives include nested-loop or sort-merge join). Finally, the hash-based group-by operator (sort would be an alternative) consumes the rows generated by the join-operator.

The query optimizer considers several different execution plans when generating an execution plan for a SQL statement.

You can examine the execution plan of a SQL statement using one of two methods:

The EXPLAIN PLAN command shows a SQL statement’s execution plan without actually executing the statement.
V$SQL PLAN – A dictionary view that displays the execution plan for a SQL statement in the cursor cache that has been compiled into a cursor.

In some circumstances, the plan displayed by the EXPLAIN PLAN command can vary from the plan displayed by V$SQL PLAN. When the SQL statement includes bind variables, for example, the plan generated by EXPLAIN PLAN ignores the bind variable values, while the plan generated by V$SQL PLAN considers the bind variable values.

The following are the reasons for DBMS XPLAN.DISPLAY:

Name of the plantable (default: PLAN TABLE)
Statement id is an identifier for a statement (default NULL)
(‘TYPICAL’ is the default)
ORACLE HOME/rdbms/admin/dbmsxpln.sql contains more details.

Formatting

The format argument is highly customizable and allows you to see as little (high-level) or as many (low-level) details as you need/want in the planned output. The high-level options are:

The Fundamentals

The process, options, and the name of the object are all included in the plan (table, index, MV, etc)

It contains the data displayed in BASIC as well as internal optimizer data such as cost, size, cardinality, and so on. This information is shown for each operation in the plan and reflects the optimizer’s estimation of the cost of the operation, the number of rows generated, and so on. It also demonstrates how the procedure tests the predicates. ACCESS and FILTER are the two kinds of predicates. Since they refer to the search columns, the ACCESS predicates for an index are used to fetch the appropriate blocks. After the blocks have been fetched, the FILTER predicates are evaluated.

Section of Notes

In addition to the plan, the kit contains notes in the NOTE section, such as whether dynamic sampling was used during query optimization or whether the query was transformed using star transformation.

If the table SALES, for example, does not have figures, the optimizer will use dynamic sampling and the plan display will show the following (see the s’+note’ information in the query)

Bind Peeking

When generating an execution plan, the query optimizer considers the values of bind variables. It conducts what is known as bind peeking. See the previous posts on bind peeking and its effect on SQL statement plans and results

As previously mentioned, the plan displayed in V$SQL PLAN takes into account the values of bind variables, while the plan displayed in EXPLAIN PLAN does not. The DBMS XPLAN package allows you to see the bind variable values that were used to create a specific cursor/plan. When using the display cursor, simply add ‘+peeked binds’ to the format statement.

Explanation of the EXPLAIN PLAN

The EXPLAIN PLAN statement shows the Oracle optimizer’s choices for Choose, UPDATE, INSERT, and DELETE statements. The execution plan for a statement is the order in which Oracle executes the statement.

The execution plan’s heart is the row source tree. It displays the following data
An arrangement of the tables listed in the statement.
Each table listed in the statement has an access process.
Join methods for tables that are affected by the statement’s join operations.

Filtering, sorting, and aggregation is examples of data operations.
The plantable includes information about the following in addition to the row source tree:
The cost and cardinality of each operation are examples of optimization.
Partitioning, such as the number of accessed partitions, is an example of partitioning.
The distribution method of join inputs, for example, is a good example of parallel execution.
You may use the EXPLAIN PLAN results to see whether the optimizer chooses a specific execution plan, such as nested loops enter. It also enables you to comprehend optimizer decisions, such as why the optimizer chose a nested loop to join over a hash join, and it allows you to comprehend query results.

Changes in Execution Plans

Execution plans may and do change with the cost-based optimizer as the underlying costs change. The EXPLAIN PLAN performance illustrates how Oracle executes the SQL statement after it has been clarified. Because of variations in the execution environment and describe plan environment, this will vary from the plan for a SQL statement during actual execution.

The following factors will cause execution plans to differ:

Different Schemas
Different Costs

Various Schemas

The implementation and explanation plans are carried out on separate databases.
The user who describes the argument is not the same as the user who implements it. Different execution plans can result from two users pointing to different objects in the same database.

Between the two operations, there are schema changes (usually index changes).

Various Rates

If the costs are different, the optimizer may select different execution plans even if the schemas are the same. The following are some of the factors that influence costs
Volume and statistics of data
Bind variable types together
Set global or session-level initialization parameters
EXPLAIN PLAN is currently in use.
Using the following to clarify a SQL statement
EXPLAIN THE Strategy

Consider the following scenario

EXPLAIN THE Strategy
FROM staff, Choose the last name;

This clarifies the PLAN TABLE table’s plan. The execution plan can then be chosen from the PLAN TABLE. If you don’t have any other plans in PLAN TABLE or just want to look at the last statement, this is useful.

Conclusion

The error arises in ORACLE and can be solved if you check any of your SQL statements i.e. INSERT, UPDATE, and DELETE. Basically, the error clearly mentions and indicates some malformed statement where ORACLE parser gives some missing keyword issues.

This doesn’t allow query to run and creates issues. Last but not the least, you should check all of the statements you have prepared and also have a look at syntax where you have mentioned everything. You can also check manual for S QL where all the details have been clearly mentioned and addresses along with the issues, which may arise if rules are not followed properly.

Источник

Содержание

ORA-00905: missing keyword
When this ORA-00905 error occurs
Root Cause
Solution 1
Problem
Solution A
Solution B
Solution 2
ORA-00905 missing keyword
Best Answer
Answers
Case Statement Error — ORA-00905: missing keyword
Answers

ORA-00905: missing keyword

When this ORA-00905 error occurs

Root Cause

Solution 1

Problem

Solution A

Solution B

Solution 2

Источник

ORA-00905 missing keyword

Working in R12 OPM Version 12.1.3

This below sql statement is giving an error ora-00905 missing keyword

Please i need to help

select * from ar_customers ac

—where ac.customer_number in(1427)

where (case when :p_type = ‘Y’ then ac.customer_number in (select lookup_code from fnd_lookup_values_vl where lookup_type like ‘SCSA_AR_EXCLUDE_CUSTOMER_LIST’)

when :p_type = ‘N’ then ac.customer_number not in (select lookup_code from fnd_lookup_values_vl where lookup_type like ‘SCSA_AR_EXCLUDE_CUSTOMER_LIST’)

Best Answer

Whenever you have a question, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all the tables involved, and the exact results you want from that data, so that the people who want to help you can re-create the problem and test their ideas.

Explain, using specific examples, how you get those results from that data.

See the forum FAQ:

«Missing keyword» can occur with all kinds of syntax errors, not just missing keywords.

In this case, the error is having a condition, rather than an expression, after THEN in a CASE expression. Review how CASE expressions work: THEN and ELSE must be followed by a single value in some SQL data type; for example,

is a value (apparently with the NUMBER datatype), but

is not, since there is no BOOLEAN data type in SQL.

Perhaps you’re trying to so something like this:

In this particular example, it might be more efficient to have three separate queries, and run one of them, depending on the value of p_type, or maybe even use dynamic SQL.

Answers

Explain, using specific examples, how you get those results from that data.

See the forum FAQ:

«Missing keyword» can occur with all kinds of syntax errors, not just missing keywords.

is a value (apparently with the NUMBER datatype), but

is not, since there is no BOOLEAN data type in SQL.

Perhaps you’re trying to so something like this:

In this particular example, it might be more efficient to have three separate queries, and run one of them, depending on the value of p_type, or maybe even use dynamic SQL.

Источник

Case Statement Error — ORA-00905: missing keyword

I have the below SQL

I get the below error

ORA-00905: missing keyword

What I am trying to acheive is when parameter status = FULL then bring all records with product_no is empty,

status = OPEN then bring all records with product_category is empty

status = CLOSED then bring all records with product_no and product_category is empty.

How can I do this with case statement?

Answers

You’re mixing up two ways of doing case. You either need:

You also don’t need the brackets around the two case statements.

may be something like … (not tested)

edit: seems like the description and the code you posted are not in sync (IS NULL or IS NOT NULL part). adjust it as needed.

select*fromproducts
whereprod_name=’CASKET’
and(CASE
WHEN:status=’FULL’THENproduct_noisnotnull
WHEN:status=’OPEN’THENproduct_catISNOTNULL
WHEN:status=’CLOSED’THENproduct_noANDproduct_catISNULL
ELSENULL
END)

Oracle SQL only implements case expressions. Case statements do exist, in PL/SQL; but they are not used to write conditions anyway, they are procedural statements. What you might want (and is somewhat in the spirit of your attempt) would perhaps best be called case condition — unfortunately, they don’t exist in Oracle SQL.

OK, enough with that. How can you write your query then?

Answer: You don’t need «case» anything, you just need to write your condition using standard (middle school) Boolean operators: AND, OR, NOT, and parentheses to control what goes with what.

Something like this:

In the plain-English description you say «bring all records with product_no is empty» but in your attempted code you wrote THEN product_no IS NOT NULL. Do you see an obvious contradiction here? Which do you actually need — is null, or is not null?
Sometimes even people who work with computers are confused between «and» and «or». You say when status = ‘CLOSED’ you want records where product_no and product_cat is null. That, is, BOTH of them must be null. If a record has «empty» product_cat, but the product_no is not «empty», then that record will NOT be selected. I point this out so that you can decide for yourself that this is indeed what you need.
A bit of terminology: «empty» may be used with regard to tables (they have no rows). If an attribute does not have a value, that’s always called «null» — never «empty». And tables in a relational database have «rows», not «records». Best to learn the proper terminology as soon as possible.
And finally, the compound condition in the WHERE clause could be written with parentheses, but they aren’t really needed — because AND has higher precedence than OR. Compare: 3 * 5 + 2 * 9 can also be written as (3 * 5) + (2 * 9), but by the time we are in fifth grade we don’t use those parentheses anymore. We would only use them if we needed them: namely, if we needed to add before we multiply; 3 * (5 + 2 * 9) does need parentheses. The condition in the WHERE clause above is of the simplest kind, similar to 3 * 5 + 2 * 9; it doesn’t need any parentheses.

EDIT: Actually you do need parentheses; I missed that you had an additional condition in your WHERE clause. So:

Источник