Oracle OCI - Fetch Data Into Array

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;

Allocate Buffers

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];

Prepare the Query

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);

Associate Select List with Data Buffer

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.

Execute the Query

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.

Define How Many Rows 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);

Process Fetched Data

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.

Complete Example in C++

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);
  }

Resources

SQLines Services

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.

You could leave a comment if you were logged in.