Re: AutoNumber Generation

From: Jonathan Gennick <gennick_at_worldnet.att.net>
Date: 1997/04/19
Message-ID: <33593420.2452233_at_netnews.worldnet.att.net>#1/1

Dave,

>...
>I am planning on using MS Access GUI and hitting Oracle via ODBC
>drivers. I need a way to generate a "next number" for my primary key.
>I would like the GUI know what numbers have already been used and to
>generate the next in sequence.
>
>What do I need to set in the Access GUI and/or in the Oracle database to
>perform this function? Any and all help appreciated. You can Email me
>direct or reply to the group. Your preference.

I would recommend going with an Oracle sequence. You can create one from SQL*Plus by:

SQL> create sequence key_for_table

  2         increment by 1
  3         start with 1
  4         nomaxvalue  --implies 10^27

  5 ;

Sequence created.

Then every time you reference it,Oracle automatically increments it. For example:

SQL> select key_for_table.NextVal from dual;

  NEXTVAL

---

        1

SQL> select key_for_table.NextVal from dual;

  NEXTVAL

---

        2

SQL> select key_for_table.NextVal from dual;

  NEXTVAL

---

        3

To be rock-solid safe on using this, you can have an Oracle trigger set the key value whenever a record is inserted. This will override any value supplied by a user. For example:

1 Create a table.
SQL> create table my_table (

  2          fld_pk  integer,
  3          fld_text        varchar2(2),
  4          constraint my_table_key
  5                  primary key (fld_pk)
  6          );

Table created.

2 Create a "before insert" trigger
SQL> create or replace trigger my_table_set_key

  2          before insert on my_table
  3          referencing new as n
  4          for each row

  5 declare
  6 new_key integer;
  7 begin
  8 select key_for_table.nextval into new_key from dual;   9 :n.fld_pk := new_key;
 10 end;
 11 /

Trigger created.

3 Note that there are now rows in the table. SQL> select * from my_table;

no rows selected

4 Insert some rows.
SQL> insert into my_table (fld_text) values ('A');

1 row created.

SQL> insert into my_table (fld_text) values ('B');

1 row created.

SQL>
SQL> select * from my_table;

   FLD_PK FL
--------- --

        1 A
        2 B

SQL> commit;

Commit complete.

5 And even if someone accidently specifies a   value for FLD_PK we are covered:

SQL> insert into my_table (fld_pk,fld_text) values (1,'C');

1 row created.

SQL> select * from my_table;

   FLD_PK FL
--------- --

        1 A
        2 B
        3 C

SQL> commit;

Commit complete.

If you allow updates on the table you will need to use a an update trigger or view to restrict the user from updating the key field.

One issue you will run into is that you might want to display the key value to the user as the record is being entered, BEFORE it is being saved. You can do this by just selecting key_for_table.nextval from dual and displaying it on the screen, but then if the user never saves the record that number will not be reused. You end up with holes in your keys. I've never found this to be a problem, but some people get all worked up about it. On the system I am currently working on, we query the database again after the save and then display the key value on the screen for the user to see.

Hope all this helps.

Jonathan Gennick

>David E. Daniel
>Database Administrator
>Western Michigan University
>david.daniel_at_wmich.edu

btw, I am in Lansing, not too far away from you.

---

Jonathan Gennick
gennick_at_worldnet.att.net
"Brighten the Corner Where you Are" Received on Sat Apr 19 1997 - 00:00:00 CEST