Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2007
    Posts
    11

    Unanswered: how to return ref cursor for more than one table?

    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"

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool OK, try this.

    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;
    /
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Dec 2007
    Posts
    11
    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?

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    PL/SQL FAQ - Oracle FAQ

    Code:
    DECLARE
      v_emp emp%ROWTYPE;
    BEGIN
    In sample above, is EMP a Table or View?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •