Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Fetch from cursor when no records returned

448225Mar 5 2010 — edited Mar 5 2010
Hi,

I've got the following question / problem?
When I do a fetch from a cursor in my for loop and the cursor returns no record my variable 'r_item' keeps the value of the previous fetched record. Shouldn't it contain null if no record is found and I do a fetch after I closed and opend the cursor? Is there a way the clear the variable before each fetch?

Below you find an example code
 
CURSOR c_item (itm_id NUMBER) IS
SELECT DISTINCT col1 from table1
WHERE id = itm_id;

r_item  c_item%ROWTYPE;
...

FOR r_get_items IN c_get_items LOOP
  IF r_get_items.ENABLE = 'N' THEN	   
      
      open c_item(r_get_items.ITMID);
      fetch c_item into r_item;
      close c_item;

      IF  r_item.ACCES = 'E' then
           action1
      ELSE   	 	    
           action2
      END IF;

  END IF; 
END LOOP;  
Thanx
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 2 2010
Added on Mar 5 2010
7 comments
3,723 views