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

morris_gary_at_bah.com wrote:
>
> 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

For a simple approach,
How about an "if" statement to choose the appropriate cursor based on the input variable.

Alternatively, dbms_sql(I think) packages in $ORACLE_HOME/rdbms/admin allow you to dynamically define and execute sql statements. Received on Thu Jul 10 1997 - 00:00:00 CDT