select * from emp where ename like upper(NVL('%mi%',ename));

select * from emp where ename like '%MI%';

for MI* select * from emp where ename like 'MI%';
for *MI* select * from emp where ename like '%MI%';
for *MI select * from emp where ename like '%MI';
for *M*I* select * from emp where ename like '%M%I%';

PROCEDURE get_emp_list(
    p_ename IN VARCHAR2,
    rec_emp_list OUT sys_refcursor)
IS
  l_main_str VARCHAR2(2000):='SELECT * FROM EMP';
  l_str VARCHAR2(200);
  l_ename varchar2(20);
BEGIN
  IF p_ename   IS NOT NULL THEN
    l_ename    :=REPLACE(p_ename,'*','%');
    l_str      :=' where upper(ename) like upper('''||l_ename||''')';
    l_main_str :=l_main_str||l_str;
  END IF;
  OPEN rec_emp_list FOR l_main_str;
  dbms_output.put_line(l_main_str);
END get_emp_list;