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

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:
>
> >begin
> > strSeqName := 'sequence_' || to_char(seqYear);
> > select strSeqName.nextval into intSeqNum from dual;
> > return intSeqNum;
> >end;
> >
> >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
> Application Developer's Guide. Essentially, it lets you
> build up dynamic SQL statements as character strings, and
> then execute same.
>
> regards,
>
> Jonathan Gennick

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

I've tried specifying the restrict_references pragma with wnds for the underlying procedures that contain the dbms_sql calls, but oracle complains that

PLS-00452: Subprogram 'NEXT_INTEGER' violates its associated pragma

at build time. I assume because you could pass an insert/update/delete to dbms_sql just as easily as a select, and it can't guard against it at build time.

in order to use dbms_sql, I've had to write the whole thing using procedures. not a problem but it's hassle binding output variables into the procedure call instead of just selecting from dual. especially since a good amount of code has already been written calling the first iteration, which was a function...

• mark

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Wed Sep 09 1998 - 18:34:47 CDT