I have procedure which has a user defined cursor. The cursors is opened and immediately after a delete is issued on a table that the query in the cursor is joining with.
Now all of a sudden the cursor has stop returning records in the fetch stage when the delete statement is present. However if the delete is removed then results are returned. This is also true if we have a COMMIT command prior to fetching the data.
My understating is that once a cursor is opened the data should be returned to maintain read consistency and it has for the last year.
-- Steps
1. Cursor opened
2. Rows deleted from a table that the query in the cursor is joining to
3. Rows in cursor fetched - Here we are now seeing 0 rows.
Anyone come across this or know how to resolve this?