go-oci8 issue 379

Makefile

#IC=$(HOME)/instantclient_18_5
IC=$(HOME)/instantclient_19_6
OLIB=$(IC)
OINC=$(IC)/sdk/include

#OH=/u01/app/oracle/product/12.1.0/dbhome_1
#OLIB=$(OH)/lib
#OINC=$(OH)/rdbms/public

oracle_break: clean
	gcc -o oracle_break -I $(OINC) oracle_break.cpp -L $(OLIB) -l stdc++ -l pthread -l clntsh

clean:
	rm -f oracle_break.o oracle_break

ocur.sql

-- select username, sid from v$session where username is not null;

SELECT s.username, a.sid, a.value, b.name
FROM v$sesstat a, v$statname b, v$session s
WHERE a.statistic# = b.statistic# AND b.name='opened cursors current'
and s.sid = a.sid and s.username is not null
ORDER BY a.value desc;

oracle_break.cpp

#include <sys/time.h>
#include <oci.h>
#include <cstring>
#include <iostream>
#include <unistd.h>
#include <pthread.h>

using namespace std;

// https://github.com/mattn/go-oci8/issues/379

// " the idea of the program is, while it is running check the target database
// for open cursors. The number of open cursors should go up as OCIBreak is
// called while OCIStmtExecute is running."

/*

Can run this externally to monitor open cursors:

SELECT a.sid, a.value, b.name
FROM v$sesstat a, v$statname b
WHERE a.statistic# = b.statistic# AND b.name='opened cursors current'
ORDER BY a.value desc;

*/

// "The ORA-01013 are wanted. That is what error happens when the OCIBreak is
// working correctly. Need to make sure the OCIBreak is happening on the
// OCIStmtExecute. So should see the output line OCIStmtExecute start but not
// see the output line OCIStmtExecute end. Getting the line OCIStmtFetch2()
// Error: ORA-01013 mean the OCIBreak is happening too late."

// For the user, connect as SYS and run:
// GRANT SELECT ON SYS.WRI$_OPTSTAT_HISTGRM_HISTORY TO CJTEST;
//#define QUERY "select OBJ# from SYS.WRI$_OPTSTAT_HISTGRM_HISTORY where rownum < 1000"

// GRANT SELECT ON V_$OPEN_CURSOR TO CJTEST;
//#define QUERY "select count(*) from v$open_cursor where user_name = user"

#define QUERY "select 1 from all_objects"

// connection parameters
const char *db_alias         = "localhost/orclpdb1";
const char *db_user_name     = "cjtest";
const char *db_user_password = "cjtest";

#define USLEEP_DELAY 12000

// helper error checking procedure to shorten main code
// returns true if critical error detected and prints out error information
bool check_oci_error(const char *error_point, OCIError *errorHp, sword status, OCIEnv *envHp);

OCIEnv *envHp = NULL; // environment handle
OCIError *errorHp = NULL; // error handle
OCIServer *serverHp = NULL; // server handle
OCISvcCtx *serviceHp = NULL;  // service handle
OCISession *sessionHp = NULL; // user session handle
OCIStmt *statementHp = NULL; // statement handle

