Auto Increment Possible?

[bunnyweb]

Hello. Our application currently works with SQL Server, but we are attempting to move it over to Oracle.

In SQL Server we can use the "Identity" feature to auto increment an ID field by 1 each time a record is added.

Is the same feature available in Oracle? I can't figure out how to do it.

Any help would be greatly appreciated! Thanks!

 

[lewisp]

Use a sequence. eg:

CREATE SEQUENCE your_sequence;

SELECT  your_sequence.NEXTVAL
FROM sys.dual;

 

[DugsDMan]

Yup, a Sequence in Oracle will fulfill your SQL Server Identity need. Here's some more info on it:

CREATE SEQUENCE YourNumberSequence
INCREMENT BY 1
START WITH 1
MAXVALUE 99999999 (or NOMAXVALUE)
MINVALUE 1 (or NOMINVALUE)
CYCLE (or NOCYCLE - the default)
CACHE n (or NOCACHE)
ORDER (or NOORDER)

The default INCREMENT BY is 1. Specifying a positive number increment will generate ascending sequence numbers with an interval equal to the value you select. A negative number will generate descending sequence numbers.

START WITH is the starting number for the sequence. The default for START WITH is MAXVALUE for a descending sequence and MINVALUE for an ascending sequence. START WITH overrides the default value.

MINVALUE is the lowest sequence number generated. MIN VALUE and START WITH default to 1 for ascending sequences.

MAXVALUE is the largest sequence number generated. For descending sequences, it defaults to -1.
[ul]
[li]To allow unlimited sequence number generation only use MINVALUE for ascending and MAXVALUE for descending sequences.[/li]
[li]Specifying a limit with MINVALUE or MAXVALUE will force an error when an attempt is made to generate a number exceeding the limit and when you have specified NOCYCLE.[/li]
[/ul]
CYCLE causes automatic cycling to start the sequence over once the MAXVALUE for an ascending sequence or MINVALUE for a descending sequence is reached. The default MAXVALUE is 10e27 - 1 (a very large number).

CACHE is an option to cache the specified number of sequence values into buffers in the SGA.
[ul]
[li]This speeds access, but loses the cached numbers if the database is shut down.[/li]
[li]The default value for cached numbers is 20 if you do not specify NOCACHE.[/li]
[/ul]
ORDER forces sequence numbers to be output in order, and is usually used where the sequence number is used for time stamping.

Altering Sequences: Use the ALTER SEQUENCE command with a syntax like that shown for the CREATE SEQUENCE command.

Most parameters may be altered, but only future sequence numbers are affected.

You cannot alter the START WITH clause without dropping and recreating the sequence.

Dropping Sequences: Use the DROP SEQUENCE sequence name command.

If you have created a trigger or procedure that references the sequence, the trigger/procedure will fail if the sequence is dropped.

Using Sequences: In order to use a sequence, you must first generate the sequence number by using the NEXTVAL option. Here is an Example using NEXTVAL.
[tt]
INSERT INTO YourTable
( RowID, OrderDate, SalesPerson )
VALUES ( YourNumberSequence.NEXTVAL, SYSDATE, 'DugsDMan' );
[/tt]
The CURRVAL option will return the current sequence number, but will not execute unless the sequence has been called at least one time using the NEXTVAL option.

CURRVAL is used instead of NEXTVAL to use the same sequence number more than once, for example, when you are inserting rows into a related table where referential integrity must be enforced.

If you use NEXTVAL and CURRVAL in the same SQL statement, both of these values will be the value retrieved by NEXTVAL.

You cannot use NEXTVAL or CURRVAL in subqueries as columns in a select clause where you use DISTINCT, UNION, INTERSECT, or MINUS or in ORDER BY, GROUP BY, or HAVING clauses.

 

[bunnyweb]

Amazing info! It worked perfectly. Thank you so much!

There is only one thing that's not clear, which is

SELECT your_sequence.NEXTVAL
FROM sys.dual;

What exactly is "sys.dual"??

 

[DugsDMan]

Sys.dual is a single row dummy table. It has one column Dummy VarChar2(1) with a value of 'X'.

It's often used for testing items (such as a sequence ), pulling the date (select SYSDATE from Dual), etc.

 

[sem]

DUAL is pseudo-table containing 1 field and 1 row to provide pure SQL access to some additional Oracle features. E.g. to get result from some function you may select function(parameres) from DUAL

Regards, Dima

http://seminihin.narod.ru

 

[lewisp]

Of course, you do not need to use sys.dual. For example, when creating new data you can use this syntax:

INSERT INTO your_table (
  id1,
  var1,
  var2 )
VALUES (
  your_sequence.NEXTVAL,
  'Value1',
  'Value2');