Re: Oracle 8i (Witty answer to a unique id generation problem)

On Mon, 2 Aug 1999 15:55:43 +0200, "GPVmbH" <GPVmbH.Engelsdorf_at_t-online.de> wrote:

>I'm a Oracle beginner, and I've a problem with create a table with rowid
>
>I have create a table like adresse varchar2(20), str varchar2(20) and so on.
>In one field I want a number with the type long , which is unique like the
>field rowid.
>
>If I add a row the field must be one more than the last

That's not what the rowid datatype is for, I think. the rowid type you use in PL/SQL code to store a reference to a particular row. I don't think beginners need it.

What you want is to generate a unique id for every new record right?

You have to create a SEQUENCE for that. A sequence is a seperate thingy from your table, and will generate a unique id for you, even when 100's of users are creating new records in your table all at the same time.

Read your books or the Schema-manager-helpfile on creating a sequence, say you call it 'my_sequence'. Everytime you call to my_sequence.nextval, you get a unique id number for use in your primary key field. In the same way my_sequence.curval gives you the current id number.

Then create a "pre-insert" trigger in your datablock, and write the trigger as something like:

  select my_sequence.nextval into :mytable.id from dual;

Where 'mytable' is the name of your table, and 'id' the name of your primary key field for which you want to generate the id. 'dual' is the name for the esoteric, not-really existing virtual system table where miscellaneous stuff in Oracle seems to come from when nobody really knows where exactly they come from, like SYSDATE. ;-) (select sysdate from dual;)

That's it.. Ah, don't be alarmed when it seems like a basic thing like this takes hours and hours to figure out in Oracle... When things get bigger and more complex Oracle keeps things a lot more straightforward than Access (for example..) even though it only takes 2 seconds to automatically generate a unique id in Access and even monkies can do it. Also remember monkies make a lot less money than Oracle programmers, so in the end it'll be worth it. ;-)

Cheers,
Han.

+----------------------------------------------------------+
[Quoted] [Quoted] | Han Thomas               30/1 Ton Kham Road,  Tha Sala   |
| Han_at_Royal.net            Muang Chiang Mai 50000 THAILAND |
| www.afmgroup.com/han     tel 053-308958 fax 249854       |
+----------------------------------------------------------+