Reopen Cursors to Reuse Them

David Clement

September 2004

Oracle versions 7-8

One of Oracle's extensions to the ANSI definition of SQL is that in the Pro*C/C++ environment it is legal to reopen a cursor that has not closed. The effect of repeating the open statement on an already open cursor is to reevaluate the cursor and get a new result set. The cursor is executed afresh, without the overhead of parsing.

This extension is an important optimization for long-running batch processes. It is a fast way to poll a queue to see if new items have arrived. It is also handy where the cursor is long-running and has a risk of encountering ORA-01555, "Snapshot too old," because it can be used to shorten the lifespan of transactions without the overhead of hard parsing.