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

From: <morris_gary_at_bah.com>
Date: 1997/07/09
Message-ID: <868469235.20569@dejanews.com>#1/1

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 Wed Jul 09 1997 - 00:00:00 CDT