Sequence/autonumber 
Author Message
 Sequence/autonumber

How do I accomplish what in other database-engines are called
autonumber? Like in Access for example. That is unique
generation of indexes/record-keys. Is there a datatype that
manages this automatically?

/Kalle W

-----------------------------------------------------------

Got questions?  Get answers over the phone at Keen.com.
Up to 100 minutes free!
http://www.***.com/



Wed, 18 Jun 1902 08:00:00 GMT
 Sequence/autonumber

hi,

one way is to use a trigger.

SQLWKS> create table temp
     2>       (data varchar2(10),
     3>        autonumber number)
     4> /
Statement processed.
SQLWKS> create sequence tmp
     2>  increment by 1
     3>  start with 1
     4>  maxvalue 9999999999
     5>  cycle
     6>  nocache
     7>  order
     8> /
Statement processed.
SQLWKS> create or replace trigger test_trigger
     2>   before insert on temp
     3>   for each row
     4> begin
     5>   select tmp.nextval into :new.autonumber from dual;
     6> end;
     7> /
Statement processed.
SQLWKS> begin
     2> for i in 1..5
     3> loop
     4>   insert into temp(data) values('test');
     5> end loop;
     6> commit;
     7> end;
     8> /
Statement processed.
SQLWKS> select * from temp
     2> /
DATA       AUTONUMBER
---------- ----------
test                1
test                2
test                3
test                4
test                5
5 rows selected.

code to test:
create table temp
      (data varchar2(10),
       autonumber number)
/
create sequence tmp
 increment by 1
 start with 1
 maxvalue 9999999999
 cycle
 nocache
 order
/
create or replace trigger test_trigger
  before insert on temp
  for each row
begin
  select tmp.nextval into :new.autonumber from dual;
end;
/
begin
for i in 1..5
loop
  insert into temp(data) values('test');
end loop;
commit;
end;
/
select * from temp
/

Hakan

Quote:

> How do I accomplish what in other database-engines are called
> autonumber? Like in Access for example. That is unique
> generation of indexes/record-keys. Is there a datatype that
> manages this automatically?

> /Kalle W

> -----------------------------------------------------------

> Got questions?  Get answers over the phone at Keen.com.
> Up to 100 minutes free!
> http://www.keen.com



Wed, 18 Jun 1902 08:00:00 GMT
 Sequence/autonumber

By a strange coincidence, I have just
started a FAQ section on my website.

Starting to list the questions I will be
answering, but the one answer I have
included is to the question you have
just asked

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Quote:

>How do I accomplish what in other database-engines are called
>autonumber? Like in Access for example. That is unique
>generation of indexes/record-keys. Is there a datatype that
>manages this automatically?

>/Kalle W

>-----------------------------------------------------------

>Got questions?  Get answers over the phone at Keen.com.
>Up to 100 minutes free!
>http://www.keen.com



Wed, 18 Jun 1902 08:00:00 GMT
 Sequence/autonumber


Quote:

> How do I accomplish what in other database-engines are called
> autonumber? Like in Access for example. That is unique
> generation of indexes/record-keys. Is there a datatype that
> manages this automatically?

> /Kalle W

> -----------------------------------------------------------

> Got questions?  Get answers over the phone at Keen.com.
> Up to 100 minutes free!
> http://www.keen.com

No, you'll need to use a sequence and either use
insert into table foo
values (fooseq.nextval...)
or create a before insert for each row trigger on foo assigning
fooseq.nextval to :new.<primarykeycolumn>

This approach may look cumbersome, but the advantage is a sequence is not
bound to one single table.

Hth,

Sybrand Bakker, Oracle DBA



Wed, 18 Jun 1902 08:00:00 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. maintain autonumber sequence

2. AutoNumber or Sequence number?

3. Autonumber/Sequence

4. autonumber with trigger and/or sequence

5. own AutoNumber table and autonumber SP skipping numbers

6. Oracle - Autonumber columns - Like MS-Access AutoNumber?

7. Exploring sequences (sequence context within a tran saction)

8. Exploring sequences (sequence context within a tran

9. Exploring sequences (sequence context within a

10. Exploring sequences (sequence context within a transaction)

11. Exploring sequences (sequence context within a transaction)

12. Implementing sequences without sequences


 
Powered by phpBB® Forum Software