What to do if ora 00922 missing or invalid option?

1.8K    Asked by Aashishchaursiya in SQL Server , Asked on Mar 15, 2023

I have the following segment of oracle pl/sql block


DECLARE

    rec all_tab_columns%ROWTYPE;
    v_tableName VARCHAR2(100);
    v_columnName VARCHAR2(100);
    v_schemaName VARCHAR2(100);

BEGIN

    v_schemaName := 'TESTNEW';
    EXECUTE IMMEDIATE 'create table '||v_schemaName||'.table_column_mapping_8(table_name varchar2(100), column_name varchar2(100));';
    for rec in (
        SELECT table_name, column_name FROM all_tab_columns WHERE identity_column = 'YES' AND OWNER = v_schemaName
    ) LOOP
         v_tableName := rec.table_name;
         v_columnName := rec.column_name;
        EXECUTE IMMEDIATE 'insert into ' ||v_schemaName||'.table_column_mapping_8 values('''||v_tableName||''', '''|| v_columnName ||''');';
      END LOOP;
END;
As I try running this, I get the following error:
Error report - ORA-00922: missing or invalid option ORA-06512: at line 8 00922. 00000 - "missing or invalid option" *Cause:
*Action:

Looks like something is wrong at the execute immediate statement

Tried to google it and found this: http://www.dba-oracle.com/t_ora_00922_missing_or_invalid_option.htm

Couldn't find the real cause.

Can someone help here?

Answered by David Edmunds

If ora 00922 missing or invalid option- Remove the semicolon at the end of the execute immediate DDL string. Oracle SQL doesn't have statement terminators - semicolons are only used by client tools and the PL/SQL language, and your DDL string is not PL/SQL.

This fails due to the unrecognised semicolon character:

begin
    execute immediate 'create table demo (id int);';
end;
This works:
begin
    execute immediate 'create table demo (id int)';
end;

You will need to do the same for the dynamic insert later in the block.


Your Answer

Answers (2)

The ORA-00922: missing or invalid option error in Oracle happens when there’s an issue with the syntax of your SQL statement. It can be frustrating, but here’s how you can troubleshoot and fix it:


1. Understand Why This Error Occurs

This error typically happens due to:

  • A missing keyword or clause in the SQL statement.
  • Using an option that doesn’t exist in the current Oracle version.
  • Incorrect placement of clauses in CREATE, ALTER, or GRANT statements.

2. Common Causes and Fixes

✅ Incorrect CREATE TABLE Syntax

If you are creating a table, make sure you’re not adding an extra comma or missing a data type:

CREATE TABLE employees (
    id NUMBER PRIMARY KEY, -- ✅ Correct
    name VARCHAR2(50) NOT NULL -- ❌ Missing comma here causes an error
);

✅ Invalid Options in ALTER Statements

Sometimes, people use MySQL or SQL Server syntax by mistake:

  ALTER TABLE employees AUTO_INCREMENT = 100; -- ❌ Wrong in Oracle

Fix: Oracle does not support AUTO_INCREMENT; use SEQUENCE instead.

✅ GRANT Command Issues

If granting privileges, avoid invalid options:

  GRANT CONNECT TO user_name WITH ADMIN;  -- ❌ Incorrect

Fix: WITH ADMIN is not valid here. Use:

  GRANT CONNECT TO user_name;

✅ Check for Reserved Words

If you named a table or column using a reserved keyword, put it in double quotes:

  CREATE TABLE "USER" (id NUMBER); -- ✅ Fix

3. Final Steps to Fix the Error

  • Double-check your SQL statement for missing commas, extra spaces, or incorrect keywords.
  • Refer to Oracle Documentation for correct syntax based on your version.
  • Run the query in SQL Developer to get detailed error hints.

By carefully reviewing your SQL syntax and following these steps, you can quickly resolve the ORA-00922 error. Let me know if you need more help!

1 Week

The ORA-00922: missing or invalid option error in Oracle SQL typically indicates a syntax error in your SQL statement, usually due to a missing or incorrect keyword, or an extra comma. Here are some common causes and solutions:

Incorrect CREATE TABLE Syntax:

Ensure all column definitions are correct and separated by commas.

Check for misplaced or missing commas.

  CREATE TABLE employees (    employee_id NUMBER(10),    first_name VARCHAR2(50),    last_name VARCHAR2(50),    hire_date DATE -- Ensure no comma after the last column);

Invalid Options in ALTER TABLE Statements:

Verify the options used in ALTER TABLE statements are correct.

  ALTER TABLE employeesADD (    email VARCHAR2(100) -- Ensure correct syntax for adding columns);Invalid Options in CREATE INDEX Statements:

Ensure correct syntax when creating indexes.

  yee_nameON employees (last_name); -- Ensure the column names are correct

Check for Unsupported SQL Keywords:

Ensure that you are not using any unsupported or misspelled SQL keywords.

  CREATE TABLE employees (    employee_id NUMBER(10) PRIMARY KEY, -- Ensure PRIMARY KEY is valid here    first_name VARCHAR2(50),    last_name VARCHAR2(50));

Extraneous Commas:

Remove any extraneous commas, particularly at the end of a list.

  INSERT INTO employees (employee_id, first_name, last_name)VALUES (1, 'John', 'Doe'); -- Ensure correct comma usageChecking Permissions and Options:

Ensure you have the necessary permissions to execute the statement.

Verify that all options used in your SQL statement are valid for your version of Oracle.

Example Scenarios and Solutions

Scenario 1: Creating a Table with Syntax Error

  CREATE TABLE employees (    employee_id NUMBER(10)    first_name VARCHAR2(50), -- Missing comma after employee_id    last_name VARCHAR2(50));

Solution:

  CREATE TABLE employees (    employee_id NUMBER(10),    first_name VARCHAR2(50),    last_name VARCHAR2(50));

Scenario 2: Altering a Table with Invalid Syntax

ALTER TABLE employees

ADD first_name VARCHAR2(50) last_name VARCHAR2(50); -- Missing comma between columns

Solution:

  ALTER TABLE employeesADD (first_name VARCHAR2(50), last_name VARCHAR2(50));Scenario 3: Creating an Index with Invalid OptionsqlCopy codeCREATE INDEX idx_employee_name

ON employees last_name; -- Missing parentheses around column name

Solution:

  CREATE INDEX idx_employee_nameON employees (last_name);

By carefully reviewing your SQL syntax and ensuring it adheres to Oracle's SQL standards, you can avoid the ORA-00922: missing or invalid option error.








6 Months

Interviews

Parent Categories