OraFAQ Forum: SQL & PL/SQL » Returning a Ref Cursor

Home » SQL & PL/SQL » SQL & PL/SQL » Returning a Ref Cursor

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Returning a Ref Cursor [message #153779]

Wed, 04 January 2006 02:09

yraghavendra
Messages: 27
Registered: August 2005
Location: India

Junior Member

Hi,

How do i fetch the refcursor into a type.
I tried to fetch it but it gave me the following error.

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

CAN U TELL ME HOW DO I FETCH

SQL> create or replace type emp_ty as object(empno number,ename varchar2(20),sal number,deptno number);

SQL> create or replace type emp_nt as table of emp_ty;

SQL> create or replace type dept_ty as object(deptno number,dname varchar(20),emp emp_nt);

SQL> create or replace type dept_nt as table of dept_ty;

declare
type empreftyp is ref cursor;
empref1 empreftyp;
v_emp_nt emp_nt := emp_nt();
v_dept_nt dept_nt := dept_nt();
begin
open empref1 for select empno,ename,sal,deptno from emp;
fetch empref1 into v_emp_nt;
close empref1;
end;

Thanks and regards

Report message to a moderator

Re: Returning a Ref Cursor [message #153791 is a reply to message #153779]

Wed, 04 January 2006 03:26

mchadder
Messages: 224
Registered: May 2005
Location: UK

Senior Member

Hello.

Two things, firstly, your SQL statement needs to use the emp_ty
constructor, since you're fetching into a TABLE type every "row" of which is an instance of an OBJECT type (emp_ty). Secondly, don't forget that you're returning multiple rows here, so you need to
BULK COLLECT, i.e.

SQL> declare
  2    type empreftyp is ref cursor;
  3    empref1 empreftyp;
  4    v_emp_nt emp_nt := emp_nt();
  5  begin
  6    open empref1 for select emp_ty(empno,ename,sal,deptno) from emp;
  7    fetch empref1 bulk collect into v_emp_nt;
  8    close empref1;
  9  end;
 10  /

PL/SQL procedure successfully completed.

Rgds

Report message to a moderator

Re: Returning a Ref Cursor [message #154145 is a reply to message #153791]

Thu, 05 January 2006 22:49

yraghavendra
Messages: 27
Registered: August 2005
Location: India

Junior Member

Hi,

Thanks for ur solution.
By using Bulk Collect we can insert the data into collection in bulk.
But how can I insert the data one record at the time into the collection without using the bulk collect.
I tried as below and got the error.

declare
type empreftyp is ref cursor;
empref1 empreftyp;
deptref empreftyp;
v_emp_nt emp_nt := emp_nt();
v_dept1_nt dept1_nt := dept1_nt();
v_salgrade_nt salgrade_nt := salgrade_nt();
begin
open deptref for select salgrade_ty(grade,losal,hisal,v_emp_nt,v_dept1_nt) from salgrade;
--loop
fetch deptref bulk collect into v_salgrade_nt;
--exit when deptref%notfound;
dbms_output.put_line(v_salgrade_nt.count);
--end loop;
close deptref;
end;

PL/SQL procedure successfully completed.

BUT I NOW REMOVE THE BULK COLLECT and LOOP:

declare
type empreftyp is ref cursor;
empref1 empreftyp;
deptref empreftyp;
v_emp_nt emp_nt := emp_nt();
v_dept1_nt dept1_nt := dept1_nt();
v_salgrade_nt salgrade_nt := salgrade_nt();
begin
open deptref for select salgrade_ty(grade,losal,hisal,v_emp_nt,v_dept1_nt) from salgrade;
loop
fetch deptref into v_salgrade_nt;
exit when deptref%notfound;
dbms_output.put_line(v_salgrade_nt.count);
end loop;
close deptref;
end;

ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got SCOTT.SALGRADE_TY
ORA-06512: at line 11

Actually I want to insert 1 record into the collection for salgrade_ty from salgrade table keeping v_emp_nt and v_dept_nt as empty
and then i want to populate v_emp_nt and v_dept_nt from emp and dept table.

Regards,

Report message to a moderator