IN list of where condition using in a cursor

From: C Chang <cschang_at_maxinter.net>
Date: Thu, 06 Feb 2003 23:01:27 -0500
Message-ID: <3E432F97.1F20@maxinter.net>

I use an explicit cursor to get multiple rows of records. My qustion is how to apply the variable to the IN list in the where clause. My example as

CURSOR c_qty_rcvd ( i_site      IN  VARCHAR2,
                    i_vendor    IN  VARCHAR2) IS
        SELECT SUM(r.qty), r.unit
        FROM receipts r, lines p, heads h
        WHERE r.site_id= i_site
        AND h.vendor_id = i_vendor  <--- replace with h.vendor_id IN
(...)
        AND (other conditions);

In the IN list some cases, there is only one variable, sometimes there are more than one.

So when I use

    OPEN c_qty_rcvd(v_site,v_vendor);
How do I prepare the IN list in the Cursor? I have tried to turn i_vendor into string such as h.vendor_id IN (i_vendor) in CURSOR and v_vendor := ''A'',''B''; before the OPEN but the compiler compalined. And I can put v_vendor := '(''A'',''B'')'; Since This cursor is in a loop of another cursor, the method mentioned in Tom's aarticle from web site (AskTom) seems not quite proper. I really do not know what to do. Need anyone help? Thanks.

C Chang

C Chang Received on Thu Feb 06 2003 - 22:01:27 CST