Re: Large IN LIST in an OBIEE query

On 10/30/2011 02:01 AM, Jared Still wrote:
> with mylist as (
>     select column_value owner
>     from (
>        table(
>           sys.odcivarchar2list( 'RT', 'TSMSYS', 'SCOTT', 'RMAN10G',
> 'RMAN11G', 'JKSTILL')
>        )
>     )
> )
> select t.owner, t.table_name
> from all_tables t
> join mylist m on m.owner = t.owner
> order by owner, table_name
> /
Unless the list is provided as a single character string that is 
concatenated to the query, in which case something that is similar in 
spirit works usually very well (used multiple times in prod):

variable list varchar2(4000)
begin
   :list := 'RT,TSMSYS,SCOTT,RMAN10G,RMAN11G,JKSTILL';
end;
/
with mylist as
      (select trim(substr(list,
                    instr(list, ',', 1, rn) + 1,
                    instr(list, ',', 1, rn + 1)
                     - instr(list, ',', 1, rn) - 1)) owner
       from (select list, rownum rn
             from (select ',' || trim(',' from :list) || ',' list  -- 
Paranoid processing,
                                                                              
-- in case developers have commas wrong
                      from dual)
             connect by level < length(list) - length(replace(list, ',', 
'')))) -- count items by counting commas
select t.owner, t.table_name
from all_tables t
join mylist m on m.owner = t.owner
order by owner, table_name
/


-- 
Stephane Faroult
RoughSea Ltd <http://www.roughsea.com>
Konagora <http://www.konagora.com>
RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>


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


Other related posts: