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

> 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.

In 8i use native dynamic SQL. In previous versions use the DBMS_SQL package.

Daniel A. Morgan Received on Sun Jan 21 2001 - 09:49:06 CET