Re: Help:Pl/SQL Pass variable for sequence into SQL Select

I am afraid, parametrizing of select statment in procedure is impossible. Try use directly system tables
all_sequences or user_sequences.

 in SQL Plus you can write:

define xx = s0021.NEXTVAL;
select &xx from dual;
... and it return next number

but in procedure it does not work.

Richard

 morris_gary_at_bah.com wrote in article <868469235.20569_at_dejanews.com>...
>I'm somewhat of a beginner trying to write a fairly simple sequence
>generator in PL/SQL. The user selects a client name and based on that
>client name it returns the next sequence for that client.
>
>Let's say there are three clients, 0021, 0022, and 0023. Each time a user
>selects a client number, it will get the .NEXTVAL for it's sequence.
 I've
>named the sequences according to clients. Sequences entitled s0021,
>s0022, and s0023. So, let's say:
>vClient = 0021;
> In a select statement I want to get: 's'||vClient||'.NEXTVAL' which I
>cannot seem to do.. Here is part of my code:
>
>_________
>FUNCTION getRpuInsert(Client IN VARCHAR2) RETURN NUMBER IS -- Client=0021
> v_Result VARCHAR2(100);
> CURSOR c_getRpuInsert IS
> SELECT 's'||Client||'.NEXTVAL'
> FROM DUAL;
>BEGIN
> OPEN c_getRpuInsert;
> FETCH c_getRpuInsert INTO v_Result;
> RETURN v_Result;
>END getRpuInsert;
>_________
>This program will return 's0021.NEXTVAL' instead of 35 (or whatever the
>next sequence is. How do I get it to accept a variable and not always
>treat it as a string? Any help is appreciated!
>
>Gary
>
>-------------------==== Posted via Deja News ====-----------------------
> http://www.dejanews.com/ Search, Read, Post to Usenet
>
Received on Thu Jul 10 1997 - 00:00:00 CDT