Re: Pl/Sql help...Immediate

The problem is that you need to check if your fetch has returned data. Oracle has created cursor variabled to do that. If you do a second fetch after one that did not return data, you will get a no_data_found exception. What I would do is add some lines:
12a EXIT WHEN c_test%NOTFOUND;
13a CLOSE c_test;
That way you will not get errors.

Jeroen van Veldhuizen
Developer

Jack Dawson wrote:

> Can someone help me out on this issue? I am trying hard since yesterday but
> cannot make out what the mistake is...
>
> The select statments in the code has data in it.
> Is everything ok on line 11 and line 12?
>
> 1 DECLARE
> 2 v_invoice1 varchar2(30);
> 3 v_amount_applied1 number;
> 4 v_amount_remaining number;
> 5 cursor c_test is
> 6 select INVOICE1, amount_applied1 from ar_payments_interface_all where
> record_type = 4;
> 7 begin
> 8 open c_test;
> 9 loop
> 10 fetch c_test into v_invoice1, v_amount_applied1;
> 11 select amount_due_remaining INTO v_amount_remaining from
> AR_PAYMENT_SCHEDULES_ALL
> 12 where trx_number = V_INVOICE1;
> 13 end loop;
> 14* end;
> SQL> /
> DECLARE
> *
> ERROR at line 1:
> ORA-01403: no data found
> ORA-06512: at line 11
>
> Appreciate your help
> Thanks
> Srikanth
Received on Sun Apr 22 2001 - 22:30:40 CEST