void start() {
  // ----- CONNECTION INITIALIZATION PART ------------------------------------------------------

  cout << "Initialization start" << endl;

  sword rc;

  // create environment handle
  rc = OCIEnvCreate(&envHp, OCI_THREADED, (dvoid *)0, 0, 0, 0, (size_t) 0, (dvoid **)0);
  if( check_oci_error("OCIEnvCreate", NULL, rc, NULL) ) {
	cout << "OCIEnvCreate" << endl;
    return;
  }
  // allocate an error handle
  rc = OCIHandleAlloc ((dvoid *)envHp, (dvoid **)&errorHp, OCI_HTYPE_ERROR, 0, (dvoid **) 0);
  if( check_oci_error("OCIHandleAlloc(OCI_HTYPE_ERROR)", NULL, rc, envHp) ) return;
  // allocate a server handle
  rc = OCIHandleAlloc ((dvoid *)envHp, (dvoid **)&serverHp, OCI_HTYPE_SERVER, 0, (dvoid **) 0);
  if( check_oci_error("OCIHandleAlloc(OCI_HTYPE_SERVER)", NULL, rc, envHp) ) return;
  // create a server context
  rc = OCIServerAttach(serverHp, errorHp, (text *)db_alias, strlen (db_alias), OCI_DEFAULT);
  if( check_oci_error("OCIServerAttach()", errorHp, rc, envHp) ) return;
  // allocate a service handle
  rc = OCIHandleAlloc ((dvoid *)envHp, (dvoid **)&serviceHp, OCI_HTYPE_SVCCTX, 0, (dvoid **) 0);
  if( check_oci_error("OCIHandleAlloc(OCI_HTYPE_SVCCTX)", errorHp, rc, envHp) ) return;

  // set the server attribute in the service context handle
  rc = OCIAttrSet((dvoid *)serviceHp, OCI_HTYPE_SVCCTX, (dvoid *)serverHp, (ub4) 0, OCI_ATTR_SERVER, errorHp);
  if( check_oci_error("OCIAttrSet(OCI_HTYPE_SVCCTX,OCI_ATTR_SERVER)", errorHp, rc, envHp) ) return;

  // allocate a user session handle
  rc = OCIHandleAlloc((dvoid *)envHp, (dvoid **)&sessionHp,  OCI_HTYPE_SESSION, 0, (dvoid **) 0);
  if( check_oci_error("OCIHandleAlloc(OCI_HTYPE_SESSION)", errorHp, rc, envHp) ) return;

  // set user name attribute in user session handle
  rc = OCIAttrSet((dvoid *)sessionHp, OCI_HTYPE_SESSION, (dvoid *)db_user_name, strlen(db_user_name), OCI_ATTR_USERNAME, errorHp);
  if( check_oci_error("OCIAttrSet(OCI_HTYPE_SESSION,OCI_ATTR_USERNAME)", errorHp, rc, envHp) ) return;
  // set password attribute in user session handle
  rc = OCIAttrSet((dvoid *)sessionHp, OCI_HTYPE_SESSION, (dvoid *)db_user_password, strlen(db_user_password), OCI_ATTR_PASSWORD, errorHp);
  if( check_oci_error("OCIAttrSet(OCI_HTYPE_SESSION,OCI_ATTR_PASSWORD)", errorHp, rc, envHp) ) return;

  rc = OCISessionBegin(serviceHp, errorHp, sessionHp, OCI_CRED_RDBMS, OCI_DEFAULT);
  if( check_oci_error("OCISessionBegin()", errorHp, rc, envHp) ) return;

  // set the user session attribute in the service context handle
  rc = OCIAttrSet( (dvoid *)serviceHp, OCI_HTYPE_SVCCTX, (dvoid *)sessionHp, (ub4) 0, OCI_ATTR_SESSION, errorHp);
  if( check_oci_error("OCIAttrSet(OCI_HTYPE_SVCCTX,OCI_ATTR_SESSION)", errorHp, rc, envHp) ) return;

  cout << "Initialization done" << endl;
}

void * test1(void *) {
  //----- PREPARE AND EXECUTE STATEMENT ------------------------------

  sword rc;
  timeval tv;
  long int ms;
  OCIDefine *col1defp   = NULL;
  double    col1value;  
  
  const char *query_text = QUERY;

  // cleanup last statement handle
  if (statementHp != NULL) {
	cout << "OCIHandleFree statementHp start" << endl;
	OCIHandleFree(statementHp, OCI_HTYPE_STMT);
	statementHp = NULL;
	cout << "OCIHandleFree statementHp end" << endl;
  }

  rc = OCIHandleAlloc(envHp, (void **)&statementHp, OCI_HTYPE_STMT, 0, NULL); 
  if( check_oci_error("OCIHandleAlloc(OCI_HTYPE_STMT)", errorHp, rc, envHp) ) return NULL;

  cout << "OCIStmtPrepare start" << endl;
  rc = OCIStmtPrepare(statementHp, errorHp, (const OraText *)query_text, strlen(query_text), OCI_NTV_SYNTAX, OCI_DEFAULT);
  if( check_oci_error("OCIStmtPrepare()", errorHp, rc, envHp) ) return NULL;
  cout << "OCIStmtPrepare end" << endl;
  
  // result column
  rc =  OCIDefineByPos(statementHp, &col1defp, errorHp, 1, &col1value, sizeof(col1value), SQLT_BDOUBLE, NULL, NULL, NULL, OCI_DEFAULT);
  if( check_oci_error("OCIDefineByPos()", errorHp, rc, envHp) ) return NULL;
  
  // execute and fetch
  gettimeofday(&tv, 0);
  ms = tv.tv_sec * 1000 + tv.tv_usec / 1000;
  cout << ms << endl;

  cout << "OCIStmtExecute start" << endl;
  rc = OCIStmtExecute(serviceHp, statementHp, errorHp, 0, 0, NULL, NULL, OCI_DEFAULT);
  if( check_oci_error("OCIStmtExecute()", errorHp, rc, envHp) ) return NULL;
  cout << "OCIStmtExecute end" << endl;

  cout << "OCIStmtFetch2 start" << endl;
  rc = OCIStmtFetch2(statementHp, errorHp, 1, OCI_FETCH_NEXT, 0, OCI_DEFAULT);
  while(rc != OCI_NO_DATA) {
    if( check_oci_error("OCIStmtFetch2()", errorHp, rc, envHp) ) return NULL;
//	cout << "value: " << col1value << endl;
    rc = OCIStmtFetch2(statementHp, errorHp, 1, OCI_FETCH_NEXT, 0, OCI_DEFAULT);
  }
  cout << "OCIStmtFetch2 end" << endl;

  cout << "OCIHandleFree statementHp start" << endl;
  OCIHandleFree(statementHp, OCI_HTYPE_STMT);
  statementHp = NULL;
  cout << "OCIHandleFree statementHp end" << endl;

  return NULL;
}

