0

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.

Petukhou Mikita
  • 49
  • 6

1 Answers1

2

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.

Your 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.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • ooh.. Sure! It make’s sense. As usually, what a silly question it turns to be afterwards. Yeap, I have simplified it for the question. Thank you! – Petukhou Mikita May 18 '21 at 15:05