Oracle Call Interface (OCI) allows you to fetch data into an array, so the first call to OCIStmtExecute and subsequent calls of OCIStmtFetch2 return multiple rows at a time.
Assume there is the following query and you want to fetch multiple rows at a time:
-- Retrieve the schema and table name for all tables that the current user can access SELECT owner, table_name FROM all_tables;
Let's fetch 100 rows at once, and since the query returns 2 character columns with the maximum length of 30 bytes each, you have to allocate the following array in a C/C++ program:
C++:
// Fetched data, array of 100 strings containing 31 characters each char owner[100][31]; char table_name[100][31]; // Fetched data indicators, lengths and codes sb2 owner_ind[100], table_name_ind[100]; ub2 owner_len[100], table_name_len[100]; ub2 owner_code[100], table_name_code[100];
The next step (you can find the full source code at the end of this article) is to prepare the statement:
char *query = "SELECT owner, table_name FROM all_tables"; // Allocate statement handle OCIHandleAlloc(envhp, (void**)&stmtp, OCI_HTYPE_STMT, 0, NULL); // Set prefetch count OCIAttrSet(stmtp, OCI_HTYPE_STMT, (void*)&prefetch_rows, sizeof(int), OCI_ATTR_PREFETCH_ROWS, errhp); // Prepare the query OCIStmtPrepare(stmtp, errhp, (text*)query, strlen(query), OCI_NTV_SYNTAX, OCI_DEFAULT);
Then you need to associate each item in the select list with the output data buffer:
// Define the select list items OCIDefineByPos(stmtp, &defnpp, errhp, 1, (void*)owner, 31, SQLT_STR, (void*)owner_ind, owner_len, owner_code, OCI_DEFAULT); OCIDefineByPos(stmtp, &defnpp, errhp, 2, (void*)table_name, 31, SQLT_STR, (void*)table_name_ind, table_name_len, table_name_code, OCI_DEFAULT);
Note that you specify the buffer address, type and length for the first item in the array only. OCIDefineByPos does not require specifying any information about the array and its size.
Now you can execute the query, and specify to fetch first 100 rows:
// Execute the statement and perform the initial fetch of 100 rows into the defined array OCIStmtExecute(svchp, stmtp, errhp, 100, 0, NULL, NULL, OCI_DEFAULT);
If the query returns less than 100 rows, OCIStmtExecute function returns OCI_NO_DATA (integer value 100) that means you do not need to perform subsequent fetch as all rows are already returned.
Then you have to define how many rows were actually returned by the last OCIStmtExecute or OCIStmtFetch2 calls as there can be less rows available than the array size, especially for the last fetch operation.
You can get OCI_ATTR_ROWS_FETCHED attribute of the statement:
// Define how many rows were actually fetched OCIAttrGet(stmtp, OCI_HTYPE_STMT, (void*)&fetched, NULL, OCI_ATTR_ROWS_FETCHED, errhp);
Since each call to OCIStmtExecute and OCIStmtFetch2 can return multiple rows, you have to process them in a loop, for example:
// Output fetched data for(int i = 0; i < fetched; i++) printf("%s.%s\n", owner[i], table_name[i]);
Then you can retrieve another set of rows using OCIStmtFetch2:
// Fetch another set of rows OCIStmtFetch2(stmtp, errhp, 100, OCI_DEFAULT, 0, OCI_DEFAULT);
If OCIStmtFetch2 returns OCI_NO_DATA this does not mean that no data fetched, this means that the number of fetched rows is less than the array size, they are all fetched by this OCIStmtFetch2 call, and you do not need to call OCIStmtFetch2 anymore.
Even if OCIStmtFetch2 returned OCI_NO_DATA, you have to request OCI_ATTR_ROWS_FETCHED attribute to define how many rows are in the array.
This full source code of a C++ program that fetches data using arrays:
Note: Error handling is skipped to simplify the demo program.
#include <stdio.h> #include <string.h> #include <oci.h> void main() { // OCI handles OCIEnv *envhp; OCIError *errhp; OCIServer *srvhp; OCISvcCtx *svchp; OCISession *authp; OCIStmt *stmtp; OCIDefine *defnpp; // Connection information text* user = (text*)"scott"; text* pwd = (text*)"tiger"; text* sid = (text*)"orcl"; int prefetch_rows = 100; int fetched; char *query = "SELECT owner, table_name FROM all_tables"; // Fetched data char owner[100][31]; char table_name[100][31]; // Fetched data indicators, lengths and codes sb2 owner_ind[100], table_name_ind[100]; ub2 owner_len[100], table_name_len[100]; ub2 owner_code[100], table_name_code[100]; // Allocate environment int rc = OCIEnvCreate(&envhp, OCI_DEFAULT, NULL, NULL, NULL, NULL, 0, NULL); // Allocate error handle rc = OCIHandleAlloc(envhp, (void**)&errhp, OCI_HTYPE_ERROR, 0, NULL); // Allocate server and service context handles rc = OCIHandleAlloc(envhp, (void**)&srvhp, OCI_HTYPE_SERVER, 0, NULL); rc = OCIHandleAlloc(envhp, (void**)&svchp, OCI_HTYPE_SVCCTX, 0, NULL); // Attach to the server rc = OCIServerAttach(srvhp, errhp, sid, strlen((char*)sid), 0); // Set server in the service context rc = OCIAttrSet(svchp, OCI_HTYPE_SVCCTX, (dvoid*)srvhp, 0, OCI_ATTR_SERVER, errhp); // Allocate session handle rc = OCIHandleAlloc(envhp, (void**)&authp, OCI_HTYPE_SESSION, 0, NULL); // Set user name and password rc = OCIAttrSet(authp, OCI_HTYPE_SESSION, (void*)user, strlen((char*)user), OCI_ATTR_USERNAME, errhp); rc = OCIAttrSet(authp, OCI_HTYPE_SESSION, (void*)pwd, strlen((char *)pwd), OCI_ATTR_PASSWORD, errhp); // Connect rc = OCISessionBegin(svchp, errhp, authp, OCI_CRED_RDBMS, OCI_DEFAULT); // Set session in the service context rc = OCIAttrSet(svchp, OCI_HTYPE_SVCCTX, authp, 0, OCI_ATTR_SESSION, errhp); // Allocate statement handle rc = OCIHandleAlloc(envhp, (void**)&stmtp, OCI_HTYPE_STMT, 0, NULL); // Set prefetch count rc = OCIAttrSet(stmtp, OCI_HTYPE_STMT, (void*)&prefetch_rows, sizeof(int), OCI_ATTR_PREFETCH_ROWS, errhp); // Prepare the query rc = OCIStmtPrepare(stmtp, errhp, (text*)query, strlen(query), OCI_NTV_SYNTAX, OCI_DEFAULT); // Define the select list items rc = OCIDefineByPos(stmtp, &defnpp, errhp, 1, (void*)owner, 31, SQLT_STR, (void*)owner_ind, owner_len, owner_code, OCI_DEFAULT); rc = OCIDefineByPos(stmtp, &defnpp, errhp, 2, (void*)table_name, 31, SQLT_STR, (void*)table_name_ind, table_name_len, table_name_code, OCI_DEFAULT); // Execute the statement and perform the initial fetch of 100 rows into the defined array rc = OCIStmtExecute(svchp, stmtp, errhp, 100, 0, NULL, NULL, OCI_DEFAULT); while(rc >= 0) { OCIAttrGet(stmtp, OCI_HTYPE_STMT, (void*)&fetched, NULL, OCI_ATTR_ROWS_FETCHED, errhp); // OCI_NO_DATA is returned by OCIStmtExecute and OCIStmtFetch2 when the number of fetched rows // is less than the number of rows allocated in the array if(fetched == 0) break; // Output fetched data for(int i = 0; i < fetched; i++) printf("%s.%s\n", owner[i], table_name[i]); if(rc == OCI_NO_DATA) break; // Fetch another set of rows rc = OCIStmtFetch2(stmtp, errhp, 100, OCI_DEFAULT, 0, OCI_DEFAULT); } rc = OCIHandleFree(stmtp, OCI_HTYPE_STMT); // Disconnect rc = OCISessionEnd(svchp, errhp, authp, OCI_DEFAULT); rc = OCIServerDetach(srvhp, errhp, OCI_DEFAULT); rc = OCIHandleFree(envhp, OCI_HTYPE_ENV); }
SQLines offers database administration, optimization and migration services for Oracle and SQL Server databases and applications. For more information, please Contact Us.
Written by Dmitry Tolpeko, dmtolpeko@sqlines.com - September 2012.