Re: sequence name as a variable

I really not sure what you're trying to do or how. Could you explain so that we might offer more help?

Mike Rose

Tod Meinke <c0012799_at_airmail.net> wrote in message news:4C2B1CA023A01898.1D6E8B4BCC4576E0.7AA760CD1C6977F7_at_library-proxy.airnew s.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 - 22:05:26 CST