RE: Large IN LIST in an OBIEE query

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Sun, 30 Oct 2011 16:04:01 -0400
Message-ID: <00e201cc973f$12438350$36ca89f0$_at_rsiz.com>

Nods. In case anyone wants it, this works from 8i forward (you can turn serveroutput on to examine how it works or delete the dbms_output lines if you don't want them to be toggle-able). This is the actual f_trans from my earlier post. I thought it was a pipeline function, but I guess this version was not. This works if the folks want a single bind variable to hold the list, similar to what Stephan wrote.

create or replace type outrecset IS TABLE OF number; /
/*
*/
CREATE OR REPLACE PACKAGE val_list IS
FUNCTION f_trans(p varchar)
RETURN outrecset;
END val_list;
/

CREATE OR REPLACE PACKAGE BODY val_list IS FUNCTION f_trans(p varchar)
RETURN outrecset IS

out_rec outrecset := outrecset();

i pls_integer := 1;
j pls_integer := 0;
k pls_integer := 1;

BEGIN
dbms_output.put_line('p varchar is: ' || p);   LOOP
    j := instr(p,',',i);
    dbms_output.put_line('j is: ' || j);     dbms_output.put_line('substr ' || substr(p,i,j-i));     EXIT WHEN j = 0 OR j IS NULL;
    out_rec.extend;
    out_rec(k) := to_number(substr(p,i,j-i));     i := j + 1;
    k := k + 1;
  END LOOP;
  out_rec.extend;
  out_rec(k) := to_number(substr(p,i));
  RETURN out_rec;
END;
END val_list;
/

select * from foo where id in (select * from table(val_list.f_trans('1,2,3,4')));

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Stephane Faroult
Sent: Sunday, October 30, 2011 5:04 AM
To: jkstill_at_gmail.com
Cc: Hemant.Chitale_at_sc.com; oracle-l_at_freelists.org Subject: 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


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

Received on Sun Oct 30 2011 - 15:04:01 CDT