RE: Large IN LIST in an OBIEE query
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <sfaroult@xxxxxxxxxxxx>, <jkstill@xxxxxxxxx>
- Date: Sun, 30 Oct 2011 16:04:01 -0400
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Stephane Faroult
Sent: Sunday, October 30, 2011 5:04 AM
To: jkstill@xxxxxxxxx
Cc: Hemant.Chitale@xxxxxx; oracle-l@xxxxxxxxxxxxx
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>
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
