I am trying to solve a task with a dynamic SQL, but facing an issue ora-00900 invalid sql statement.
execute immediate 'alter table my_table set interval (NUMTOYMINTERVAL(1, ''MONTH''))';
However, it works in the anonymous block treating the statement to be executed as a string.
DECLARE str VARCHAR2 (250) := 'alter table my_table set interval (NUMTOYMINTERVAL(1, ''MONTH''))'; BEGIN execute immediate str; END;
So where is the issue in the first case? It looks like with escape quotes, but can’t catch this.
As the error says,
execute immediate is not a SQL statement. There is an
execute command in some clients – SQL*Plus, SQL Developer, SQLcl and possibly others – which is a shorthand wrapper around an anonymous block; but from the error you don’t seem to be using one of those. If you were you’d get ORA-06550 and PLS-0010: Encounter edthe symbol “alter table…” when expecting…
execute immediate is a PL/SQL statement. So it is only valid within a PL/SQL block.
It has no meaning in plain SQL; the only
execute in the SQL reference is referring to directory object privileges.
alter table doesn’t make much sense anyway, not sure if that’s supposed to be an
update, but if so it isn’t say what to set to that interval value. It isn’t obvious why you need it to be dynamic either. Possibly that reason and the actual statement have been lost in simplifying for posting your question.