When you do not need to close a cursor in oracle?

Question

I am working in Oracle 10.2 with TOAD.

I have a script similar to this:

DECLARE

my_query VARCHAR2(500) := 'select C1, C2, C3 FROM  MYSCHEMA.MYTABLE';

    TYPE my_record_type IS RECORD
    (
           C1 MYTABLE.C1%TYPE,
           C2 MYTABLE.C2%TYPE,           
           C3 MYTABLE.C3%TYPE
    );

    TYPE my_table_type IS TABLE OF my_record_type;
    my_records my_table_type;

     my_cursor SYS_REFCURSOR;
BEGIN
     OPEN my_cursor FOR my_records;
        FETCH my_cursor
        BULK COLLECT INTO my_records
        LIMIT 1000;                           

        FOR indx IN 1 .. my_records.COUNT
        LOOP

            /* SOME CODE HERE*/
            IF /* CONDITION */ THEN
                EXIT;
            END IF; 
        END LOOP;
    CLOSE my_cursor;
END;

I have read that if you CLOSE a cursor that is already closed, oracle will raise the INVALID_CURSOR exception.

I have also read that when you use a CURSOR in a FOR LOOP oracle closes the cursor explicitly when the LOOP ends. So in theory I don't need to CLOSE the cursor as I am, and I should get an exception, which I am not.

Now I need to add a condition inside the LOOP that will cause it to EXIT the LOOP. I read that oracle implicitly closes CURSORS when you use EXIT inside a FOR LOOP or when an EXCEPTION is thrown.

So should I close the CURSOR explicitly? If I do so, and the CURSOR gets closed because I called EXIT, should it throw an Exception?

If I remove the explicit call that closes the cursor, will I get memory leaks?

What is the correct way to use the CURSOR in this case?

Answer 1

It seems that you're confusing cursors in for loops with cursor for loops. The former is what you illustrated. The latter looks like this:

DECLARE    
   cursor my_cursor is select C1, C2, C3 FROM  MYSCHEMA.MYTABLE;
BEGIN         
        FOR my_record in my_cursor LOOP    
            /* SOME CODE HERE*/
            IF /* CONDITION */ THEN
                EXIT;
            END IF; 
        END LOOP;
    CLOSE my_cursor;
END;

In this case, the close statement will throw an error because the cursor was implicitly opened by the for statement and implicitly closed when the loop concluded.

To summarize, if you have an open statement, you should also have a close statement. If you implicitly open a cursor, you can trust that it will be implicitly closed.

Answer 2

There's a simple rule - if you have to code an OPEN statement to open the cursor, you must code a matching CLOSE statement to close the cursor.

Thus if you have

DECLARE
  CURSOR some_cursor IS SELECT * FROM SOME_TABLE;
  some_table_row  SOME_TABLE%ROWTYPE;
BEGIN
  OPEN some_cursor;

  LOOP
    FETCH some_cursor INTO some_table_row;
    EXIT WHEN some_cursor%NOTFOUND;  -- this exit **WILL NOT** close some_cursor
  END LOOP;

then at some point later in your code you must have

  CLOSE some_cursor;
END;

If you use a cursor FOR-loop to open the cursor, e.g.

BEGIN
  FOR aRow IN some_cursor LOOP
    -- whatever

    IF aRow.SOME_THING = 'xyz' THEN
      EXIT;  -- this EXIT **will** close the cursor
    END IF;
  END LOOP;

or in the case where you don't even have an explicit cursor declaration:

FOR aRow IN (SELECT * FROM SOME_TABLE) LOOP
  -- blah, blah

  IF aRow.YADA_YADA = 'BADA BOOM!' THEN
    EXIT;  -- this EXIT **will** close the cursor
  END IF;
END LOOP;

then you don't need or want to write a CLOSE statement.

Share and enjoy.