Oracle pl/sql block throwing ORA-00922: missing or invalid option

dynamic-sqlerrorsoracleplsqlquery

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 statment

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?

Thanks in advance

Best Answer

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.

Related Question