Is this safe???

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?

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

--
Chris Forlano
Automation Development
Nortel Networks, Maidenhead
590 4342 (01628 434 342)
cforlano_at_nortelnetworks.com Received on Sun Sep 26 1999 - 17:00:20 CDT