Re: Large IN LIST in an OBIEE query
- From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- To: jkstill@xxxxxxxxx
- Date: Sun, 30 Oct 2011 10:04:14 +0100
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
Other related posts:
