Confused about WHEN NO_DATA_FOUND vs. SQLROWCOUNT, etc.
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Confused about WHEN NO_DATA_FOUND vs. SQLROWCOUNT, etc.

  1. #1
    Join Date
    Jan 2001
    Posts
    63
    I am confused as to the proper way to get an accurate count of rows not found on a SELECT inside a cursor loop, and when to use SQLROWCOUNT, etc. In the code piece below, if I increment the 'not found' counter WHEN NO_DATA_FOUND, I get a very different count from when I say "NULL" at that point, and then increment the counter based on checking the INTO variable being null. What's the best way to do this in cursor loops? Thanks in advance....

    FOR sp_null_rec IN sp_null_cur
    LOOP
    BEGIN

    SELECT premise_id
    INTO v_premise_id
    FROM cisif_service_point
    WHERE sp_id = sp_null_rec.sp_id;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    NULL;
    /* v_counter_3 := v_counter_3 + 1; */

    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM || ' FOR ' || sp_null_rec.sp_id);
    END;

    EXIT WHEN sp_null_cur%NOTFOUND;

    IF v_premise_id is null THEN
    v_counter_3 := v_counter_3 + 1;
    END IF;
    END LOOP;

  2. #2
    Join Date
    Mar 2001
    Posts
    635
    Hi

    Here is an example where SQL%ROWCOUNT can be used

    SQL> declare
    2 a number(4);
    3 begin
    4 delete from emp;
    5 a := sql%rowcount;
    6 dbms_output.put_line(a);
    7* end;
    SQL> /
    14

    PL/SQL procedure successfully completed.

    this pl/sql block is returning 14 rows

    When no_data_found is an exception which will be raised when there in no matching record available in the tables


    PS : This is Just an example for understaning you can also implement depending on your requirement

    Regards
    Santosh

  3. #3
    Join Date
    Jan 2001
    Posts
    153
    Try this..

    FOR sp_null_rec IN sp_null_cur
    LOOP
    BEGIN

    SELECT premise_id
    INTO v_premise_id
    FROM cisif_service_point
    WHERE sp_id = sp_null_rec.sp_id;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    variablenotfound := variablenotfound + 1;
    NULL;
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM || ' FOR ' || sp_null_rec.sp_id);
    END;

    EXIT WHEN sp_null_cur%NOTFOUND;
    END LOOP;
    Vijay.s

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