Missing keyword oracle ошибка

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.

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:

1. The EXPLAIN PLAN command shows a SQL statement’s execution plan without actually executing the statement.
2. 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.

Any expected keyword missing from statements may result in ORA-00905. This is only a case when we tried to add a new data file to a tablespace, but it failed with ORA-00905.

Case 1: Add a File to 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

Case 2: Grant Privilege to User

SQL> grant select any table hr;
grant select any table hr
                       *
ERROR at line 1:
ORA-00905: missing keyword

ORA-00905 means that an expected keyword is missing from the statement at the specific position of statement, usually, it’s a syntax error.

Solutions

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.

In case 1, it turns out that 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;

In case 2, we missed TO keyword.

SQL> grant select any table to hr;

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 have to worry about the error ORA-00905, it always points out the position where keyword is missing. Another similar error ORA-02142 might also be thrown in ALTER TABLESPACE ADD DATAFILE statements.