Create table with id as identity
create table colors (id number(4) GENERATED ALWAYS as IDENTITY(START with 1 INCREMENT by 1), name varchar2(30));
create table colors2 (id number(4) GENERATED by default on null as IDENTITY, name varchar2(30));
Old fashion
To create sequence
CREATE SEQUENCE my_seq;
to get the next value
SELECT my_seq.NEXTVAL FROM DUAL;
to use in a trigger for your table demo
CREATE OR REPLACE TRIGGER demo_increment
BEFORE INSERT ON demo
FOR EACH ROW
BEGIN
SELECT my_seq.NEXTVAL
INTO :new.id
FROM dual;
END;
To alter table table_name modify
ALTER TABLE agap.colors MODIFY id
GENERATED ALWAYS AS IDENTITY (START WITH LIMIT VALUE);
/
note : it's not support 12c r1
ERROR at line 1:
ORA-30673: column to be modified is not an identity column
