Re: using sequences in a function

From: Nina Wiesemann <nw.th_at_rhein-main.net>
Date: Sun, 28 Mar 1999 20:15:42 +0100
Message-ID: <7dlv6d$jro$1@newsreader.ipf.de>

You have to use the dynamic-sql-package where you can dynamically create a sql-statement in a varchar variable and execute it via the package.

N. Wiesemann

---

Im Beitrag
<80BCCC9727105437.7E3344D9340F3425.84197B4924954F4E_at_library-proxy.airnews.ne t>, "Tod Meinke" <tod_at_cyberramp.net> schrieb:

>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 Sun Mar 28 1999 - 13:15:42 CST