| Cursor when not found. [message #369966] |
Tue, 21 November 2000 02:59  |
Kiki
Messages: 13
Registered: November 2000
|
Junior Member |
|
|
Does anyone help or tell me what is the problem I have here:
I have the cursor which is likely not to have any row at all.
and I used explicit cursor.
open cursor;
Loop
fetch cursor into v_cursor;
if cursor%NOTFOUND then
dbms_output.put_line('error');
exit;
else call procedure with parameter v_cursor
end if;
close cursor;
the result that I have is even though there is some rows, it still prints the message 'error' but also do run the procedure ok. funny, isn't it??
Thanks,
|
|
|
|
| Re: Cursor when not found. [message #369970 is a reply to message #369966] |
Tue, 21 November 2000 03:52  |
Babu Paul
Messages: 38
Registered: November 2000
|
Member |
|
|
Hi,
The answer to this is quite straight forward. When you open the cursor all the rows selected based on the select criteria resides in memory area. When you issue a fetch statement subsequent to the open statement, you retrieve the first row from the memory and the pointer moves down pointing to the second row. This is how it works.
The fetch statement is inside a loop. So it keeps fetching all rows failing the 'if' clause but until a point is reached where there are no further rows to be retrieved or fetched : at this point of time, the 'if' clause succeds, so it is obvious that your dbms_output statement is executed printing the error...
To avoid this 'if' clause to succeed you need to include a statement after fetch prior to 'if' an
EXIT WHEN cursor%NOTFOUND ;
and to check whether rows exists in the cursor...
you can make use of the rowcount built in before closing the cursor.
ie IF cursor%ROWCOUNT = 0 THEN
dbms_output.put_line('error');
END IF ;
CLOSE cursor ;
This is a better way of checking. I hope the code is executed in the right way...I don't see anything funny! It has done its job properly..
Good Luck!
Babu
To avoid
|
|
|
|
| Re: Cursor when not found. [message #369971 is a reply to message #369966] |
Tue, 21 November 2000 03:53 |
Babu Paul
Messages: 38
Registered: November 2000
|
Member |
|
|
Hi,
The answer to this is quite straight forward. When you open the cursor all the rows selected based on the select criteria resides in memory area. When you issue a fetch statement subsequent to the open statement, you retrieve the first row from the memory and the pointer moves down pointing to the second row. This is how it works.
The fetch statement is inside a loop. So it keeps fetching all rows failing the 'if' clause but until a point is reached where there are no further rows to be retrieved or fetched : at this point of time, the 'if' clause succeds, so it is obvious that your dbms_output statement is executed printing the error...
To avoid this 'if' clause to succeed you need to include a statement after fetch prior to 'if' an
EXIT WHEN cursor%NOTFOUND ;
and to check whether rows exists in the cursor...
you can make use of the rowcount built in before closing the cursor.
ie IF cursor%ROWCOUNT = 0 THEN
dbms_output.put_line('error');
END IF ;
CLOSE cursor ;
This is a better way of checking. I hope the code is executed in the right way...I don't see anything funny! It has done its job properly..
Good Luck!
Babu
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
