CODEWITHSUNDEEP
c++oracle-databaseoracle-call-interface
hoganj_lv_426
hoganj_lv_426

Reputation: 33

Oracle OCI OCIStmtFetch2 hangs cursor C++

My code hangs when I try to iterate over a cursor. The execute statement returns approx 800k results (when I run in sqlplus). When I do something similar calling a simpler stored procedure with 20 results it returns instantly. I have left the program hanging for over 12 hours and still not returned. I am using Oracle11g oci.lib, however when I tested with a 9i version of the lib it returned within 5 seconds. Using 9i Oci is not an option for me (for reasons outside my control). I suspect the OCIStmtFetch2() call is pre-caching all the results first. Is it possible to stop this and make the cursor useable?

Stored procedure declaration:

CREATE OR REPLACE PROCEDURE FXT_TEST_CALL(CRESULTS OUT SYS_REFCURSOR) IS

and here is the code: (I've removed some boilerplate)

OCIStmt* pOciStatement;
OCIStmt* cursor;
OCIEnv* g_pOciEnvironment = NULL;
OCIServer* g_pOciServer = NULL;
OCISession* g_pOciSession = NULL;
OCISvcCtx* g_pOciServiceContext = NULL;
char* sqlCharArray = "BEGIN fxt.fxt_test_call(:refCursor ); END;";

answer = OCIInitialize(OCI_THREADED, NULL, NULL, NULL, NULL);
answer = OCIEnvInit(&g_pOciEnvironment, OCI_DEFAULT, 0, NULL);
answer = OCIHandleAlloc(g_pOciEnvironment, (void **)&pOciError, OCI_HTYPE_ERROR, 0, NULL);
answer = OCIHandleAlloc(g_pOciEnvironment, (void **)&g_pOciSession, OCI_HTYPE_SESSION, 0, NULL);
answer = OCIHandleAlloc(g_pOciEnvironment, (void **)&g_pOciServer, OCI_HTYPE_SERVER, 0, NULL);
answer = OCIHandleAlloc(g_pOciEnvironment, (void **)&g_pOciServiceContext, OCI_HTYPE_SVCCTX, 0, NULL);
answer = OCIServerAttach(g_pOciServer, pOciError, (unsigned char *)pConnectChar, strlen(pConnectChar),OCI_DEFAULT);
answer = OCIAttrSet(g_pOciSession, OCI_HTYPE_SESSION, (unsigned char *)pUsernameChar, strlen(pUsernameChar),OCI_ATTR_USERNAME, pOciError);
answer = OCIAttrSet(g_pOciSession, OCI_HTYPE_SESSION, (unsigned char *)pPasswordChar, strlen(pPasswordChar),OCI_ATTR_PASSWORD, pOciError);
answer = OCIAttrSet(g_pOciServiceContext, OCI_HTYPE_SVCCTX, g_pOciServer, 0, OCI_ATTR_SERVER, pOciError);
answer = OCIAttrSet(g_pOciServiceContext, OCI_HTYPE_SVCCTX, g_pOciSession, 0, OCI_ATTR_SESSION, pOciError);
answer = OCISessionBegin(g_pOciServiceContext, pOciError, g_pOciSession, OCI_CRED_RDBMS, OCI_DEFAULT);
answer = OCIHandleAlloc(g_pOciEnvironment, (void **)(&pOciStatement), OCI_HTYPE_STMT, 0, NULL);
answer = OCIStmtPrepare(pOciStatement, pOciError, (unsigned char *)sqlCharArray, strlen(sqlCharArray),OCI_NTV_SYNTAX, OCI_DEFAULT);
answer = OCIHandleAlloc(g_pOciEnvironment, (void **)(&cursor), OCI_HTYPE_STMT, 0, NULL);

answer = OCIBindByPos(pOciStatement,&pBind, pOciError, 1, &cursor, 0,SQLT_RSET, pIndicator2, 0,NULL, 0,0,OCI_DEFAULT);

OCINumber numTest;

answer = OCIStmtExecute(g_pOciServiceContext, pOciStatement, pOciError, 1, 0, NULL, NULL, OCI_COMMIT_ON_SUCCESS);
answer = OCIDefineByPos(cursor,&pOciDefine2, pOciError,1,&numTest,sizeof(OCINumber), SQLT_VNU, 0, 0, 0,OCI_DEFAULT);

// Program hangs here
while ((answer = OCIStmtFetch2(cursor,pOciError, 1,OCI_FETCH_NEXT,0,OCI_DEFAULT)) == 0)
{
    OCIAttrGet(cursor, OCI_HTYPE_STMT, (void*)&fetched, NULL, OCI_ATTR_ROWS_FETCHED, pOciError);
    long long newNum =  0;
    OCINumberToInt(pOciError, &numTest, sizeof(long long), OCI_NUMBER_SIGNED , &newNum);
}

Upvotes: 1

Views: 762

Answers (1)

hoganj_lv_426
hoganj_lv_426

Reputation: 33

Since this has been hanging around for a while with no answer I will reply for anyone curious, how I got around the situation. It was performing slow in an MFC debug build, so I ran in release mode and it worked (with a 2 minute pause).

I still don't see what difference this would make, perhaps the libraries work differently in debug/release builds.

Anyway, its not an issue anymore, for the moment at least.

Upvotes: 1

Related Questions