If you want to send multiple records to an oracle function, a common practice is to create the function with one or more parameters as a table of an object type.

In the function itself, you can loop thru the array or select from it using to TABLE command.

Another option to send an array of multiple records is to use XML (xmltype) as the function parameter but this will be addressed in a separate post.

While I use this method for years, for some reason, many times I find myself struggling with the relatively simple syntax. This post will serve me (and maybe some of you) as a short, very simplistic template to copy from.

Basically, what you need to do is the create a user defined object type (UDT)

create or replace type order_type as object (
  num_of_units number
, cost_of_unit number)

Then you create a table of this user defined type

create or replace type order_tbl as  table of order_type

A simple function

create or replace function calc_total (i_orders order_tbl) return number is

res number;

begin


   select sum(num_of_units * cost_of_unit)
   into res
   from table (i_orders);

  

   return res;

end;

Testing this function with SQLPlus is a little challenging. The simple way to send a table of UDT type to a function is to cast it to the table of the user defined type.

SQL>   select  calc_total(
  2                      cast(MULTISET(
  3                        select * from
  4                        (
  5                        select 9 num_of_units, 2 cost_of_unit from dual union all
  6                        select 6, 3  from dual
  7                        ) ) as order_tbl   ))
  8    from dual;
 
CALC_TOTAL(CAST(MULTISET(SELEC
------------------------------
                            36

The reason for the additional inline view is the weird oracle bug the cause the function to hang (and sometimes crash with ora-03113 and ora-00600 ) when using cast and multiset together with union. The inline view is fast workaround for this.
Another option to work around this problem is to use multiset union

SQL>    select  calc_total( (cast(multiset(
  2                               select 9,2 from dual
  3                                MULTISET UNION
  4                               select 6,3 from dual
  5                           ) as order_tbl   ))
  6                       )
  7     from dual;
 
CALC_TOTAL((CAST(MULTISET(SELE
------------------------------
                            36