Re: Unique Key

Paul,

It sounds like you should read up on Oracle sequences. If you have an Oracle Server SQL reference, look under CREATE SEQUENCE. An Oracle "sequence" is simply a counter that is maintained by the database. It is always incremented every time it is refernced. There are no worries about locking, and it is not affected by whether or not you commit a transaction.

to create a sequence:

	CREATE SEQUENCE SEQ_NAME 
		START WITH 1 
		INCREMENT BY 1
		MAXVALUE 99999;

To get the next value, you have to do a select from the DUAL table, like this:

        SELECT seq_name.NEXTVAL from DUAL;

Usually, in a program or trigger, you select INTO some variable:

        SELECT seq_name.NEXTVAL INTO your_variable_name FROM DUAL; Hope this helps.

Jonathan

On Tue, 31 Mar 1998 14:25:44 -0500, Paul Bennett <bennett_at_cc.gatech.edu> wrote:

>Can someone point me to some stored procedures that are used to generate
>net keys?
>
>I have a legacy client server application where the client generates the
>next key by checking table that is setup like:
>
>CODE NEXT_VALUE
>
>Some sample data would be
>
>MODULE1 100
>MODULE2 201
>MODULEA 4345
>
>The client has some functions that you pass "MODULE1" and it will lock
>the row, update the value to 101, then return the value to the caller.
>
>I would like to move this to the database and have the client call a
>stored procedure. I need some code that is similar in the fact that it
>uses a "code" value in a "next sequence" table.
>
>Thank you for your help.
>
>-- Paul
Received on Wed Apr 01 1998 - 00:00:00 CST