Since writing this article, I've learned a few things relative to this topic. Please read the following follow-up article: More on PL/SQL's Cursor Handling
I also participated in a long discussion with Steven Feuerstein, Bill Pribyl, and Bryn Llewellyn (the PL/SQL product manager). Read about that in the conclusion to this whole saga: PL/SQL Cursor Handling Explained.
Why is all this important? Read On the Importance of Mental Models for the answer to that question.
Recently, a reader emailed me with an intriguing question regarding PL/SQL's handling of cursors. Mike's question boils down to this: if you explicitly open a cursor and then forget to close it before exiting the PL/SQL block, will PL/SQL implicitly close the cursor for you? Mike had done his homework on this question, as you can see in his note below:
I've written you in the past and enjoyed, in my opinion, an unbelievable willingness to respond and wanted to pass another question by you.
I've been challenged by a co-worker regarding the persistance of explicit cursors (not in a package) in a pl/sql block. I've been told by my Oracle contacts including David Jacob-Daub a senior principal instructor and Scott Gossatt who is probably the most knowledgable guru I've personally met (who teaches the internals classes) that explicitly opened cursors are open, in memory until they are either explicitly closed or until the end of the session that opened the cursor. Although that is the theory I espouse, I've also received responses from Steve Feuerstein who tells me that explicitly opened cursors are "closed" at the termination of the block in which they were defined. To his credit, he also quickly adds that while Oracle implicitly closes cursors at the end of the block which defined them, the developer should make it a practice to close the cursors he opens.
I've noticed on the RevealNet pipelines rather spirited debates surrounding this issue. I've attempted to uncover documentation that verifies one or the other theory and even attempted, as per Scott Gossatt's recommendation to do a process state dump to verify that the cursor was still open even after the block terminates but wasn't positive how to interrpret the results. I've also tried to determine the number of opened cursors using some of the data dictionary views with no luck.
I asked Steve F. if he could recommend a test to prove his theory and he said it was out of his area of expertise and recommended posting the question on a couple of web sites, which I did but again, NO TAKERS! I've asked others for help with no response.
Do you have an opinion on this issue or better yet can you point me to a fool-proof test so that I can put this issue to rest once and for all?
Your input will be greatly appreciated.
Also, I still enjoy your aticles in the Oracle Magazine and look forward to your work on SQL*Loader.
Looking forward to hearing from you.
Fairview Heights, IL
This struck me as an interesting question, and after letting it percolate in the back of my mind for a couple weeks, I set out to do some testing to see if I could determine for certain what PL/SQL does. Before I get into my test results, let me share some of the thoughts that were going through my mind.
What Should Logically Happen?
Whenever I'm faced with a question like the one Mike asked, I ask myself at least two questions:
How do I believe PL/SQL should behave? In other words, under ideal circumstances, how would I have designed the language?
- Given all the things I currently know about Oracle, PL/SQL, other languages, etc., how do I believe PL/SQLmight really behave.
These two questions sound almost the same, but the answers in this case are diametrically opposed. Let's attack the first question of how I think PL/SQL should behave. First off, I believe that leaving a cursor open when a PL/SQL block ends is tantamount to a memory leak. That got me to thinking about how different languages manage memory. Most languages allow you to declare variables that are local to a function or procedure. Examples are Pascal, C, and C++. When a function or procedure ends, such variables are destroyed and their memory reclaimed. PL/SQL exhibits this behavior with respect to variables that you declare in a PL/SQL block. Languages such as C and C++ also allow you to dynamically allocate memory via the newoperator or a call to malloc(). Such memory remains allocated until you free it using delete or free(), and the failure to deallocate dynamic memory is a frequent cause of memory leaks. PL/SQL has no mechanism that you can use to allocate memory dynamically, thus, from a certain point of view, it follows that since you cannot dynamically allocate memory that you should not be responsible for freeing dynamically allocated memory (such as might be allocated when you open a cursor).
Ok, enough for the question of how I would have designed PL/SQL, let's attack the second question: how do I think PL/SQL might really behave with respect to the issue of unclosed cursors? Getting back to languages such as Pascal, C, and C++, I thought about how such languages manage local variables. Way back in the day I used to do a fair amount of assembler programming, and occasionally I would debug Pascal programs at the machine-language level. One mechanism I've seen used for local variables involves the following process:
Your code invokes a function.
The stack pointer is saved, and space is allocated on the stack for the function's local variables.
Your function does its thing.
The end of the function is reached.
The stack pointer is restored, thus freeing all space allocated to local variables in step 2.
I believe PL/SQL uses a process such as this to manage variables local to a PL/SQL block. The problem is that a cursor is more than just a variable. A cursor is likely some sort of structure, and it wouldn't surprise me if that structure contained one or more pointers to memory that is dynamically allocated for you when you open the cursor. The question now is, when you reach the end of a PL/SQL block, is PL/SQL "smart" enough to close the cursor and deallocate its memory, or does PL/SQL just dump the cursor's structure, including the pointers, leaving the dynamically allocated memory still allocated, and the cursor still open?
The only way to find out for sure is to test.
On the Reliability of Undocumented Behavior
Mike stated in his question that PL/SQL's behavior with respect to the issue at hand is not documented in the Oracle manuals. I take him at his word for that. He's obviously looked long and hard for the answer, and if he says it's not there, then it's not there. We can test PL/SQL's behavior, and I did, but since the behavior is undocumented, we need to be very careful in how we interpret the results. It's one thing to test and confirm documented behavior. It's quite another to test and confirm undocumented behavior. Consider the following:
If you test and confirm documented behavior, you know PL/SQL is behaving as it should. You can then rely on that behavior when you write code.
If you test and find behavior that doesn't match the documentation, then you've found a bug. You may then need to develop a work around. You can also hope that the bug gets fixed.
When you test and discover undocumented behavior, what can you do? You certainly can't rely on that behavior when you write code! You can't even rely on the behavior to remain consistent from one release to the next. You likely haven't found a bug either, because, by definition, a bug is behavior that doesn't match documentation.
In this case, it's useful to be aware of the open cursor issue, because that awareness will encourage you to be more alert for bugs in your own programs that result from inadvertently leaving cursors open. Hopefully you won't make the mistake to begin with, but if you do make the mistake, your knowledge of PL/SQL's undocumented behavior will enable you to discover your mistake more quickly than would otherwise be the case.
The path to an answer to Mike's question seemed to lie in some testing. At first I thought I'd need to write a loop to somehow dynamically create and open cursors until I either ran out of memory or convinced myself that PL/SQL was implicitly closing them. But then I remembered that Oracle's OPEN_CURSORS initialization parameter places an upper limit on the number of open cursors that any given session can have. If PL/SQL wasn't implicitly closing cursors at the end of a block, I could easily reach that limit and my code would error out.
By the way, the testing I did for this article I did using Oracle9i Database. The specific version information is as follows:
SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle9i Enterprise Edition Release 184.108.40.206.0 - Production PL/SQL Release 220.127.116.11.0 - Production CORE 18.104.22.168.0 Production TNS for Linux: Version 22.214.171.124.0 - Production NLSRTL Version 126.96.36.199.0 - Production
I began my testing by editing my parameter file and changing the value for my OPEN_CURSORS initialization parameter to 50. Then I bounced my test database, being sure to use the PFILE option on restart to point to my modified parameter file. (By default, Oracle9i reads parameters from a binary SPFILE). A simple query to V$PARAMETER while logged on as SYSTEM confirm my new open cursor limit:
SQL> SELECT VALUE FROM v$parameter 2* WHERE name='open_cursors' SQL> /
VALUE --------------------------------------- 50
I then executed a PL/SQL block that contained a nested PL/SQL block. The nested block opened 48 cursors, and left them opened when the block ended. The outer block then opened one last cursor. Scroll down to see whether that caused an error.
Note: I hod to experiment a bit to determine just the right number of cursrs to open.
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; 8 CURSOR test04 IS SELECT * FROM dual; 9 CURSOR test05 IS SELECT * FROM dual; 10 CURSOR test06 IS SELECT * FROM dual; 11 CURSOR test07 IS SELECT * FROM dual; 12 CURSOR test08 IS SELECT * FROM dual; 13 CURSOR test09 IS SELECT * FROM dual; 14 CURSOR test10 IS SELECT * FROM dual; 15 CURSOR test11 IS SELECT * FROM dual; 16 CURSOR test12 IS SELECT * FROM dual; 17 CURSOR test13 IS SELECT * FROM dual; 18 CURSOR test14 IS SELECT * FROM dual; 19 CURSOR test15 IS SELECT * FROM dual; 20 CURSOR test16 IS SELECT * FROM dual; 21 CURSOR test17 IS SELECT * FROM dual; 22 CURSOR test18 IS SELECT * FROM dual; 23 CURSOR test19 IS SELECT * FROM dual; 24 CURSOR test20 IS SELECT * FROM dual; 25 CURSOR test21 IS SELECT * FROM dual; 26 CURSOR test22 IS SELECT * FROM dual; 27 CURSOR test23 IS SELECT * FROM dual; 28 CURSOR test24 IS SELECT * FROM dual; 29 CURSOR test25 IS SELECT * FROM dual; 30 CURSOR test26 IS SELECT * FROM dual; 31 CURSOR test27 IS SELECT * FROM dual; 32 CURSOR test28 IS SELECT * FROM dual; 33 CURSOR test29 IS SELECT * FROM dual; 34 CURSOR test30 IS SELECT * FROM dual; 35 CURSOR test31 IS SELECT * FROM dual; 36 CURSOR test32 IS SELECT * FROM dual; 37 CURSOR test33 IS SELECT * FROM dual; 38 CURSOR test34 IS SELECT * FROM dual; 39 CURSOR test35 IS SELECT * FROM dual; 40 CURSOR test36 IS SELECT * FROM dual; 41 CURSOR test37 IS SELECT * FROM dual; 42 CURSOR test38 IS SELECT * FROM dual; 43 CURSOR test39 IS SELECT * FROM dual; 44 CURSOR test40 IS SELECT * FROM dual; 45 CURSOR test41 IS SELECT * FROM dual; 46 CURSOR test42 IS SELECT * FROM dual; 47 CURSOR test43 IS SELECT * FROM dual; 48 CURSOR test44 IS SELECT * FROM dual; 49 CURSOR test45 IS SELECT * FROM dual; 50 CURSOR test46 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; 57 OPEN test04; 58 OPEN test05; 59 OPEN test06; 60 OPEN test07; 61 OPEN test08; 62 OPEN test09; 63 OPEN test10; 64 OPEN test11; 65 OPEN test12; 66 OPEN test13; 67 OPEN test14; 68 OPEN test15; 69 OPEN test16; 70 OPEN test17; 71 OPEN test18; 72 OPEN test19; 73 OPEN test20; 74 OPEN test21; 75 OPEN test22; 76 OPEN test23; 77 OPEN test24; 78 OPEN test25; 79 OPEN test26; 80 OPEN test27; 81 OPEN test28; 82 OPEN test29; 83 OPEN test30; 84 OPEN test31; 85 OPEN test32; 86 OPEN test33; 87 OPEN test34; 88 OPEN test35; 89 OPEN test36; 90 OPEN test37; 91 OPEN test38; 92 OPEN test39; 93 OPEN test40; 94 OPEN test41; 95 OPEN test42; 96 OPEN test43; 97 OPEN test44; 98 OPEN test45; 99 OPEN test46; 100 OPEN test47; 101 OPEN test48; 102 END; 103 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
Aha! An error. This is pretty conclusive evidence. All those 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. By the way, I reran this test opening only 47 cursors within the inner block, and then my code executed successfully. More on that in a moment.
Do you wonder why the cursor math doesn't work out? I do to. I'm sure one cursor is necessary to execute my anonymous block. But one cursor to execute the anonymous block + 48 cursors opened in the inner block + 1 cursor opened in the outer block = 50 cursors. I should have been able to open 50 cursors, so what put me over the limit? Is another cursor opened to execute the inner block? If you know the answer, please let me know. Regardless, it's clear that PL/SQL is NOT implicitly closing cursors at the end of a block.
Now for the Strange Part
When I modified my test case (above) so that the inner block only opened 47 cursors, I could execute the code successfully. What's more, I could execute it repeatedly. As I sit here writing this, I can paste the code into SQL*Plus over, and over, and over again. So while PL/SQL doesn't implicitly close cursors at the end of a block, at least not at the end of an inner block, it seems equally clear that some cleanup is occurring when control returns from the outer block back to SQL*Plus. This I don't understand. So while I've answered Mike's question (I hope!), all I seem to have in return for my troubles is yet another question!