Re: Strange PB !?

>hi,
>thanks for help but can you write some lines on how to implement a
>sequence in my pl/sql code?
>
>
>
>
>In article <7k09qr$be3$1_at_news1.inlink.com>,
> "Russ Whiteman" <russw_at_inlink.com> wrote:
>>
>> Steve Cosner wrote in message <7jveev$h7g$1_at_nntp.csufresno.edu>...
>> >In article <7jv0j6$l84$1_at_nnrp1.deja.com>,
>> >Wassim <net2000_at_francemel.com> wrote:
>> >>I've in many of my PL/SQL procedures to create a new record in a
>> table
>> >>just with Key code = Last key + 1, (key_code is the table primary
>> key)
>> >>
>> >>--------------------------------------------------------------------
>> ----
>> >>last_key_code := 0;
>> >>begin
>> >>select max(key_code) into last_key_code from myTable;
>> >>exception when OTHERS then last_key_code:=0; -- when myTable is
>> empty !!
>> >>end;
>> >>
>> >>htp.print(last_key_code);
>> >>
>> >>insert into myTable values (last_key_code+1,...);
>> >>--------------------------------------------------------------------
>> ----
>>
>> >
>> >Yes, a sequence is the accepted way to insert a new key value. But
>> >your way would work, as well. It is just that when there are no rows
>> >in your table, the max function returns null. It does not produce an
>> >exception as you are trying to trap.
>> >
>>
>> Actually, there are circumstances in a multi-user database where using
>> "max(key_code) + 1" will NOT work, i.e. what happens when someone else
>> inserts a record (during the fraction of a second) between your
>> calculation and your insert? Your insert fails...
>>
>> A sequence is by far the best way to do this. There are other ways,
>> but they either do no more than duplicate the functionality of a
>> sequence, or they involve locking the table so nobody else can insert.
>>
>>
>

2 comments... the reason you original code did not trap the error is because there was no error. The functions MAX and MIN (and COUNT) return 0 if no rows are found. (There may be others; I havn't checked).

To use a sequence in your insert, you have to first define it;

define sequence my_seq initial 1 increment 1;

(This will start it at 1, and increment it 1 each time someone uses it)

Then you can reference it in an insert like this:

insert into mytable (seq_column, col1, col2) values (my_seq.nextvalue, col1_value, col2_value);

If the sequence is not owned by the user, the user will need GRANT SELECT ON MY_SEQ to <user>, and qualify the use by specifying the owner.

HTH Dan Hekimian-Williams Received on Tue Jun 15 1999 - 02:46:21 CEST