CREATE OR REPLACE PROCEDURE SP_TMP_CERCA_CON_SPLIT(p_CD_GRMS varchar2) as

BEGIN
  -- PROCEDURA D'ESEMPIO PER FARE UNA "IN" CON VALORI PASSATI IN INPUT
COME STRINGA SEPARATI DA VIRGOLA
  INSERT into TMP_ARRAY(char30)
  select g.ds_grm
  from anag_gruppi_merceologici g
  where g.cd_grm in (select regexp_substr(p_CD_GRMS,'[^,]+', 1, level) from dual
                     connect by regexp_substr(p_CD_GRMS, '[^,]+', 1,
level) is not null);
  COMMIT;
END;

https://blogs.oracle.com/aramamoo/entry/how_to_split_comma_separated_string_and_pass_to_in_clause_of_select_statement