Looping Through Sys_Refcursor: Type Mismatch Found Between Cursor and INTO Varaiables

Question

I have a function that returns a Sys_Refcursor. It looks like this:

    Open l_cursor For
      Select b.balance + b.arrears arrears_bucket
            ,b.levy + b.penalty levy_bucket
            ,b.supplementary_levy supp_bucket
            ,b.other_balance + b.other_penalty other_bucket
            ,b.arrears_balance + b.arrears_penalty + b.levy_balance + b.levy_penalty calculated_balance
        From balances b
       Where b.id = p_id;
    Return l_cursor;

I have another function in which I want to call the above function, and loop through it. It looks like this:

    Cursor l_cursor Is
      Select balance_sel(p_id) From dual;
    l_result1 Number;
    l_result2 Number;
    l_result3 Number;
    l_result4 Number;
    l_result5 Number;

    Begin
      Loop
      Fetch l_cursor into l_result1, l_result2, l_result3, l_result4, l_result5;
      EXIT  WHEN l_cursor%notfound;
      End Loop;

But I keep getting the error:

Error: PLS-00386: type mismatch found at 'L_RESULT1' between FETCH cursor and INTO variables
Line: 316
Text: Fetch l_cursor into l_result1, l_result2, l_result3, l_result4, l_result5;

I'm fetching numbers into numbers, so what it is the mismatch? How do I resolve it? This seems like it should be simple but I am at a loss.

Thanks.

Answer 1

I think your problem is with the use of the cursor.

declare a variable of the type sys_refcursor and save the result of your function

declare
  l_cursor Sys_Refcursor;

  l_result1 Number;
  l_result2 Number;
  l_result3 Number;
  l_result4 Number;
  l_result5 Number;

Begin
...
  l_cursor := balance_sel(p_id);
  Loop
    Fetch l_cursor into l_result1, l_result2, l_result3, l_result4, l_result5;
    EXIT  WHEN l_cursor%notfound;
  End Loop;

end;
/