DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Exceptions Not working?

  1. #1
    Join Date
    Jan 2001
    Posts
    642
    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

  2. #2
    Join Date
    Jul 2000
    Posts
    296
    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.

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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,
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

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


Click Here to Expand Forum to Full Width