RE: IN comparison operator in PL/SQL cursor

From: Borrill, Christopher <Chris.Borrill_at_hp.com>
Date: Thu, 14 Dec 2006 10:15:18 +1300
Message-ID: <94941EE84F6FCD43B55C5B64DD0DAC6A0406B8FD@nzmexc01.asiapacific.cpqcorp.net>

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mercadante, Thomas F (LABOR)
Sent: Thursday, 14 December 2006 10:03 a.m. To: gequelch_at_frii.com; 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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] 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_at_frii.net

--

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

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

--

http://www.freelists.org/webpage/oracle-l Received on Wed Dec 13 2006 - 15:15:18 CST