Oracle data type (Autonumber)?

[Lonewolf]

I have only recently started using oracle and i have to admit it frightens me with its huge scale

Whatever happens i am starting to get the hang of it but one thing still eludes me. Autonumber
Is there a data type that i can set in an Oracle database that acts the same way as Autonumber does in Access 2000. If not is there a way i can work round this to give the same results?

Thanks for any help

[AlanP]

To do the equivalent of autonumber you have to use sequences in oracle. The sequence will provide you with a guaranteed unique number which you can then use in your insert/update statement. You can also interrogate the sequence to get the last value you used which is useful (NOTE you cant see the last value used if you havent already used the sequence).

See 'Create sequence' in the SQL Manual

Use select <sequence name>.nextval to get the next value.

Use insert into table (...) values (<sequence name>.nextval,....)

Use select <sequence name>.currval from dual to get the last value selected by your session.

NOTE you may get gaps in the sequence due to caching and transactions which rollback.

Alan

[Lonewolf]

Ill have a look at that.
Thanks

[vagreaty]

Hi,

For the above requirement the simple solution is create a sequence and trigger on the particular table. Trigger will avoid you from writing sequence.nextval in all insert statement and the developer dont have to remember the sequence name.

Thanks

Antony Gubert.