invalid ALTER TABLE option when execute immediate statement

Question

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit

Production PL/SQL Release 12.1.0.2.0 - Production

"CORE 12.1.0.2.0 Production" TNS for 64-bit Windows: Version

12.1.0.2.0 - Production NLSRTL Version 12.1.0.2.0 - Production

This code working well

ALTER TABLE ACTIONLOG MODIFY (ID GENERATED ALWAYS AS IDENTITY START WITH 8);

but when I used with execute immediate giving error

declare 
mvalue INTEGER; 
exp varchar(1000);
begin 
select max(coalesce(ID,0))+1 into mvalue from ACTIONLOG; 
exp := 'ALTER TABLE ACTIONLOG MODIFY (ID GENERATED ALWAYS AS IDENTITY START WITH '|| mvalue ||');';
dbms_output.Put_line('Max Value: '|| mvalue);
DBMS_OUTPUT.PUT_LINE('Expression: ' || exp);
execute immediate exp; 
end;
/

DBMS Output

Max Value: 8

Expression: ALTER TABLE ACTIONLOG MODIFY (ID GENERATED ALWAYS AS IDENTITY START WITH 8);

Error

Error starting at line : 1 in command - declare mvalue INTEGER; exp

varchar(1000); begin select max(coalesce(ID,0))+1 into mvalue from

ACTIONLOG; exp := 'ALTER TABLE ACTIONLOG MODIFY (ID GENERATED ALWAYS

AS IDENTITY START WITH '|| mvalue ||');'; dbms_output.Put_line('Max

Value: '|| mvalue); DBMS_OUTPUT.PUT_LINE('Expression: ' || exp);

execute immediate exp; end; Error report - ORA-01735: invalid ALTER TABLE

option ORA-06512: at line 9

00000 - "invalid ALTER TABLE option"

*Cause:

*Action:

Edit: The problem was ";"

So The answer is :

exp := 'ALTER TABLE ACTIONLOG MODIFY (ID GENERATED ALWAYS AS IDENTITY START WITH '|| mvalue ||');';

instead of

 exp := 'ALTER TABLE ACTIONLOG MODIFY (ID GENERATED ALWAYS AS IDENTITY START WITH '|| mvalue ||')';

Unknown Unknown · Accepted Answer · 2015-12-24T10:10:27

Try this

declare 
mvalue INTEGER; 
exp varchar(1000);
begin 
select max(coalesce(ID,0))+1 into mvalue from ACTIONLOG; 
--exp := 'ALTER TABLE ACTIONLOG MODIFY (ID GENERATED ALWAYS AS IDENTITY START WITH '|| mvalue ||');';
dbms_output.Put_line('Max Value: '|| mvalue);
--DBMS_OUTPUT.PUT_LINE('Expression: ' || exp);
execute immediate 'ALTER TABLE ACTIONLOG MODIFY (ID GENERATED ALWAYS AS IDENTITY START WITH '|| mvalue ||')'; 
end;
/

invalid ALTER TABLE option when execute immediate statement

1 Answers