Base Table 

create table opportunity(
  name varchar2(100),
  product_ids varchar2(255)
)
/

 Populate Base Table 

insert into opportunity values ( 'A', '1:2:3' );
insert into opportunity values ( 'B', '1:10' );
insert into opportunity values ( 'C', '1' );
insert into opportunity values ( 'D', '2:9' );
insert into opportunity values ( 'E', '8' );
insert into opportunity values ( 'F', '7:3' );
insert into opportunity values ( 'G', '5:6:10' );
insert into opportunity values ( 'H', '1:11' );
insert into opportunity values ( 'I', '3:9' );
insert into opportunity values ( 'J', '2:4:6:8' );
insert into opportunity values ( 'K', '7:8:9:10' );
insert into opportunity values ( 'L', '4' );


 SQL Type 

create or replace 
type myTableType as table of varchar2(255)
/

 Pipelined Function Definition 

create or replace
function to_myTableType( p_string varchar2 ) return myTableType
PIPELINED is
  l_arr apex_application_global.vc_arr2;
begin
  l_arr := apex_util.string_to_table( p_string );
  for i in 1 .. l_arr.count loop
    PIPE ROW( ':' || l_arr(i) || ':' );
  end loop;
  return;
end;
/

 Region Query 

select distinct o.name, o.product_ids
  from opportunity o,
         TABLE( to_myTableType( :P9_PRODUCT_IDS ) ) p
 where instr( ':' || o.product_ids || ':', p.COLUMN_VALUE ) > 0
/