Oracle – How use string data in (in operator)

In tbl_1 I have:

id
1
2
3
4
5
6
7
8
9

in tbl_2:
id  value
1   1,2,3
2   5

Select * from tbl_1 where id in (Select value from tbl_2 where id = 2); –is OK

Select * from tbl_1 where id in (Select value from tbl_2 where id = 1);

--Need this resault: 3 rows:  1, 2 and 3

Answer

Yet another option:

SQL> with
  2  -- sample data
  3  tbl_1 (id) as
  4    (select 1 from dual union all
  5     select 2 from dual union all
  6     select 3 from dual union all
  7     select 4 from dual union all
  8     select 5 from dual union all
  9     select 6 from dual union all
 10     select 7 from dual union all
 11     select 8 from dual union all
 12     select 9 from dual
 13    ),
 14  tbl_2 (id, value) as
 15    (select 1, '1,2,3' from dual union all
 16     select 2, '5,6,7' from dual
 17    )
 18  -- query which returns what you want
 19  select a.id
 20  from tbl_1 a join
 21    (select regexp_substr(b.value, '[^,]+', 1, column_value) id
 22     from tbl_2 b cross join
 23       table(cast(multiset(select level from dual
 24                           connect by level <= regexp_count(b.value, ',') + 1
 25                          ) as sys.odcinumberlist))
 26     where b.id = 1
 27    ) c on c.id = a.id;

        ID
----------
         1
         2
         3

SQL>