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

Question

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

Answer 1

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.