How to write a PL/SQL function have parameter similar to greatest function

Question

In Oracle/PLSQL, the greatest function returns the greatest value in a list of expressions. The syntax for greatest function is

greatest( expr1, expr2, ... expr_n )). 

How can I write my function with unlimit parameter like this:

myfunction(param1 , param2,...param_n)

Answer 1

You could simulate var args using a table type as the parameter.

create or replace type VARGS as table of varchar2(32767);

You can then use this type as the last parameter of the function:

CREATE OR REPLACE Function FNC_COUNT_WITH_NAMES
   ( P_NAMES IN VARGS )
   RETURN number
IS
RT_COUNT NUMBER;
BEGIN
  select count(*) INTO rt_count from employees where name IN 
    ( 
       select * from TABLE(p_names))
    );
   return rt_count;
END;

Client code would call it with:

exec FNC_COUNT_WITH_NAMES (vargs('Brian','Mike','John','David', 'Bob'));

or

select FNC_COUNT_WITH_NAMES (vargs('Brian','Mike','John','David', 'Bob')) from dual;