CODEWITHSUNDEEP
ora-01735
Arash moradabadi
Arash moradabadi

Reputation: 365

ORA-01735 during running ALTER TABLE inside the body of a loop using Oracle EXECUTE IMMEDIATE

I am trying to use an ALTER TABLE statement inside the body of a cursor by using EXECUTE IMMEDIATE <strAlterTableStatement>; but i get ORA-01735: invalid ALTER TABLE option error. The statement is OK if i run it as a PLSQL statement but as a string passed to EXECUTE IMMEDIATE, raises the error.

Upvotes: -1

Views: 37

Answers (1)

Arash moradabadi
Arash moradabadi

Reputation: 365

The problem was a semi-colon at the end of the statement string. Passing a statement to the EXECUTE IMMEDIATE must not include the semi-colon at the end of the command, otherwise Oracle raises the above error.

Running the below code would result to ora-01735

Begin
     EXECUTE IMMEDIATE 'ALTER TABLE table1 DROP CONSTRAINT constraint1;';
end;

but running it without the semi-colon at the end of the statement string as below will work fine

Begin
     EXECUTE IMMEDIATE 'ALTER TABLE table1 DROP CONSTRAINT constraint1';
end;

Upvotes: 0

Related Questions