Creating a sequence on an existing table - ORA-00940: invalid ALTER command

Question

I created a sequence and I want a table to make use of it. The creation of the sequence works fine. However, I when I try to alter the table in order to make use of the sequence, I get this error (in personInformationSequenceAlterTest):

ORA-00940: invalid ALTER command

Please note I need to use Java (Eclipse IDE).

    String personInformationSequenceTest = 
              "CREATE SEQUENCE seq_person "
            + "start with 1 "
            + "increment by 1 "
            + "NOCACHE "
            + "NOCYCLE ";

    String personInformationSequenceAlterTest =
              "alter table personInformationTest "
            + "alter column personId " 
            + "set default nextval('seq_person')";

    String personInformationSequenceOwnedTest = 
            "alter sequence seq_person owned by personInformationTest.personId";

Answer 1

Your alter statement has syntax problem.

Try this (assuming datatype is int for that column. Change accordingly):

alter table personInformationTest modify (personId int default seq_person.nextval);

This will only work in Oracle 12c and up.

For 11g or lower, you can use triggers. If you don't want to use triggers, you can explicitly use seq_person.nextval in your inserts.

insert into personInformationTest (personId, . . .)
values (seq_person.nextval, . . .)