how to return ref cursor for more than one table?

sanoj_av · #1 (**permalink**) 02-09-10, 04:27

Hi

I am new to Oracle. I have a procedure getLoanInfo that returns a recordset as ref cursor. I just want to call it from the PL/SQL.
my doubt is regarding the declaration of the tblLoans%rowtype. Here tblLoans is one table but the result set returned by the proc contains a differrent data set. ie, it is the result set from more than one table.
So how do I specify it in the %rowtype. Or any other way. I tried it in the following way and I got an error

declare
type r_cursor is REF CURSOR;
c_var r_cursor;
res tblLoans%rowtype;
begin
pkgmain.getLoanInfo('AK','AK020',NULL,c_var);
loop
fetch c_var into res;
exit when c_var%notfound;
dbms_output.put_line(res.closingBal);
end loop;
close c_var;
end;

ORA-00932: inconsistent datatypes: expected - got -
ORA-06512: at line 8
00932. 00000 - "inconsistent datatypes: expected %s got %s"

LKBrwn_DBA · #2 (**permalink**) 02-09-10, 11:03

Assuming "res.closingbal" is always returned:

Code:

DECLARE
  TYPE r_cursor IS REF CURSOR;
  c_var  R_CURSOR;
BEGIN
  pkgmain.Getloaninfo('AK','AK020',NULL,c_var);
  
  FOR res IN c_var LOOP
    dbms_output.Put_line(res.closingbal);
  END LOOP;
END;
/

sanoj_av · #3 (**permalink**) 02-09-10, 23:43

But my question is how to define the Res.
In the example it is defined as res tblLoans%rowtype;
But it is throwing error as the result set is not in the same structure of tblLoans Table.
It is the combination of columns from five different tables by joining.

Any Idea?

anacedent · #4 (**permalink**) 02-10-10, 00:32

PL/SQL FAQ - Oracle FAQ

Code:

DECLARE
  v_emp emp%ROWTYPE;
BEGIN

In sample above, is EMP a Table or View?

Internet Services

Web Development

Digital Marketing

Consumer Tech