Can someone point me in the right direction. I am opening a cursor called ICC_CUR and if I DON'T find a record, it should insert else if I do find a record, then update.
However, I am getting a ORA-01403: no data found. error at the FETCH statement. I would expect it to test the IF ICC_CUR%FOUND statement and not go to the EXCEPTION handler:
declare
cursor icc_cur is
select icc_previous_forecast, icc_forecast, icc_part_year
from icap_changes
where icc_css_acct_no = lclCSSAcctNo
and icc_lse = lclLse
and icc_part_year = lclPartYear
and icc_status = 'N'
for update;
icc_rec icc_cur%rowtype;
begin
---The values lclCSSAcctNo, lclLse, lclPartYear, etc are set here before we OPEN the CURSOR
open icc_cur;
fetch icc_cur into icc_rec; <-- it is failing here with a RA-01403: no data found.
if icc_cur%found then <-- I would expect this statement to test if NO DATA FOUND or DATA FOUND…. Not go to the EXCEPTION
--- if record IS FOUND, then update current record and update values…
if icc_rec.icc_part_year != lclPartYear then
Update icap_changes
set icc_previous_forecast = nvl(lclIcapCurrent,0), icc_forecast = lclForecast, icc_part_year = icc_rec.icc_part_year
where current of icc_cur;
else
Update icap_changes
set icc_previous_forecast = nvl(lclIcapCurrent,0), icc_forecast = lclForecast
where current of icc_cur;
end if;
else
--- if no record found on ICAP_CHANGES table, to insert the record…
insert into icap_changes
(icc_icap_changes_no, icc_css_acct_no, icc_lse, icc_part_year,
icc_trip_no, icc_forecast, icc_previous_forecast, icc_eff_date, icc_exp_date, icc_refresh_dt)
values(ICC_ICAP_CHANGES_NO_SEQ.nextval, lclCssAcctNo, lclLse, lclPartYear,
lclTripNo, lclForecast, nvl(lclIcapCurrent,0), lclEffDt, lclExpDt, trunc(sysdate));
end if;
close icc_cur;
exception
when others then
sp_file_io.write_line(BadFilePointer, lclLine ||
',Record=' || lclRecordCount || ' ' || sqlerrm);
end;