Hi,
We are currently running into an issue with allocating more than 3*10^6 descriptors on 32bit applications and not more than 5*10^7 descriptors for a 64 bit test application on windows 64 bit environments. This function according to OCI documentation, will throw an error in case it cannot allocate more descriptors, instead we find the oci error handle does not set any messages in the error buffer.We aren't able to even see an error code set by it, but the function returns -1, so we know its not setting the descriptors and failing instead. This error is also seen in the call to OCIArrayDescriptorFree() in case of too many descriptors being allocated in a 32bit application.
We tried looking online for official oracle documentation about the issue, but found nothing: https://docs.oracle.com/cd/B28359_01/appdev.111/b28395/oci16rel002.htm#i493114
We have no idea what to set the upper limit of the number to as oracle has no documentation of what the upper limit should be, or what the limit depends on , ie: is it dependent on RAM of system, or OS type, et cetera. We have also written simple OCI code to test this and it too has the same issue of not being able to allocate more that 50 mill descriptors.
Would like inputs on the following:
- We would like to know if this is an issue with OCI and the reason for failure of allocation of the descriptors.
- Is there a workaround that would enable us to fetch over 50 million rows of timestamp timezone in a single OCI fetch.
Dev environment:
- Hardware: Worksation- 16GB RAM, 1TB HDD, Intel Core i7-6700 CPU
- Software:Windows 10 64 bit, VS2015 for cpp development
- Oracle DB version 12C
- OCI version: 12.2.0.1.0
Below is the sample code used for testing the descriptor limit on fetching over 50 million rows of Timestamp Timezone from a 12c data source in a single fetch:
#include "stdafx.h"
#include <stdlib.h>
#include <oratypes.h>
#include <oci.h>
//#include <windows.h>
#include <iostream>
#include<time.h>
#include<stdio.h>
#include<chrono>
#include<thread>
#include<string>
#include <map>
#include <list>
#include <iterator>
using namespace std;
OCISPool * m_ociSPool;
OCISvcCtx *m_ociService;
OCIEnv *ociEnv = NULL;
OCIError *ociError = NULL;
// OCI server handle. (OWN)
OCIServer *m_ociServer = NULL;
bool flag;
OCIBind* bindHandle = (OCIBind *)NULL;
OCIStmt *statement = (OCIStmt *)0;
OCIDefine *definehandle = (OCIDefine *)0;
OCIBind *bindhandle = (OCIBind *)0;
OCIDateTime * x = (OCIDateTime *)0;
OCISession *m_session;
OCIStmt *stmtp;
OCIDefine *def;
void CHK_OCI_SUCCESS(OCIError *errhp, sword status)
{
text errbuf[512];
sb4 errcode = 0;
std::string x;
int s = std::string::npos;
sword err;
switch (status)
{
case OCI_SUCCESS:
break;
case OCI_SUCCESS_WITH_INFO:
(void)printf("Error - OCI_SUCCESS_WITH_INFO\n");
break;
case OCI_NEED_DATA:
(void)printf("Error - OCI_NEED_DATA\n");
break;
case OCI_NO_DATA:
(void)printf("Error - OCI_NODATA\n");
break;
case OCI_ERROR:
err = OCIErrorGet((dvoid *)errhp, (ub4)1, (text *)NULL, &errcode,
errbuf, (ub4) sizeof(errbuf), OCI_HTYPE_ERROR);
x = std::string((char *)errbuf);
s = x.find("ORA-01017:");
if (s != std::string::npos)
{
printf("invalid Proxy UserID/Proxy Password; logon denied");
}
(void)printf("Error - %.*s\n", 512, errbuf);
break;
case OCI_INVALID_HANDLE:
(void)printf("Error - OCI_INVALID_HANDLE\n");
break;
case OCI_STILL_EXECUTING:
(void)printf("Error - OCI_STILL_EXECUTE\n");
break;
case OCI_CONTINUE:
(void)printf("Error - OCI_CONTINUE\n");
break;
default:
break;
}
}
void main()
{
// OCI handles
OCIEnv *envhp;
OCIError *errhp;
OCIServer *srvhp;
OCISvcCtx *svchp;
OCISession *authp;
OCIStmt *stmtp;
OCIDefine *defnpp;
// Connection information
text* user = (text*)"test";
text* pwd = (text*)"abc123";
text* sid = (text*)"ORCL12C";
int fetched;
// we kept cross joins on the same table so as to reach a value above 50 million
char *query = "select KeyColumn,Column1 from internal_test.Timestamp_Timezone_Table cross join internal_test.multi1000 cross join internal_test.multi1000 cross join internal_test.multi1000 cross join internal_test.multi1000";
// Fetched data
char owner[100][31];
char table_name[100][50];
// 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];
unsigned char *m_dataBuffer;
unsigned char *m_bufferAtRow;
cout << "Assigning buffer" << endl;
m_dataBuffer = new unsigned char[900000000];
m_bufferAtRow = m_dataBuffer;
int m_moveLength = sizeof(OCIDateTime**);
//m_dataBuffer = new unsigned char[m_moveLength * 10];
// 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);
// 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*)m_dataBuffer, 0, SQLT_TIMESTAMP_LTZ, (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,
0,
0,
NULL,
NULL,
32);
CHK_OCI_SUCCESS(errhp, rc);
rc = OCIArrayDescriptorAlloc(
envhp,
reinterpret_cast<void**>(m_dataBuffer),
OCI_DTYPE_TIMESTAMP_LTZ,
60000000,
0,
0);
CHK_OCI_SUCCESS(errhp, rc);
// Function returns -1 and error messages from error handle cannot be retrieved
/* rc = OCIArrayDescriptorFree(
reinterpret_cast<void**>(m_dataBuffer),
OCI_DTYPE_TIMESTAMP_TZ);
CHK_OCI_SUCCESS(errhp, rc);*/
rc = OCIStmtFetch2(
stmtp,
errhp,
10,
OCI_FETCH_NEXT,
0,
OCI_DEFAULT);
int m_currentNumFetchedRows;
rc = OCIAttrGet(
stmtp,
OCI_HTYPE_STMT,
(void*)&m_currentNumFetchedRows,
NULL,
OCI_ATTR_ROWS_FETCHED,
errhp);
CHK_OCI_SUCCESS(errhp, rc);
OCIDateTime* srcBuffer;
for (int i = 0; i < 10; i++)
{
sb2 year;
ub1 month;
ub1 day;
srcBuffer = *reinterpret_cast<OCIDateTime**>(m_bufferAtRow);
rc = OCIDateTimeGetDate(
envhp,
errhp,
srcBuffer,
&year,
&month,
&day);
ub1 hour;
ub1 minute;
ub1 second;
ub4 fraction;
rc = OCIDateTimeGetTime(
envhp,
errhp,
srcBuffer,
&hour,
&minute,
&second,
&fraction);
sb1 hourOffset;
sb1 minuteOffset;
rc = OCIDateTimeGetTimeZoneOffset(
envhp,
errhp,
srcBuffer,
&hourOffset,
&minuteOffset);
CHK_OCI_SUCCESS(errhp, rc);
m_bufferAtRow = m_bufferAtRow + 8;
}
rc = OCIArrayDescriptorFree(
reinterpret_cast<void**>(m_dataBuffer),
OCI_DTYPE_TIMESTAMP_TZ);
CHK_OCI_SUCCESS(errhp, rc);
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);
}
SQL scripts to create the tables:
CREATE TABLE internal_test.Timestamp_Timezone_Table(KeyColumn VARCHAR2 (255),Column1 TIMESTAMP WITH TIME ZONE);
INSERT INTO internal_test.Timestamp_Timezone_Table VALUES('TypicalDate', TIMESTAMP '1955-10-11 01:00:00 +2:00');
INSERT INTO internal_test.Timestamp_Timezone_Table VALUES('TypicalDate', TIMESTAMP '1955-10-11 01:00:00 +3:00');
INSERT INTO internal_test.Timestamp_Timezone_Table VALUES('TypicalTime', TIMESTAMP '2013-05-01 09:30:12 America/Los_Angeles PDT');
INSERT INTO internal_test.Timestamp_Timezone_Table VALUES('TypicalTimeBase24', TIMESTAMP '2013-05-01 13:59:23 -8:00');
INSERT INTO internal_test.Timestamp_Timezone_Table VALUES('TimestampNanoSeconds', TIMESTAMP '1955-10-11 11:10:33.123456789 -12:00');
INSERT INTO internal_test.Timestamp_Timezone_Table VALUES('TimestampMicroSeconds', TIMESTAMP '1955-10-11 11:10:33.123456 -8:00');
Thanks in advance