Home » Developer & Programmer » Precompilers, OCI & OCCI » How do I loop through a recordset in OCI (Solaris 10, Oracle 10, C )
How do I loop through a recordset in OCI [message #411171] Thu, 02 July 2009 04:10
Messages: 36
Registered: December 2006
I am probably being obtuse here, but I cannot see from the documentation how to step through the records returned from a select statement.
The select statement is quite straight forward:
SELECT acc_number FROM acc_inf;

This will return about 2.5 million records and I want to take each value for acc_number and use it as the input to another procedure, but I dont know how to get to the next value out of the recordset.
I am guessing that it will probably be a bad idea to pull back all 2.5 million records at once, so I am setting the Prefetch Rows parameter to 1000. So am aware that I will need to use the OCIStmtFetch function, but again I am not entirely sure how to use it in a loop until there is no more data.
Any help will be gratefully accepted.

My code is:

    /* Declarations */
    char             AccountNumber[20] = {""};
    char             QueryString[500] = {""};      /* String to hold the SQL query */
    sb4              RtrnValue;
    ub4              MaxNumRows;
    OCIDefine        *OraDefPtr;

    /* Initialisations */
    RtrnValue     = 0;
    NumRecords    = 0;
    MaxNumRows    = 1000;

    TRACE(("TRACE: Entering function check_acc_details\n"));

    /* Query the database to get the list of account numbers */
    TRACE(("TRACE: Build the query string\n"));
    strcpy(QueryString, "SELECT acc_number FROM ACC_INF\0");

    /* Attach sql statement to statement handle */
    TRACE(("TRACE: Preparing statement handle\n"));
    OCIStmtPrepare( OraStmntHandlePtr, 
            OCI_DEFAULT );

    /* Set the prefetch rows attribute */
    OCIAttrSet(OraStmntHandlePtr, (ub4) OCI_HTYPE_STMT, (dvoid *) &MaxNumRows, (ub4)sizeof(MaxNumRows), (ub4)OCI_ATTR_PREFETCH_ROWS, OraErrorHandlePtr);

    /* Define output variable */
    TRACE(("TRACE: Defining the output variable\n"));
    RtrnValue = OCIDefineByPos( OraStmntHandlePtr,
            &AccountNumber,        /* destination variable */
            (dvoid *) 0, (ub2 *) 0, (ub2 *) 0, OCI_DEFAULT);
    if ( RtrnValue != 0 )
        checkerr( OraErrorHandlePtr, RtrnValue);

    /* Execute statement */
    TRACE(("TRACE: Executing the statement\n"));
    RtrnValue = OCIStmtExecute(OraServiceContextHandlePtr,
            0,    /* iters */
            (CONST OCISnapshot *) NULL,
            (OCISnapshot *) NULL,
    if ( RtrnValue != 0 )
        checkerr( OraErrorHandlePtr, RtrnValue);

Previous Topic: What are the types of Project that uses Pro *c
Next Topic: Data is wrong for char
Goto Forum:

Current Time: Mon Nov 23 16:43:05 CST 2020