3

In Oracle 11g when PL/SQL context finishing function/procedure automatically close opened cursors. Why in many examples over the web users opening and closing their cursors?

Is this backward compatibility ?

What about REF-CURSORs ? Leaving procedure close them also ?

If cursor should be closed always what about handling exceptions? In EXCEPTION block do I have to check all cursors ISOPEN and then close them?

Basic script demonstrates auto-closing feature:

DECLARE
  PROCEDURE TEST IS
    CURSOR CUR_CLIENTS IS SELECT DUMMY CL_ID FROM DUAL;
    TYPE RT_CLIENTS IS TABLE OF CUR_CLIENTS%ROWTYPE;
    LT_CLIENTS RT_CLIENTS;
  BEGIN
    IF CUR_CLIENTS%ISOPEN THEN
      DBMS_OUTPUT.PUT_LINE('CLOSING CURSOR');
      CLOSE CUR_CLIENTS;
    END IF;
    OPEN CUR_CLIENTS;
    LOOP
      FETCH CUR_CLIENTS BULK COLLECT INTO LT_CLIENTS LIMIT 1000;
      EXIT WHEN LT_CLIENTS.COUNT = 0;
      FOR I IN 1..LT_CLIENTS.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(LT_CLIENTS(I).CL_ID);
      END LOOP;
    END LOOP;
  END TEST;
BEGIN
  DBMS_OUTPUT.PUT_LINE('--------------------');
  TEST;
  DBMS_OUTPUT.PUT_LINE('--------------------');
  TEST;
  DBMS_OUTPUT.PUT_LINE('--------------------');
  TEST;
  DBMS_OUTPUT.PUT_LINE('--------------------');
END;
Florin Ghita
  • 17,525
  • 6
  • 57
  • 76
WBAR
  • 4,924
  • 7
  • 47
  • 81
  • 1
    always close the cursor below the loop to avoid chance of going overuse of process, not that its gonna influence in the final result anyway...its just a good practice – Toping Jan 30 '13 at 13:19
  • @Ark `GOOD practice` advices is always welcome so +1 from me – WBAR Jan 30 '13 at 13:39
  • So you did the test, why not accept the result of auto-closing? – Plouf Jan 30 '13 at 17:18
  • [OPEN/FETCH/CLOSE is a bad idea](http://stackoverflow.com/a/13151348/409172) Just use implicit cursors; they run as fast or faster, and are easier to code and understand. – Jon Heller Feb 01 '13 at 05:14
  • 1
    @jonearles No, Many cases We have necessary to use Explicit cursor. This is nonsense answer. – MrYo Feb 02 '13 at 12:02
  • @Sanjay Yes, there are many cases where explicit cursors are necessary. But 99% of the time when I see OPEN/FETCH/CLOSE, like in this example, it is unnecessary. – Jon Heller Feb 02 '13 at 21:15
  • @jonearles yeah. You are right. – MrYo Feb 04 '13 at 05:22
  • @jonearles How do You want to use IMPLICIT curosrs in BULK COLLECT ? – WBAR Feb 13 '13 at 16:45
  • @WBAR: Implicit cursors automatically bulk collect. See my answer I linked to above for an example and a link to a whitepaper explaining it. You can't control the LIMIT, but in my experience using a custom LIMIT makes very little difference. – Jon Heller Feb 13 '13 at 17:04

1 Answers1

1

In your script, you are doing wrong because You are checking the cursor is opened or not, you are closing it if it is already opened, and again re-opened. If cursor is already opened then Do Not close it , just use it.

In plsql, cursors opened within the inner block have obviously not been implicitly closed. Had they been closed, I would not have exceeded the maximum number of open cursors.

In my experience:

SQL> DECLARE    
  2     CURSOR last99 IS SELECT * FROM dual;    
  3  BEGIN
  4    DECLARE    
  5       CURSOR test01 IS SELECT * FROM dual;
  6       CURSOR test02 IS SELECT * FROM dual;    
  7       CURSOR test03 IS SELECT * FROM dual;    
............................    
............................    
 51       CURSOR test47 IS SELECT * FROM dual;    
 52       CURSOR test48 IS SELECT * FROM dual;    
 53    BEGIN    
 54       OPEN test01;    
 55       OPEN test02;    
 56       OPEN test03;   

 ..............    
...............   

 99       OPEN test46;    
100       OPEN test47;  
101       OPEN test48;    
102    END;    
104    --This last OPEN will cause an error     
105    --from too many cursors.    
106    OPEN last99;    
107  END;    
108  /    
DECLARE    
*    
ERROR at line 1:    
ORA-01000: maximum open cursors exceeded    
ORA-06512: at line 2    
ORA-06512: at line 106

You can close cursor in Exception block also.

MrYo
  • 1,797
  • 3
  • 19
  • 33