What to do if ora 00922 missing or invalid option?

1.1K 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

Answer (1)

Ranjana

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.

2 Months