Steel
Programming
+38 098 888 58 89 Alexander
s.berezovich Skype
steelprogram@gmail.com E-mail
Oracle select from string (varchar2)
If you receive string with list of ID separated in some way as input, and you should use this string as filer, you have few ways do this.
1. Dynamic SQL
declare
ids varchar2(100) := '1,2,3,4,5';
l_cnt number;
begin
execute immediate 'select count(*) from account where account_id in (' || ids || ')' into l_cnt;
dbms_output.put_line(l_cnt);
end;
Minuses - dynamic SQL should use when you can't do your task without dynamic sql, too large performance impact in bad side. Dynamic SQL is way to the Dark side.
2. Check is ID in string
declare
ids varchar2(100) := '1,2,3,4,5';
l_cnt number;
begin
select count(*)
into l_cnt
from trader_account
where instr(ids || ',', account_id || ',') != 0;
dbms_output.put_line(l_cnt);
end;
Minuses - not used index by table. In his situation will be fast index scan (INDEX FAST FULL SCAN) because all data needed for this select already in index, so Oracle will use index instead of table, but in another select where not all data will be in index, Oracle will use full table scan TABLE ACCESS FULL
3. Transform string into datasource
declare
ids varchar2(100) := '1,2,3,4,5';
l_cnt number;
begin
select count(*)
into l_cnt
from account
where account_id in
(select TO_NUMBER(trim(SUBSTR(t1.str,
t1.curr_pos + 1,
DECODE(t1.next_pos,
0,
LENGTH(t1.str) + 2,
t1.next_pos)
- t1.curr_pos - 1)))
from (select ids str,
DECODE(level,
1,
0,
INSTR(ids, ',', 1, level - 1)
) as curr_pos,
INSTR(ids, ',', 1, level) as next_pos
from dual
connect by level <= LENGTH(ids)
- LENGTH(
replace(ids,
',',
'')
) + 1) t1);
dbms_output.put_line(l_cnt);
end;
Pluses - index will be used
Minuses - max length of string (SQL 4000, PL/SQL 32768)
4. Transform array of strings into datasource.
declare
ids1 varchar2(100) := '1,2,3,4,5';
ids2 varchar2(100) := '7,8,9,10,11,12';
l_cnt number;
begin
with tmp_str as
(select 1 as id, ids1 as ids
from dual
union
select 2 as id, ids2 as ids from dual),
tmp_str2 as
(select t.*, length(ids) - LENGTH(replace(ids, ',', '')) + 1 as cnt from tmp_str t),
tmp_dual as
(select level as lv from dual connect by level <= (select max(cnt) as total_cnt from tmp_str2)),
tmp_str3 as
(select *
from (select id, lv, ids, cnt, row_number() over(partition by id order by lv) as rn from tmp_dual, tmp_str2) t1
where rn <= cnt)
select count(*)
into l_cnt
from account
where account_id in
(select TO_NUMBER(trim(SUBSTR(t1.str,
t1.curr_pos + 1,
DECODE(t1.next_pos, 0, LENGTH(t1.str) + 2, t1.next_pos) - t1.curr_pos - 1)))
from (select ids str,
DECODE(lv, 1, 0, INSTR(ids, ',', 1, lv - 1)) as curr_pos,
INSTR(ids, ',', 1, lv) as next_pos
from tmp_str3) t1);
dbms_output.put_line(l_cnt);
end;
When you have more than one string for filtering you can use last way. Algorithm can be easy modified for your datasource (pl/sql table, array...)