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.
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.