HOWTO : Understanding Explicit Commit Requirement To Close a Cursor in Pro*C (Doc ID 1256114.1)

Last updated on SEPTEMBER 23, 2016

Applies to:

Oracle TimesTen In-Memory Database - Version 11.2.1.6.0 to 11.2.1.7.1 [Release 11.2]
Information in this document applies to any platform.
This problem is limited to Pro*C programs running against TimesTen.

This problem involves programs which iteratively open/fetch/close a cursor and then repeat the cycle, reopening a closed cursor. This problem is believed to be limited to circumstances where a bulk fetch is performed, so that multiple rows of data are fetched from the cursor into an array.

***Checked for relevance on 13-Aug-2012***
***Checked for relevance on 13-Jan-2014***

Symptoms

When run against a TimesTen data store, Pro*C programs which iteratively open, close and then reopen cursors must include an explicit 'commit' statement each time a cursor is closed. This is true even when the cursor is read-only and does not update the data store. Failure to include a commit after closing the cursor will result in the cursor not having been closed: subsequent attempts to reopen that cursor will fail without any exception being returned and the cursor's pointer will remain pointed at the last row of fetched data. This problem is believed to be limited to programs where an array fetch is used to perform bulk fetches of data from the cursor. 

Consider the following code construct:
                                                                           

for (ctr=0; ctr<2; ctr++) {
EXEC SQL DECLARE Cur01 CURSOR FOR SELECT empno, ename, job FROM emp;
EXEC SQL OPEN Cur01;

{/* fetch loop performs array fetch and print each row of the cursor */ }

EXEC SQL CLOSE CUR01;
}


This code fragment attempts to open a cursor, fetch through the cursor, close the cursor and then attempts to repeat the open/fetch loop/close. If this code is executed, all of the rows in the cursor will be fetch and printed in sequence during the first loop iteration. However, when the loop is reopened, the cursor will remain pointed to the last row of the fetch and will print only that last row. This is because the cursor was not closed after the first iteration.

Were this same executable to be run against an Oracle database,  this problem would not be observed and the code given above would cause the contents of the cursor to be listed twice in identical order.
    
                                                    

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms