using sequences in a function

From: Tod Meinke <tod_at_cyberramp.net>
Date: Sat, 27 Mar 1999 08:26:18 -0600
Message-ID: <80BCCC9727105437.7E3344D9340F3425.84197B4924954F4E@library-proxy.airnews.net>

I'm trying to write a function where I pass a sequence name, check for its existence and then either increment it or not based on its current and max values. How can I substitute the sequence name into a variable? The code below will change the variable name to the passed value when it compiles, which I don't want.

Thanks!

Tod Meinke
tod_at_cyber(no spam please)ramp.net remove the parenthetical for my address.

---

Function SEQ_CHECK
  ( seq_name_input IN varchar2)
RETURN number IS

var_exists number(1);
var_currval number;
var_maxvalue number;
var_increment number;

BEGIN
select count(*) into var_exists from user_sequences where sequence_name = seq_name_input ;

if var_exists = 1 then
select &&seq_name..currval into var_currval from dual; select max_value into var_maxvalue from user_sequences where sequence_name = seq_name_input ;
select increment_by into var_increment from user_sequences where sequence_name = seq_name_input ;
 if var_currval + var_increment < var_maxvalue then  select &&seq_name.nextval into seq_nextval from dual;  else
 seq_nextval := 0;
 end;
end if;

RETURN seq_nextval;
END SEQ_CHECK;

---

Received on Sat Mar 27 1999 - 08:26:18 CST