Oracle PL/SQL - procedure with array parameter

Question

I need to write an oracle procedure which will have an array of ID's as parameter. Then I will return a cursor which contains result of select(1).

(1) - select * from table where id in(ID's)

As an option we can pass a string param and then convert string to array.

DECLARE
info sys_refcursor ;  
error varchar(255); 
BEGIN
package.test_function('1,2,3',info ,error);// info will contain a result cursor for select(1)

END;

Do you have other ideas?

Answer 1

You can create a user-defined collection type:

CREATE TYPE int8_list IS TABLE OF NUMBER(8,0);

Then your package:

CREATE PACKAGE pkg_name AS
  PROCEDURE proc_name (
    i_ids    IN  int8_list,
    o_cursor OUT SYS_REFCURSOR
  );
END;
/

CREATE PACKAGE BODY pkg_name AS
  PROCEDURE proc_name (
    i_ids    IN  int8_list,
    o_cursor OUT SYS_REFCURSOR
  )
  IS
  BEGIN
    OPEN o_cursor FOR
      SELECT * FROM table_name WHERE id MEMBER OF i_ids;
  END;
END;
/

Then you can call the procedure:

DECLARE
  v_info  sys_refcursor ;  
  v_id    TABLE_NAME.ID%TYPE;
  v_value TABLE_NAME.VALUE%TYPE;
BEGIN
  pkg_name.proc_name(int8_list(1,2,3), v_info);
  LOOP
    FETCH v_info INTO v_id, v_value;
    EXIT WHEN v_info%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_id || ' ' || v_value);
  END LOOP;
END;
/

Which, for the sample data:

CREATE TABLE table_name (id, value) AS
SELECT LEVEL, CHR(64+LEVEL) FROM DUAL CONNECT BY LEVEL <= 5;

Outputs:

1 A
2 B
3 C

db<>fiddle here