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...)

You can add comment on current page, or on forum page there.Post count 5.