Menu
With PL/SQL will there ever be a situation, say in the case of an exception, where an implicit cursor will fail to close?
I understand that an implicit cursor is supposed to close itself after use, but just want to know if there's ever a situation where this might not be the case, and if this is possible, what kind of remediation will be a good idea.
410
Drawbacks of Implicit CursorsIt is more vulnerable to data errors. It gives you less programmatic control. Even if your query returns only a single row, you might still decide to use an explicit cursor. However, there is no explicit cursor for UPDATE, DELETE, and INSERT statements.
If you declare a cursor in an anonymous block, procedure, or function, the cursor will automatically be closed when the execution of these objects end. However, you must explicitly close package-based cursors. Note that if you close a cursor that has not opened yet, Oracle will raise an INVALID_CURSOR exception.
Comments. Yes, open cursors both consume some memory on their own, and also pin pieces of the btree into cache, which interferes with JE's ability to manage its cache. You should always close cursors promptly, and remember to do so in a finally statement to make sure you don't leak them if there is an exception.
A cursor is a name or handle to a specific private SQL area. As shown in Figure 14-5, you can think of a cursor as a pointer on the client side and as a state on the server side. The result of a query is a result set and is pointed to by the cursor. It is stored in a temporary location, either in memory or on disk.
When COMMIT or ROLLBACK fails the cursor will not close automatically
It is recommended to use route bellow when using cursors:
-- before using the cursor
IF your_cursor %ISOPEN THEN
CLOSE your_cursor;
END IF;
OPEN your_cursor;
-- after using the cursor
CLOSE your_cursor;
-- when exception
IF your_cursor %ISOPEN THEN
CLOSE your_cursor;
END IF;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
