-
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;
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|