create table EMPLOYEES
(
  EMP_ID   NUMBER,
  EMP_NAME VARCHAR2(100)
);


insert into EMPLOYEES (EMP_ID, EMP_NAME)
values (10, 'Jack');
insert into EMPLOYEES (EMP_ID, EMP_NAME)
values (20, 'Jill');
insert into EMPLOYEES (EMP_ID, EMP_NAME)
values (30, 'hill');
commit;

SQL> create or replace type empname is table of varchar2(40);
  2  /

Type created

SQL> create or replace type empdet is table of varchar2(40);
  2  /

Type created

SQL> create or replace procedure test_emp (p_emp_name varchar2)
  2  is
  3  v_str varchar2(200) := p_emp_name||','||' ';
  4  v_emp empname := empname();
  5  v_pos number;
  6  --v_len := length(p_emp_name);
  7  empd empdet := empdet();
  8  begin
  9  loop
 10  exit when v_str is null;
 11  v_pos := instr(v_str,',');
 12  v_emp.extend;
 13  v_emp(v_emp.count):=ltrim(rtrim(substr(v_str,1,v_pos-1)));
 14  v_str := substr(v_str,v_pos+1);
 15  end loop;
 16  select emp_name into empd from employees where emp_name in 
(select * from table(cast(v_emp as empname)));
 17  FOR i in 1..empd.count loop
 18  dbms_output.put_line(empd(i));
 19  end loop;
 20  end;
 21  /

Warning: Procedure created with compilation errors

SQL> show errors;
Errors for PROCEDURE SLDDEVELOPER.TEST_EMP:

LINE/COL ERROR
-------- ----------------------------------------------------------------
16/8     PL/SQL: ORA-00932: inconsistent datatypes: expected UDT got CHAR
16/1     PL/SQL: SQL Statement ignored