thomas, cursor with 'for update of' would do the trick too ?
Ted
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.
Received on Mon Sep 27 1999 - 14:08:37 CDT
