oracle tutorial webinars

ORA-00922

ORA-00922: missing or invalid option is one of many Oracle errors, which does not point to a concrete and easily identifiable problem. The key to resolving this issue is to find the specific location of the error (which can be tedious) and correct its syntax. A small error such as a misplaced comma or missing quotation mark can throw this error. To make things easier, use a good integrated development environment software application to help identify syntactical errors that are hard to catch on your own.

The Problem

ORA-00922: missing or invalid option occurs if an invalid option is specified while defining a storage clause or column. Oracle provides the following information about this error:

Cause: The valid option in specifying a column is NOT NULL to specify that the column cannot contain any NULL values. Only constraints may follow the datatype. Specifying a maximum length on a DATE or LONG datatype also causes this error.

The Solution

Resolving ORA-00922: missing or invalid option can be tricky as the error does not appear due to one, universal cause. Oracle simply states that the syntax must be corrected. The user must remove the erroneous option or length specification from the column or storage specification.

In the following example, a table named employee is created:

CREATE table employee
(employee_id(5) NOT NULL, employee_name varchar2(30) employment_length varchar2 (40))

While this code seems correct at first glance, there is a missing comma after “employee_name varchar2(30),” throwing off the entire code and warranting ORA-00922 error message. The corrected code should read:

CREATE table employee
(employee_id(5) NOT NULL, employee_name varchar2(30), employment_length varchar2 (40))

ORA-00922: missing or invalid option is commonly seen during the installation process, specifically when creating new passwords. It installer may state that the user cannot be created along with the message: “missing or invalid option.” In such cases, the error is likely occurring because you are attempting to create a password that is complex. Changing the password to a less complex, weaker password will allow you to resolve the error and complete the installation successfully. Note that you can change the password to a stronger one after the installation is complete.

Moving Forward

If you continue to face problems with ORA-00920: missing or invalid option, you may consider contacting your database administrator for assistance if you yourself are not the DBA. Another option may be to contact an Oracle professional to resolve the issue. Remember to always check the consultant’s credentials and certification to ensure your Oracle needs are properly met.