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. <\/p>\n
In the function itself, you can loop thru the array or select from it using to TABLE command. <\/p>\n
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.<\/p>\n
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. <\/p>\n
Basically, what you need to do is the create a user defined object type (UDT) <\/p>\n
\r\ncreate or replace type order_type as object (\r\n num_of_units number\r\n, cost_of_unit number)\r\n<\/pre>\nThen you create a table of this user defined type<\/p>\n
\r\ncreate or replace type order_tbl as table of order_type\r\n<\/pre>\nA simple function<\/p>\n
\r\ncreate or replace function calc_total (i_orders order_tbl) return number is\r\n\r\nres number;\r\n\r\nbegin\r\n\r\n\r\n select sum(num_of_units * cost_of_unit)\r\n into res\r\n from table (i_orders);\r\n\r\n \r\n\r\n return res;\r\n\r\nend;\r\n<\/pre>\nTesting 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.<\/p>\n
\r\nSQL> select calc_total(\r\n 2 cast(MULTISET(\r\n 3 select * from\r\n 4 (\r\n 5 select 9 num_of_units, 2 cost_of_unit from dual union all\r\n 6 select 6, 3 from dual\r\n 7 ) ) as order_tbl ))\r\n 8 from dual;\r\n \r\nCALC_TOTAL(CAST(MULTISET(SELEC\r\n------------------------------\r\n 36\r\n\r\n<\/pre>\n