RE: IN comparison operator in PL/SQL cursor
- From: "Mercadante, Thomas F \(LABOR\)" <Thomas.Mercadante@xxxxxxxxxxxxxxxxx>
- To: <gequelch@xxxxxxxx>, "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 13 Dec 2006 16:02:32 -0500
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
