Re: Is this safe???

A copy of this was sent to tedchyn_at_yahoo.com (if that email address didn't require changing) On Mon, 27 Sep 1999 19:08:37 GMT, you wrote:

>thomas, cursor with 'for update of' would do the trick too ?
>Ted

yes, it would work. it would be terribly inefficient.... sequences are the right way.

>In article <2sjuN7p=sTetSQVUANfhM4d6aSbL_at_4ax.com>,
> tkyte_at_us.oracle.com wrote:
>> A copy of this was sent to Chris Forlano <cforlano_at_nortelnetworks.com>
>> (if that email address didn't require changing)
>> On Sun, 26 Sep 1999 23:00:20 +0100, you wrote:
>>
>> >Does the following procedure guarantee atomicity for the id_generator
>> >table?
>> >What I mean to say is, how can I guarantee that no one else will be
>able
>> >to
>> >update the id_generator table at the same time this function is
>running?
>> >
>>
>> no one will be able to update it concurrently -- HOWEVER since you
>select first
>> and then update -- you will generate many many duplicate IDs out of
>this
>> routine.
>>
>> change it to this in Oracle:
>>
>> create sequence my_seq;
>>
>> create or replace function generate_id( p_type_in varchar2 ) return
>varchar2
>> as
>> l_id number;
>> begin
>> select my_seq.nextval into l_id from dual;
>>
>> return p_type_in || to_char( l_id, 'fm000000009' );
>> end;
>> /
>>
>> Use a sequence, don't use a table.
>>
>> If you must, absolutely must, use the table (bad idea), put the UPDATE
>first and
>> then read the value out to SERIALIZE (eg: have no concurrency) on this
>> operation.
>>
>> >If not, how can I guarantee it?
>> >Can I do this as a function?
>> >
>> >create or replace
>> >PROCEDURE generate_id( type_in in varchar2, id_out out varchar2 )
>> >AS
>> > CURSOR id_cursor IS
>> > SELECT prefix, next_id FROM id_generator WHERE type = type_in;
>> > prefix varchar2(20);
>> > next_id number(9);
>> >BEGIN
>> >
>> > OPEN id_cursor;
>> >
>> > /* Get the next available id. */
>> > FETCH id_cursor INTO prefix, next_id;
>> >
>> > /* Update to the next available id. */
>> > UPDATE id_generator SET next_id = next_id + 1 WHERE type = type_in;
>> >
>> > /* Create an id containing a prefix and id (ex, TST000123). */
>> > id_out := CONCAT( prefix, LPAD( TO_CHAR( next_id ), 9, '0' ) );
>> >
>> > CLOSE id_cursor;
>> >
>> >END generate_id;
>> >
>> >I'm running Oracle 8.0.5 on Solaris 2.6.
>> >
>> >Thanks,
>> >
>> >Chris
>>
>> --
>> See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to
>Oracle8i'...
>> Current article is "Part I of V, Autonomous Transactions" updated June
>21'st
>>
>> Thomas Kyte tkyte_at_us.oracle.com
>> Oracle Service Industries Reston, VA USA
>>
>> Opinions are mine and do not necessarily reflect those of Oracle
>Corporation
>>
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Sep 29 1999 - 12:01:29 CDT