What to do if ora 00922 missing or invalid option?
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?
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.