How to use pass array in PL / SQL functions

Question

I am a Java developer with limited knowledge of Oracle PL / SQL. Please let me know how to pass an array to the PL / SQL function in the following example and how to call it.

CREATE OR REPLACE FUNCTION get_employees (pUserId NUMBER) RETURN VARCHAR2 IS l_text VARCHAR2(32767) := NULL; BEGIN FOR cur_rec IN (SELECT grp.NAME GROUP_NAME FROM UserGroupRole ugr, Group_ grp WHERE ugr.groupid=grp.groupid and USERID = pUserId) LOOP l_text := l_text || ',' || cur_rec.GROUP_NAME; END LOOP; RETURN LTRIM(l_text, ','); END; / SELECT get_employees(414091) FROM DUAL; 

Answer 1

You can create a collection type and pass the parameter as an instance of this type.

 SQL> create type num_array as table of number; 2 / Type created. SQL> create or replace function myfun ( arr_in num_array ) return varchar2 is 2 txt varchar2(1000); 3 begin 4 for i in 1..arr_in.count loop 5 txt := txt || to_char( arr_in(i) ) || ','; 6 end loop; 7 return txt; 8 end; 9 / Function created. SQL> declare 2 myarray num_array; 3 mytext varchar2(1000); 4 begin 5 myarray := num_array(); 6 myarray.extend(3); 7 myarray(1) := 1; 8 myarray(2) := 5; 9 myarray(3) := 9; 10 dbms_output.put_line( myfun( myarray )); 11 end; 12 / 1,5,9, PL/SQL procedure successfully completed.