autonumber in oracle 9i lite

chloh
chloh used Ask the Experts™
on
does anyone knows how to do an autonumber in oracle 9i lite? this autonumber will always be incremented by one. i tried to use sequence and etc but it seemed doesn't work at all.

does oracle 9i lite supports autonumber? or do i need to install oracle enterprise?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
You can do by sequence, then make procedure to inserting new row or any PL/SQL command like this.

select sequence_name.nextval into :iNext from dual;
insert into table_name(....) values(iNext, ...);
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
to explain a bit further:
most databases provide a datatype autonumber or identity, which supplies automatically the new values.
Oracle worked out a different approach: the sequence objects. you can create such a sequence, which has NO reference to a table, and it's up to the developer to use this sequence to fill the "autonumber" column.

Credits are for dikau that posted the code how to implement things.

CHeers

Commented:
In addition to creating a sequence, you may also want to create a trigger in order to implement your autonumber feature.  This way, you may insert data into your table without specifying the column containing your autonumber.

To do this, you need to do something like the following:

First create the sequence:
CREATE SEQUENCE SEQ_AUTONUMBER MINVALUE 1

Then create the trigger:
CREATE OR REPLACE TRIGGER TR_SEQ_AUTONUMBER BEFORE INSERT ON TableName FOR EACH ROW
BEGIN SELECT SEQ_AUTONUMBER.nextval INTO :new.ColumnName FROM dual; END;

This is much closer to being an "autonumber" much like Access or SQL Server provides.

Author

Commented:
thanks for all your responses. i think update you people later.

Commented:
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
Accept dikau's comment as answer
Please leave any comments here within the next seven days.
 
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
 
jpkemp
EE Cleanup Volunteer

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial