hold_cursor and release_cursor

David Clement

September 2004

Oracle versions 7-8


The related precompiler options hold_cursor and release_cursor can be used to tune parsing and cursor management. Hold_cursor by default is "no," which tells Oracle that after it executes a SQL statement it should release the links to the cursor cache entry and memory used, so the space is available for reuse. Release_cursor by default is "no," which tells Oracle that after it executes a SQL statement it should maintain the links to the cursor cache entry and memory used, so the cursor is available for reuse.

In case of conflict, hold_cursor=no overrides release_cursor=no, but release_cursor=yes overrides hold_cursor=yes. Oracle provides the following table to summarize the results. This is adapted from the Pro*C/C++ Programmer's Guide in the 8.1.6 documentation but the information has not changed for several minor releases.

hold_cursor release_cursor Cursor links are...
N N marked reusable
Y N maintained
N Y removed immediately
Y Y removed immediately