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

Reputation: 33

Call OCIStmtExecute() with ref cursor and OCI_ATTR_PREFETCH_ROWS?

I can call a stored procedure via OCI with a ref cursor, then iterate over the results using a call to OCIStmtFetch2() each time. My question is how can I loop over a set of rows so I don't have to call OCIStmtFetch2() each time? Presume the answer will be something with OCI_ATTR_PREFETCH_ROWS but I cant seem to get this working as access voilations are being thrown everytime I call OCIStmtExecute.

http://www.sqlines.com/oracle/oci/array_fetch works for select statements but I want to use it for a cursor which crashes on my implementation. Sorry for code length but here is the full function:

OCIError* pOciError;
OCIStmt* pOciStatement;
char* sqlCharArray = "BEGIN fxt.fxt_get_risk_trns('SOD', '29-SEP-2014', :refCursor ); END;";
OCIEnv* g_pOciEnvironment = NULL;
OCIServer* g_pOciServer = NULL;
OCISession* g_pOciSession = NULL;
OCISvcCtx* g_pOciServiceContext = NULL;
sb2* pIndicator=0;
OCIDefine* pOciDefine2;
OCIBind* pBind;
OCIStmt* cursor;

int answer = OCIEnvCreate((OCIEnv **)&g_pOciEnvironment, (ub4)OCI_THREADED ,
     (void  *)0, (void  * (*)(void  *, size_t))0,
     (void  * (*)(void  *, void  *, size_t))0,
     (void (*)(void  *, void  *))0,
     (size_t)0, (void  **)0);

<snip boilerplate code>
const int prefetchSize(PREFETCH_ROWS_SIZE);
answer = OCIHandleAlloc(g_pOciEnvironment , (void **)(&pOciStatement), OCI_HTYPE_STMT, 0, NULL);
answer = OCIAttrSet(pOciStatement, OCI_HTYPE_STMT, (void*)&prefetchSize, sizeof(int), OCI_ATTR_PREFETCH_ROWS, pOciError);

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, pIndicator, 0, NULL, 0, 0, OCI_DEFAULT);

// Fetched data indicators, lengths and codes
char dealSTSCode[PREFETCH_ROWS_SIZE][4000];
sb2 dealSTSCode_ind[PREFETCH_ROWS_SIZE];
ub2 dealSTSCode_len[PREFETCH_ROWS_SIZE], dealSTSCode_code[PREFETCH_ROWS_SIZE];

answer = OCIAttrSet(cursor, OCI_HTYPE_STMT, (void*)&prefetchSize, sizeof(int), OCI_ATTR_PREFETCH_ROWS, pOciError);

// Unhandled exception thrown here, Access violation reading location xxx
answer = OCIStmtExecute(g_pOciServiceContext, pOciStatement, pOciError, PREFETCH_ROWS_SIZE, 0, NULL, NULL, OCI_DEFAULT);
answer = OCIDefineByPos(cursor,&pOciDefine2, pOciError,6, (void*)&dealSTSCode, 4000, SQLT_STR, dealSTSCode_ind, dealSTSCode_len, dealSTSCode_code,OCI_DEFAULT);    

if (answer == 0)
{
    int rowsFetched = 0;
    do
    {
        if (!OCIStmtFetch2(cursor, pOciError, 100, OCI_FETCH_NEXT,0,OCI_DEFAULT))
        {
            OCIAttrGet(cursor, OCI_HTYPE_STMT, (void*)&rowsFetched, NULL, OCI_ATTR_ROWS_FETCHED, pOciError);
            for (int i = 0; i != rowsFetched; ++i)
            {
                // process row
            }
        }
    }
    while (rowsFetched > 0);
}

Upvotes: 0

Views: 1101

Answers (1)

hoganj_lv_426
hoganj_lv_426

Reputation: 33

I have found a solution to this. Needed to try some more combinations of calls but eventually got there. I have corrected the original code to reflect the correct solution.

The Key thing is to define the prefetch rows attribute on the cursor itself, not the statement pointer.

Upvotes: 1

Related Questions