-
Hi,
I have a small procedure, which updates the credit ratings to the account table. There are no records in the account table but there are few records in the credit rate table.As such the records should not get updated in 'Account' Table.
Should It give a NO_DATA_FOUND OR Not ?
PLEASE CLARIFY.
CREATE OR REPLACE procedure UPD_CRD_RATE
is
C_SYS_ACCOUNT_NUM STG_CREDIT_RATING.SYS_ACCOUNT_NUM%TYPE;
C_MMAS_PROSPECT_ID STG_CREDIT_RATING.MMAS_PROSPECT_ID%TYPE;
C_CREDIT_SCORE STG_CREDIT_RATING.CREDIT_SCORE%TYPE;
C_CREDIT_CHK_DT STG_CREDIT_RATING.CREDIT_CHK_DT%TYPE;
tot_rec integer :=0 ;
num_rec_ld integer :=0 ;
num_NOT_UPD integer :=0 ;
cursor c1 is
select SYS_ACCOUNT_NUM,
MMAS_PROSPECT_ID,
CREDIT_SCORE,
CREDIT_CHK_DT
from stg_CREDIT_RATING;
begin
dbms_output.put_line('Updating Credit Ratings: .....!!');
open c1;
loop
fetch c1 into C_SYS_ACCOUNT_NUM,
C_MMAS_PROSPECT_ID,
C_CREDIT_SCORE,
C_CREDIT_CHK_DT;
exit when c1%notfound;
tot_rec := tot_rec + 1;
begin
num_rec_ld := num_rec_ld + 1;
UPDATE ACCOUNT
SET CREDIT_SCORE = C_CREDIT_SCORE,
credit_check_date = To_date(c_credit_chk_dt,'DD-MON-YY')
WHERE SYSTEM_ACCOUNT_NUM = C_SYS_ACCOUNT_NUM;
Exception
WHEN NO_DATA_FOUND THEN
NUM_NOT_UPD :=NUM_NOT_UPD + 1;
NUM_REC_LD := NUM_REC_LD -1 ;
dbms_output.put_line('Rec not found ' ||c_sys_account_num);
When others then
DBMS_OUTPUT.PUT_LINE('CHECK THE ERROR '||SQLERRM);
end;
end loop;
close c1;
dbms_output.put_line('Updating complete');
dbms_output.put_line('Update Summary');
dbms_output.put_line('Total Rec' ||tot_rec);
dbms_output.put_line('Updated' ||num_rec_ld);
dbms_output.put_line('Not Updated' || num_NOT_UPD);
end;
/
Badrinath
-
NO_DATA_FOUND exception is not raised.
If there are no rows in the table STG_CREDIT_RATING, you directly exit from the loop.
The UPDATE statement never raises a NO_DATA_FOUND exception. If no rows satisfy the WHERE condition of the UPDATE statement, no rows are updated, there is no exception to be raised.
-
As akkerend allready explained, NO_DATA_FOUND (as well as TOO_MANY_ROWS) are only applicable to SELECTs.
If your intention with this exception was merely to correctly display the procedure's updating summary, you should use SQL%ROWCOUNT (and maybe SQL%FOUND, SQL%NOTFOUND) immediatelly after your UPDATE clause to properly advance your update counters.
HTH,
