Fetch multiple rows with PL/SQL procedure

Question

Procedure:

create or replace PROCEDURE employee_project1 (
  emp_id   in  EMPLOYEES.EMPLOYEE_ID%TYPE,
  cur      out SYS_REFCURSOR
)
IS
BEGIN
  OPEN cur FOR
    SELECT p.project_id,
           p.project_name
    FROM   employees e
           INNER JOIN departments d
           ON ( e.department_id = d.department_id )
           INNER JOIN projects p
           ON ( p.department_id = e.department_id)
    WHERE  e.employee_id = emp_id;
END;

This procedure works for me. I am executing the procedure with below code:

Execution:

declare
    cur SYS_REFCURSOR;
    pro_id int;
    pro_name projects.project_name%TYPE;
begin
    employee_project1(43, cur);
    loop
        fetch cur into pro_id, pro_name;
        exit when cur%NOTFOUND;
        dbms_output.put_line(pro_id||' '||pro_name);
    end loop;
end;

I have to call the procedure in java with JDBC. So how can I call the procedure in java by above execution code?

Perhaps you need to use a [PL/SQL collection](https://blogs.oracle.com/oraclemagazine/working-with-collections) ? — Abra, Apr 18 '20 at 12:53

MT0 · Accepted Answer · 2020-04-18T19:39:49.100

Use a collection (which you can create using CREATE TYPE ... AS TABLE OF ...) or VARRAY (like the built-in VARRAYs SYS.ODCI*LIST) and BULK COLLECT INTO:

CREATE PROCEDURE employee_project (
  emp_id    in  EMPLOYEES.EMPLOYEE_ID%TYPE,
  pro_ids   out SYS.ODCINUMBERLIST,
  pro_names out SYS.ODCIVARCHAR2LIST
)
IS
BEGIN
  SELECT p.project_id,
         p.project_name
  BULK COLLECT INTO
         pro_ids,
         pro_names
  FROM   employees e
         INNER JOIN departments d
         ON ( e.department_id= d.department_id )
         INNER JOIN projects p
         ON ( p.department_id = d.department_id)
  WHERE  e.employee_id = emp_id;
END;
/

Or just return the cursor (without an INTO clause):

CREATE PROCEDURE employee_project (
  emp_id   in  EMPLOYEES.EMPLOYEE_ID%TYPE,
  cur      out SYS_REFCURSOR
)
IS
BEGIN
  OPEN cur FOR
    SELECT p.project_id,
           p.project_name
    FROM   employees e
           INNER JOIN departments d
           ON ( e.department_id= d.department_id )
           INNER JOIN projects p
           ON ( p.department_id = d.department_id)
    WHERE  e.employee_id = emp_id;
END;
/

2nd one procedure works for me but I am executing in in java. So how can I call that procedure in java with aboce execution code? _(edited the question)_ @MT0 — Darshak Chavda, Apr 18 '20 at 17:42
@DarshakChavda There are many answers ([like this one](https://stackoverflow.com/a/37471729/1509264)) on how to return a cursor from a procedure using JDBC. And there are other answers ([like this one](https://stackoverflow.com/questions/47707903/how-do-i-get-a-stored-procedure-output-parameter-that-is-an-array-to-work/47711778#47711778)) on how to pass arrays to/from a procedure using JDBC. I'm sure you can find more by searching. — MT0, Apr 18 '20 at 19:54

Fetch multiple rows with PL/SQL procedure

1 Answers1