void cleanup() {
  cout << "cleanup start" << endl;
  
  sword rc;

  if (sessionHp != NULL) {
    rc = OCISessionEnd(serviceHp, errorHp, sessionHp, OCI_DEFAULT);
	if( check_oci_error("OCISessionEnd()", errorHp, rc, envHp) ) return;
  }
  if (serverHp != NULL) {
	rc = OCIServerDetach(serverHp, errorHp, OCI_DEFAULT);
	if( check_oci_error("OCIServerDetach()", errorHp, rc, envHp) ) return;
  }

  if (sessionHp != NULL) {
	OCIHandleFree(sessionHp, OCI_HTYPE_SESSION);
	sessionHp = NULL;
  }
  if (serviceHp != NULL) {
	OCIHandleFree(serviceHp, OCI_HTYPE_SVCCTX);
	serviceHp = NULL;
  }
  if (serverHp != NULL) {
	OCIHandleFree(serverHp, OCI_HTYPE_SERVER);
	serverHp = NULL;
  }
  if (errorHp != NULL) {
	OCIHandleFree(errorHp, OCI_HTYPE_ERROR);
	errorHp = NULL;
  }
  if (envHp != NULL) {
	OCIHandleFree(envHp, OCI_HTYPE_ENV);
	envHp = NULL;
  }

  cout << "cleanup end" << endl;

  return;
}

void * break1(void *) {
	timeval tv;
	long int ms;
	
	while(statementHp == NULL) {
		sched_yield();
		usleep(USLEEP_DELAY); // may need to adjust sleep delay
	}

	gettimeofday(&tv, 0);
	ms = tv.tv_sec * 1000 + tv.tv_usec / 1000;
	cout << ms << endl;
	cout << "OCIBreak start" << endl;
	OCIBreak( (dvoid *)serviceHp, errorHp);
	cout << "OCIBreak end" << endl;
}

int main(int, char* []) {
	int rc;
	pthread_t thread1;
	pthread_t thread2;

	start();

	for( int i = 0; i < 50; i++ ) {
		cout << "loop start" << endl;
		
		rc = pthread_create(&thread1, NULL, test1, NULL);
		if (rc) {
			cout << "Error:unable to create thread," << rc << endl;
			return 0;
		}

		rc = pthread_create(&thread2, NULL, break1, NULL);
		if (rc) {
			cout << "Error:unable to create thread," << rc << endl;
			return 0;
		}

		pthread_join(thread1, NULL);
		pthread_join(thread2, NULL);

		sleep(3);

		cout << "loop end\n" << endl;
	}

	cleanup();

	pthread_exit(NULL);
}

// helper error checking procedure to shorten main code
// returns true if critical error detected and prints out error information
bool check_oci_error(const char *error_point, OCIError *errorHp, sword status, OCIEnv *envHp) { 

  text errbuf[1024];
  sb4  errcode;
  bool ret_code = true;

  switch (status) { 
    case OCI_SUCCESS:
        ret_code = false;
      break;
    case OCI_SUCCESS_WITH_INFO:
        OCIErrorGet ((dvoid *) errorHp, (ub4) 1, (text *) NULL, &errcode, errbuf, (ub4) sizeof(errbuf), (ub4) OCI_HTYPE_ERROR);
        cout << error_point << " Error: OCI_SUCCESS_WITH_INFO; Info: " << errbuf << endl;
        ret_code = (errcode == 436 || errcode == 437 || errcode == 438 || errcode == 439);
      break;
    case OCI_NEED_DATA:
        cout << error_point << " Error: OCI_NEED_DATA"<< endl;
      break;
    case OCI_NO_DATA:
        cout << error_point << " Error: OCI_NO_DATA"<< endl;
      break;
    case OCI_ERROR:
        OCIErrorGet ((dvoid *) errorHp, (ub4) 1, (text *) NULL, &errcode, errbuf, (ub4) sizeof(errbuf), (ub4) OCI_HTYPE_ERROR);
        cout << error_point << " Error: " << errbuf << endl;
      break;
    case OCI_INVALID_HANDLE:
        cout << error_point << " Error: OCI_INVALID_HANDLE" << endl;
      break;
    case OCI_STILL_EXECUTING:
        cout << error_point << " Error: OCI_STILL_EXECUTE"<< endl;
      break;
    case OCI_CONTINUE:
        cout << error_point << " Error: OCI_CONTINUE" << endl;
      break;
    default:
        cout << error_point << " Error: UNKNOWN(" << status << ")" << endl;
      break;
  }

  return ret_code;
}