How to consolidate code for WHERE column IN (prm,....,prn-n)??

From: Eric Givler <egivler_at_flash.net>
Date: Wed, 17 Jan 2001 18:23:34 GMT
Message-ID: <Gql96.7587$J%.785998_at_news.flash.net>

[Quoted] I have a number of functions and procedures that use cursors to lookup information. The code looks similar to the following:

SELECT attribute_value
into vDUMMY

     FROM custom_attributes
     WHERE owner_eid = pu_eid
     AND attribute_type_code in
('CBELECTR','CSELECTR','PVELECTR','OGTELETY',
                                 'CSELEC20', 'CSELEC30', 'CSELEC40',

'CSELEC50'); The list in this example is hard-coded. In the above case, if the query returns a row, we want to say that the site is "ELECTRIC".

In other cases, the list is NOT hard-coded but comes in as a parameter, ie.

WHERE owner_eid = pu_eid_in

   and the_list_prm_in like '%' || ATTRIBUTE_TYPE_CODE || '%'

This seems kind of "kludgy". In this case the variable: the_list_prm_in would contain:
(CBELECTR,CBSELECTR,PVELECTR,OGTELETY,CSELEC20,CSELEC30,CSELEC40,CSELEC50) What I was wondering....
Is there a way to make these a little more "generic". How can I pass a comma separated list of items in as a parameter and have the code behave like this:

select attribute_value
  into vDummy
from custom_attributes
where owner_eid = pu_eid
  and attribute_type_code in the_list_prm_in;

I know the above SQL is invalid. I was thinking of going the route of "dynamic-sql", but I'd like the ability to use ref-cursors with this just because it's so easy to interact with them in SQL*Plus for testing purposes.

Any suggestions are greatly appreciated. I can post some examples of how I was trying to consolidate the code. Received on Wed Jan 17 2001 - 19:23:34 CET