Java SE (Java Platform, Standard Edition)

Bug or DESIGN
By design the query is returned at hard parse time, the parameter values is accessed at runtime.
Any ideas ? Suggestion ?
Split the string at runtime.

That does not fully explain the error - WHERE IN is a standard SQL clause ? - and I can not split the list of ids that I am looking up. Needs to be clearer...

That does not fully explain the error - WHERE IN is a standard SQL clause ?
Parameter acts like a bind variable. fahrbahnproj.employee_func('100, 101') -> ... employee_id in (:p_list_of_ids) -> ... employee_id in ('100, 101')  -> ORA-01722: invalid number
I can not split the list of ids that I am looking up.

with function sm(numlist varchar2) return varchar2 sql_macro as
  nl varchar2(32767 byte);
begin
  return 'select id from json_table(''[''||sm.numlist||'']'', ''$[*]'' columns (id number path ''$''))';
end;
select * from sm('11,22,33')
/

        ID
----------
        11
        22
        33

To split string you can use a table/varray function instead of sql_macro.

CREATE OR REPLACE
 FUNCTION EMPLOYEE_FUNC(
                        P_LIST_OF_IDS VARCHAR2
                       )
   RETURN VARCHAR2
   SQL_MACRO
   IS
   BEGIN
       RETURN Q'{
                 SELECT  EMPLOYEE_ID,
                         FIRST_NAME,
                         LAST_NAME,
                         EMAIL
                   FROM  EMPLOYEES
                   WHERE EMPLOYEE_ID IN (
                                         SELECT  TO_NUMBER(REGEXP_SUBSTR(P_LIST_OF_IDS,'\d+',1,LEVEL))
                                           FROM  DUAL
                                           CONNECT BY LEVEL <= REGEXP_COUNT(P_LIST_OF_IDS,',') + 1
                                        )
                }';
END EMPLOYEE_FUNC;
/

Function created.

SQL> SELECT  *
  2    FROM  EMPLOYEE_FUNC('100')
  3  /

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL
----------- -------------------- ------------------------- -------------------------
        100 Steven               King                      SKING

SQL> SELECT  *
  2    FROM  EMPLOYEE_FUNC('100,101')
  3  /


EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL
----------- -------------------- ------------------------- -------------------------
        101 Neena                Kochhar                   NKOCHHAR
        100 Steven               King                      SKING

SQL>

SY.

Hi, user551641
WHERE IN is a standard SQL clause ? 
That's right, IN is a standard SQL operator. When the right operand is a single value, as in
employee_id IN (101)
then it's equivalent to
employee_id = 101
'101,101' is a single VARCHAR2 value, not two NUMBER values.
I can not split the list of ids that I am looking up.
If you can't split the string (or, even better, collect the values in First Normal Form, e.g. a global temporary table) in the first place, then you can do something like this:

SELECT   employee_id, first_name, last_name, email
FROM	 hr_employees
WHERE	 INSTR  ( ',' || :p_list_of_ids        || ','
	 	, ',' || TO_CHAR (employee_id) || ','
		) > 0
;

or you could split the sting inside the function.

the parameter you pass in, is a single string. Just because your string contains commas doesn't mean that Oracle will magically know that it should be a list of values, and it's not just substituted into the query string as if it's a dynamically built SQL. As user_<whatever> says, it acts like a bind variable, so Oracle replaces the value in the string with the single value you pass it, as a whole.... it's expecting a set of numbers and you're giving it a string that isn't a number... simple as.