Using of cursors as function output in procedure (Oracle)

Question

I'm trying to find a solution but all the time something is wrong. So what is my problem:

I have a function:

function fun1 (
p_param1 number) return sys_refcursor
is 
  c_result sys_refcursor;
begin
  open c_result for select e.username, c.contract_id from employees e 
    join contracts c on c.employee_id = e.employee_id;
  return c_result;
end fun1;

I want to use this function inside my stored procedure:

procedure proc1 (...) 
is ...
cur_contract sys_refcursor;
begin
...
  open cur_contract for fun1(p_param1);
  loop
    fetch cur_contract into v_username, v_contract_id;
    exit when cur_contract%notfound;
    ...
  end loop;
  close cur_contract;
...
end proc1;

And I get error: expression is of wrong type in line "open cur_contract for fun1(p_param1);"

What should I change to make my procedures work?

Answer 1

You've already opened the cursor in fun1. Try the following:

procedure proc1 (...)
  is
  ...
  cur_contract sys_refcursor;
begin
  ...
 cur_contract := fun1(p_param1);

 loop
   fetch cur_contract into v_username, v_contract_id;
   exit when cur_contract%notfound;
   ...
 end loop;

 close cur_contract;
 ...
end proc1; 

I hope this helps.