oracle tutorial webinars

ORA-00905

Error ORA-00905 is likely one you will see rather frequently while coding within Oracle. Fortunately, it is much more straightforward and easier to resolve than some of the other ORA errors you will run into while working in Oracle.

In Oracle, there are certain words that have special meaning within the program: reserved words and keywords. (Namespaces are also another type of word that holds special meaning in Oracle but which is unrelated to the discussion of ORA-00905.) Reserved words are words that cannot be redefined and therefore, can never be used to define database objects such as columns or tables. These words are predefined by Oracle and will always hold their respective meanings as long as they are used.

Keywords are words that also have special meaning to Oracle but those that are not reserved words and therefore, can be redefined. However, some keywords may later become reserved keywords and therefore, should be used with caution when executed as variable or function names. Here you will find a list of keywords.

The Problem

Error ORA-00905 is seen when a required keyword is missing. The error message will read:

ORA-00905: missing keyword

As the message suggests, your code is missing a keyword where there should be one in order for the query to run successfully.

The Solution

According to the Oracle documentation, the action for this error is to “correct the syntax.” Resolving ORA-00905 involves figuring out what keyword is missing and where to insert the keyword. Take the following example:

SELECT *

INTO department_Backup

FROM department

In this example, the user is selecting all variables within the “department” list into the backup list. However, running this query will throw an ORA-00905 error. It is missing the keyword INSERT.

INSERT INTO department_Backup

(SELECT * FROM department)

Here is another PL/SQLexample in which the user is attempting to create a new table and copy the inventory data into this new table:

SELECT *

INTO inventory_may2009

FROM inventory;

This query will not work because the keyword SELECT * INTO is not proper PL/SQL syntax. The proper way to write this query and get rid of the error message is to write it as the following:

INSERT INTO inventory_may2009

SELECT *

FROM inventory;

Looking Forward

The best way to avoid seeing ORA-00905 in the future is to be aware of Oracle keywords as well as correct Oracle syntax. You should be familiar with common keywords and reserved words and refer back to official Oracle documentation for reference. If you cannot resolve ORA-00905, consider contacting your system database administrator for help. Otherwise, it may be best to contact an Oracle consultant who can resolve the error as well as meet other requirements you may have. Remember to double check the professional’s credentials, experience and certification levels to ensure your Oracle needs are met.