How to Resolve ORA-00905: missing keyword

ORA-00905

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.