RE: IN comparison operator in PL/SQL cursor

Even better is the solution described in this article, which I have use
successfully:

http://www.oracledba.co.uk/tips/collections.htm

Chris

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Mercadante, Thomas F
(LABOR)
Sent: Thursday, 14 December 2006 10:03 a.m.
To: gequelch@xxxxxxxx; oracle-l
Subject: RE: IN comparison operator in PL/SQL cursor

Geoff,

Take a look at dynamic sql/execute immediate.  You are building a
dynamic query.  I suspect that if you passed in just one value for
sd.abbreviation, it would work fine (GetStationDataByList('a');)

But if you pass in a real list like 'a,b,c', then the in clause is
looking specifically for that string GetStationDataByList('a,b,c').

Tom


--------------------------------------------------------
This transmission may contain confidential, proprietary, or privileged
information which is intended solely for use by the individual or entity
to whom it is addressed.  If you are not the intended recipient, you are
hereby notified that any disclosure, dissemination, copying or
distribution of this transmission or its attachments is strictly
prohibited.  In addition, unauthorized access to this transmission may
violate federal or State law, including the Electronic Communications
Privacy Act of 1985.  If you have received this transmission in error,
please notify the sender immediately by return e-mail and delete the
transmission and its attachments.


-----Original Message-----

From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Geoffrey E. Quelch
Sent: Wednesday, December 13, 2006 3:57 PM
To: oracle-l
Subject: IN comparison operator in PL/SQL cursor

Hi,
I'm wondering if someone on this list can help with this problem I'm
trying to resolve.

I'm attempting to use an IN comparison operator in a PL/SQL cursor and
can't seem to get it to work.

Pseudo code follows:

    PROCEDURE GetStationDataByList (
        in_list     IN VARCHAR2
    )
    IS
        -- Retrieval CURSOR
        CURSOR cr_retrieve IS
        SELECT SD.Value AS Value
        FROM Station_Data SD
        WHERE SD.Abbreviation IN (in_list)

(I have removed unrelated items in this cursor.)

As written, the above doesn't work, how can I modify "in_list" to get
this to work? Is this in fact even possible?

By not work, I mean no rows are returned and I don't get an error. If I
move the select statement to sqlplus and place the text I have in
"in_list" into the operator, I get rows returned as expected.

I've consulted the Oracle documentation, and there appears to be no
mention on how this needs to be done.

Thanks
Geoff Quelch

--
Geoff Quelch
Email: gequelch@xxxxxxxx

--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l

Other related posts: