Re: Large IN LIST in an OBIEE query

From: Stephane Faroult <sfaroult_at_roughsea.com>
Date: Sun, 30 Oct 2011 10:04:14 +0100
Message-ID: <4EAD130E.400_at_roughsea.com>

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

Received on Sun Oct 30 2011 - 04:04:14 CDT