Cursor infinite loop

Question

This question is just out of curiosity. I am trying to find out what happens when no exit condition is specified for a cursor. What happens after last record is fetched. Will the cursor point to first record or point to blank memory area? For this I have created a test table with below structure.

create table CURSOR_TEST
  (col1 number,col2 NUMBER
  );

and below is my anonymous block without exit condition

declare
  cursor CUR is select EMPNO from EMP;
  v1 number;
begin
  open CUR;

  loop
    fetch CUR into V1;
    insert into CURSOR_TEST values(s.nextval,V1);
    commit;
  end loop;

  close CUR;
end;

As it is infinite loop, I need to break the operation in between. After this when I check records in the table I could see that after all rows of emp are fetched, the cursor points to first row again but fetching doesn't seem to happen after this. I could not understand why cursor is not able to fetch the second and subsequent rows during second iteration of the table.

In sql developer I cancelled the operation in task progress window — PTK
– PTK, Commented May 31, 2017 at 10:56

APC · Accepted Answer · 2017-05-31 11:24:25Z

3

"What happens after last record is fetched. Will the cursor point to first record or point to blank memory area?"

Neither. We need to distinguish between the cursor and the result set.

The cursor is just some in formation about a query. Once we've fetched all the records the cursor is "exhausted" but it doesn't change. It won't re-set itself and start again from scratch. There is no "second iteration of the table". Nor will it return null. It has a state, which we can test form e.g. CUR%NOTFOUND, and act on with an EXIT.

When we don't do that the fetch is executed but nothing is returned. The result set - in your case the variable V1 contains the last value fetched. So your table should have a lot of repeated values in col2.

" the cursor points to first row again but fetching doesn't seem to happen after this"

That seems unlikely. Bear in mind that your cursor doesn't have an ORDER BY clause, so the values of V1 could have a random sort order. The last EMPNO fetched could be the lowest value in the EMP table.

answered May 31, 2017 at 11:24

APC

147k20 gold badges180 silver badges289 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

PTK Over a year ago

thanks @APC .cursor is pointing to last record only after first table iteration.

Boneist · Accepted Answer · 2017-05-31 11:29:38Z

Your code will just keep attempting to fetch the next record and coming up with the cur%notfound condition, until you cancel the procedure call externally.

<cursor>%notfound isn't an exception in and of itself, since Oracle won't know when it's reached the end of a result set until it is informed that the next record isn't found. That's what you need to catch and then handle appropriately in order to stop looping.

You can see this by running the following:

DECLARE
  CURSOR cur IS SELECT dummy FROM dual;

  v_dummy VARCHAR2(1);
  v_nf_count INTEGER := 0;
  v_f_count INTEGER := 0;
BEGIN
  OPEN cur;
  LOOP
    FETCH cur INTO v_dummy;
    IF cur%NOTFOUND THEN
     v_nf_count := v_nf_count + 1;
     dbms_output.put_line('current %notfound count = '||v_nf_count||'; v_dummy = '||v_dummy);
    ELSIF cur%FOUND THEN
     v_f_count := v_f_count + 1;
     dbms_output.put_line('current %found count = '||v_f_count||'; v_dummy = '||v_dummy);
    END IF;

    EXIT WHEN v_f_count = 10 OR v_nf_count = 10;
  END LOOP;
END;
/

current %found count = 1; v_dummy = X
current %notfound count = 1; v_dummy = X
current %notfound count = 2; v_dummy = X
current %notfound count = 3; v_dummy = X
current %notfound count = 4; v_dummy = X
current %notfound count = 5; v_dummy = X
current %notfound count = 6; v_dummy = X
current %notfound count = 7; v_dummy = X
current %notfound count = 8; v_dummy = X
current %notfound count = 9; v_dummy = X
current %notfound count = 10; v_dummy = X

Collectives™ on Stack Overflow

Cursor infinite loop

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related