I have two relatively large procedures that differ only
by the contents of an "in (...)" clause, e.g.

create proc1 as
...
select foo from bar where x in ('a','b')
...

create proc2 as
...
select foo from bar where x in ('x','y')
...

I would like to parameterize this, so that proc1 and proc2 can
call a common subroutine, and pass in the "in" list.

create proc1 as ... baseproc(['a','b']) -- some representation of a,b
create proc2 as ... baseproc(['x','y']) -- some representation of x,y

create baseproc(INLIST SOMETYPE)
...
select foo from bar where x in INLIST
...

How can I parameterize this? Is there an appropriate type?

Many TIA!!
Mark

--
Mark Harrison
Pixar Animation Studios

mh (AT) pixar (DOT) com schrieb:
I have serious doubts the best way exists, but there are many
alternatives which you can implement. Besides that, the question you ask
was very often discussed ( keyword: dynamic in list) here or at AskTom.
Here are some approaches:

1) select foo from bar where instr(','||INLIST||',',','||x||',') > 0
2) select foo from bar where x in ( select
regexp_substr(INLIST,'(^|,)(.*)(,|$)','\2',level,1 )
from dual
connect by regexp_substr(INLIST,'(^|,)(.*)(,|$)','\2',level,1 )
is not null)

David Fitzjarell has published on his site an approach as well
http://oratips-ddf.blogspot.com/2008...w-dynamic.html

But, as i mentioned already, this question is probably asked as often as
about pivot tables, so if you start to search, you'll find dozens of
possibilities


Best regards

Maxim

On 01.03.2009 10:23, Maxim Demenko wrote:
Another option: create a table type and use a query roughly like

select foo from bar where x in ( select column_value from table(array) )

That's true.

Kind regards

robert

On Mar 1, 10:42*am, Robert Klemme <shortcut... (AT) googlemail (DOT) com> wrote:
The cleanest and simplest approach might be to pass a collection into
the procedure and replace the IN clause with MEMBER OF. I'm not sure
how well that scales for large collections though.