Re: Selecting from a sequence based on a variable name?

bracher_at_my-dejanews.com wrote:
>
> In article <35eddd5b.25411625_at_netnews.worldnet.att.net>,
> gennick_at_worldnet.att.net wrote:
> > On Sun, 23 Aug 1998 22:20:15 +0100, "Alexander Bisset"
> > <alexander_at_bisset.demon.co.uk> wrote:
> >
> > >This does not work, but hopefully you get the idea. I need some method of
> > >selecting the next sequence number from one of many sequences based on a
> > >variable passed into the function.
> >
> > You need to use the DBMS_SQL package. It's documented in the
>
> yes, but I don't think dbms_sql is going to work in a function, as it
> does not guarantee not to alter the database. whoops, that's a double
> negative, but it does convey the intended context. changing a database
> from within a function is a big no-no in oracle. you'll see an error
> like
>
> ORA-06571: Function FNC_NAME does not guarantee not to update database
>

A stored function has no problems updating something in the database (I do it all the time), as long as you don't call the function from within an sql-statement.
This does not work (gives ORA-06571) if the function changes something in the database:
SELECT my_function FROM my_table;
INSERT INTO my_table(my_key) VALUES(my_function);

But there is no problem when calling the function from within another procedure or function (or from e.g. Forms Builder).

DECLARE
  next_value NUMBER;
BEGIN
  next_value := my_function;
  INSERT INTO my_table(my_key) VALUES(next_value); END; Marc Billiet Received on Fri Sep 11 1998 - 00:28:57 CDT