How do you create an auto increment in Oracle 11g?
You have to modify the sequence as follows :
CREATE SEQUENCE sequence_staffMINVALUE 1000000START WITH 1000000INCREMENT BY 1 NOCACHE NOCYCLE;
Also, you have to insert the new staff_id column using sequence_staff.nextval always. See how it works hitting
select sequence_staff.nextval from dual; --repeated times.
Read more about sequences here https://docs.oracle.com/cd/B28359_01/server.111/b28310/views002.htm
EDIT :
Yes, it is possible. Create sequence the way you were creating and :
select to_char(sequence_staff.nextval,'FM0000000') from dual;
EDIT 2 :
This link deserves the credit. http://stackoverflow.com/questions/14561210/creating-a-sequence-for-a-varchar2-field-in-oracle
EDIT 3 : If you really want the results your way in Oracle Database you have to :
1. alter table staff modify staff_id varchar(20);2. CREATE SEQUENCE sequence_staffMINVALUE 1START WITH 1INCREMENT BY 1 NOCACHE NOCYCLE;3. insert into staff(Staff_id, surname,firstnames, phone,address) values(to_char(sequence_staff.nextval,'FM0000000'),'Wayne','Bruce','0000','Gotham');
CREATE TABLE staff ( id NUMBER(7) NOT NULL, surname VARCHAR2(50) NOT NULL, firstnames VARCHAR2(50) NOT NULL, phone VARCHAR2(30) NOT NULL, address VARCHAR2(150) NOT NULL );ALTER TABLE Staff ADD ( CONSTRAINT staff_pk PRIMARY KEY (ID)); CREATE SEQUENCE staff_seq START WITH 1;
Trigger definition:
CREATE OR REPLACE TRIGGER staff_bir BEFORE INSERT ON Staff FOR EACH ROW BEGIN SELECT staff_seq.NEXTVAL INTO :new.id FROM dual; END;