Unable to use Joins in Sys RefCursor in Oracle

Question

I want to pass sys_refcursor as an argument to a procedure in PL/SQL. I have used the following codes to create a procedure

create or replace procedure reffunmani(
  cname varchar2,
  mysys out sys_refcursor) 
is
begin   
  open mysys for
    select /*c.ins_id,c.cname, c.start_date,*/i.ins_id,i.ins_name
      from course c,institution i where c.ins_id=i.ins_id 
     order by c.start_date;
end;
/
show errors;

and i have called the same procedure i an anonymous block

declare
  mysys sys_refcursor;
  rec institution%rowtype; 
begin
  reffunmani('MCA',mysys);
  loop
    fetch mysys into rec;
    exit when mysys%notfound;
    dbms_output.put_line(rec.ins_id||'    '||rec.ins_name);
  end loop;
  close mysys;
end;
/

When I execute my anonymous block, I get an error

ORA-06504: PL/SQL: Return types of Result Set variables or query do not match 
ORA-06512: at line 7

Note that the institution table has 5 columns.

Answer 1

You are fetching the data from the cursor into a local variable rec. That record is defined to be of type institution%rowtype. That would work if and only if the cursor actually returned all the columns from the institution table (in the same order as they are defined in the table). Since that is not the case here, you have a couple of options.

First, you can simply define a few scalar variables and fetch the data into those

declare
  mysys sys_refcursor;
  l_ins_id   institution.ins_id%type;
  l_ins_name institution.ins_name%type;
begin
  reffunmani('MCA',mysys);
  loop
    fetch mysys into l_ins_id, l_ins_name;
    exit when mysys%notfound;
    dbms_output.put_line(l_ins_id||'    '||l_ins_name);
  end loop;
  close mysys;
end;
/

Alternately, you can declare a local record type that has two fields and fetch data into that

declare
  mysys sys_refcursor;

  -- I'm guessing at your data types here
  type typ_my_rec is record (
    ins_id    integer,
    ins_name  varchar2(100)
  );
  rec typ_my_rec; 
begin
  reffunmani('MCA',mysys);
  loop
    fetch mysys into rec;
    exit when mysys%notfound;
    dbms_output.put_line(rec.ins_id||'    '||rec.ins_name);
  end loop;
  close mysys;
end;
/