CODEWITHSUNDEEP
sqloracle-databaseplsql
carlplusplus
carlplusplus

Reputation: 77

Altering column to add sequence error in Toad

CREATE TABLE C_ORDER  
( 
    OrderNumber     NUMBER(5), 
    OrderDate       DATE DEFAULT (sysdate), 
    CustomerName    VARCHAR2(100), 
    CONSTRAINT c_order_pk PRIMARY KEY (OrderNumber),
    CONSTRAINT customername_uq UNIQUE(CustomerName)
);

CREATE SEQUENCE ordernumber_seq 
 START WITH     1 
 INCREMENT BY   1 
 NOCACHE 
 NOCYCLE;

ALTER TABLE C_ORDER 
    MODIFY OrderNumber NUMBER(5) DEFAULT ordernumber_seq.NEXTVAL;

The code was working in livesql but when I try to execute it in Toad this error shows:

ALTER TABLE C_ORDER MODIFY OrderNumber NUMBER(5) DEFAULT ordernumber_seq.NEXTVAL Error at line 1 ORA-00984: column not allowed here

Upvotes: 1

Views: 1427

Answers (1)

krokodilko
krokodilko

Reputation: 36107

This works in Oracle 12c, but in 11.2g is not allowed yet,
please refer to the documentation of ALTER TABLE statement for version 11.2g:

DEFAULT

Use the DEFAULT clause to specify a default for a new column or a new default for an existing column. Oracle Database assigns this value to the column if a subsequent INSERT statement omits a value for the column. If you are adding a new column to the table and specify the default value, then the database inserts the default column value into all rows of the table.

The data type of the default value must match the data type specified for the column. The column must also be large enough to hold the default value.

Restrictions on Default Column Values
Default column values are subject to the following restrictions:

A DEFAULT expression cannot contain references to other columns, the pseudocolumns CURRVAL, NEXTVAL, LEVEL, and ROWNUM, or date constants that are not fully specified.

The expression can be of any form except a scalar subquery expression.

You need BEFORE INSERT trigger:

CREATE OR REPLACE TRIGGER some_name
BEFORE INSERT ON C_ORDER
FOR EACH ROW 
BEGIN
  :NEW.OrderNumber := ordernumber_seq.NEXTVAL;
END;
/

Upvotes: 1

Related Questions