Oracle 11g: running time of cursors inside a procedure. Should I close open cursors?
In Oracle 11g, when a PL / SQL context termination function / procedure will automatically close open cursors. Why in many examples do Internet users open and close their cursors?
Is this backward compatible?
How about REF-CURSOR? Is their closure also completed?
If the cursor must always be closed, what about the exception handling? In a block, EXCEPTION
do I need to check all cursors ISOPEN
and then close them?
The main script demonstrates the auto close 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;
source to share
In your script you are wrong because you are checking if the cursor is open or not, you close it if it is already open and reopen it again. If the cursor is already open, Don't close it , just use it.
In plsql, cursors open inside an inner block were obviously not closed implicitly. If they were closed, I would not exceed 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 also close the cursor in the Exception block .
source to share