CODEWITHSUNDEEP
javasqloracle-databasealter
Viteazul
Viteazul

Reputation: 181

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

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";

Upvotes: 1

Views: 3862

Answers (2)

Gurwinder Singh
Gurwinder Singh

Reputation: 39497

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, . . .)

Upvotes: 2

Krishna Chaitanya Cattamanchi
Krishna Chaitanya Cattamanchi

Reputation: 1

Check by Changing

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

to

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

In Oracle and MySql we use "Modify" for altering an existing column . In SQL Server / MS Access , "Alter" is used .

Upvotes: 0

Related Questions