How to use pass an array in PL/SQL function

Question

I am a Java developer with limited knowledge of Oracle PL/SQL. Please let me know how to pass an array to a PL/SQL function in the following example and how to invoke 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;

In the example you give, you're passing in an id and getting back a comma delimited string. What is it you're trying to do? — eaolson
– eaolson, Commented Jun 14, 2011 at 3:36
Instead of passing the id, i want to pass a array of ids, return type should also be an array of Strings. — micheal marquiz
– micheal marquiz, Commented Jun 14, 2011 at 18:06

eaolson · Accepted Answer · 2011-06-14 23:27:38Z

16

You can create a collection type and pass the parameter as an instance of that 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.

answered Jun 14, 2011 at 23:27

eaolson

15.2k7 gold badges46 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

micheal marquiz Over a year ago

Thanks eaolson, As a follow up can you please let me know how to return a array.

Erick Stone Over a year ago

@michealmarquiz the same as above. Instantiate an array. Return the array instead of returning txt.

Collectives™ on Stack Overflow

How to use pass an array in PL/SQL function

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related