OCI: Concurrent queries in 1 process ? (ORA-01076, ORA-03127)

From: <Torsten.Reichert_at_gmx.net>
Date: Wed, 02 Feb 2000 22:32:47 +0100
Message-ID: <3898A27F.5FE73E43@gmx.net>

I want to send concurrent queries (fork/wait scheme) to the oracle server like this:

1:      qid1=SendQuery1();
2:      ...do some calculation...
3:      qid2=SendQuery2();
4:      ...do some calculation...
5:      qid3=SendQuery3();
6:      ...do some calculation...
7:      wait(qid1);
8:      ...use data from Query 1...

using one lda,hda the oci call (#define ONE_LOGON !0) in line 3 returns:
        ORA-03127 no new operations allowed until the active operation

ends

using one logon (lda, hda) per parallell query (#define ONE_LOGON 0), the oci call returns

        ORA-01076 multiple logons per process not yet supported

Does anyone know if there is a way - maybe by means of threads - to make the above described example run ?
I suppose, since threads run in one process, the restriction of ORA-01076 still prohibits that.

Thank you

Running Sample-Source ( ANSI-C ):
/*
author: Torsten.Reichert_at_gmx.net
posted in: comp.databases.oracle.misc comp.databases.oracle.server

1. qid1=SendQuery1();
2. ...do some calculation...
3. qid2=SendQuery2();
4. ...do some calculation...
5. qid3=SendQuery3();
6. ...do some calculation...
7. wait(qid1);
8. ...use data from Query 1...

  ORA-03127 no new operations allowed until the active operation ends     Cause: An attempt was made to execute a new operation before the active, non-blocking operation completed or a new

           operation was attempted before all the pieces of a column were inserted or fetched.

    Action:Execute the new operation after the non-blocking operation completes. If piecewise binds/defines were done,

           execute the new operation after all the pieces have been inserted or fetched.
  ORA-01076 multiple logons per process not yet supported     Cause: Oracle does not support multiple logons per process.     Action: No action required.

  see: #define ONE_LOGON
using one lda,hda the oci call (#define ONE_LOGON !0) in line 3 returns:

        ORA-03127 no new operations allowed until the active operation ends

using one logon (lda, hda) per parallell query (#define ONE_LOGON 0), the oci call returns

        ORA-01076 multiple logons per process not yet supported

Does anyone know if there is a way - maybe by means of threads - to make the above described example run ?
I suppose, since threads run in one process, the restriction of ORA-01076 still prohibits that.
 */

/* OCI #include statements */
#include <stdio.h>
#include <string.h>
#include <memory.h>
#include <dos.h>

#include <oratypes.h>

/* LDA and CDA struct declarations */
#include <ocidfn.h>
#include <ociapr.h>

/* demo constants and structs */
#include <ocidem.h>

#define DEFER_PARSE 1 /* oparse flags */
#define NATIVE 1
#define VERSION_7 2
#define ARRAY_SIZE 1

#define OCI_EXIT_FAILURE 1 /* exit flags */
#define OCI_EXIT_SUCCESS 0

#define BLOCKED -3123
#define SUCCESS 0

/* ===== delayed Transaction handles ===== */ typedef sword (* t_op_fetch)(struct cda_def *cursor, ub4 nrows, sword cancel, sword exact);
typedef sword (* t_op_write)(struct cda_def *cursor, sword iters, sword rowoff);

typedef struct {
  t_op_fetch op_fetch;
    ub4 nrows;
    sword cancel;
    sword exact;
} t_op_fetch_h;

typedef struct {
  t_op_write op_write;
    sword iters;
    sword rowoff;
} t_op_write_h;

#define ONE_LOGON !0
#if ONE_LOGON

  Lda_Def   lda;                                 /*  login data area */
  ub1       hda[256];                            /*   host data area */

#endif

typedef union {
#if !ONE_LOGON

  Lda_Def   lda;                                 /*  login data area */
  ub1       hda[256];                            /*   host data area */


#endif


  Cda_Def   cda;                                 /* cursor data area */

  t_op_fetch_h op_fetch_h;
  t_op_write_h op_write_h;
  sword oerr;
  int done;
} t_op_h;
/* ===== delayed Transaction handles ===== */

struct emp_record                       /* employee data record */

{ int empno;

   char ename[11];
   char job[11];
   int mgr;
   char hiredate[10];
   float sal;
   float comm;
   int deptno;
};
typedef struct emp_record emp_record;

struct emp_record_indicators

{  short empno;                    /* indicator variable record */

   short ename;
   short job;
   short mgr;
   short hiredate;
   short sal;
   short comm;
   short deptno;
};
typedef struct emp_record_indicators emp_record_indicators;

struct emp_record_return_codes

{  ub2 empno;                    /* indicator variable record */

   ub2 ename;
   ub2 job;
   ub2 mgr;
   ub2 hiredate;
   ub2 sal;
   ub2 comm;
   ub2 deptno;
};
typedef struct emp_record_return_codes emp_record_return_codes;

emp_record              emp_records [ARRAY_SIZE];
emp_record_indicators   emp_rec_inds[ARRAY_SIZE];
emp_record_return_codes emp_rec_retc[ARRAY_SIZE];

void FillData(void)
{
 auto int i=0;
  emp_records[0].empno += 1; emp_rec_inds[0].empno = 0;
  strcpy(emp_records[0].ename,"a123456789"); emp_rec_inds[0].ename =
0;
  strcpy(emp_records[0].job ,"b234567890"); emp_rec_inds[0].job =
0;
  emp_records[0].mgr = 2; emp_rec_inds[0].mgr = 0;
  strcpy(emp_records[0].hiredate,"c345678901"); emp_rec_inds[0].hiredate = 0;
  emp_records[0].sal = 3.4; emp_rec_inds[0].sal = 0;
  emp_records[0].comm = 5.6; emp_rec_inds[0].comm = 0;
  emp_records[0].deptno = 40; emp_rec_inds[0].deptno = 0;

  #if ARRAY_SIZE >

1                                                                         
  emp_records[1].empno       = 11;              emp_rec_inds[1].empno   

= 0;
  strcpy(emp_records[1].ename,"d123456789"); emp_rec_inds[1].ename =
0;
  strcpy(emp_records[1].job ,"e234567890"); emp_rec_inds[1].job =
0;
  emp_records[1].mgr = 22; emp_rec_inds[1].mgr = 0;
  strcpy(emp_records[1].hiredate,"f345678901"); emp_rec_inds[1].hiredate = 0;
  emp_records[1].sal = 33.44; emp_rec_inds[1].sal = 0;
  emp_records[1].comm = 55.66; emp_rec_inds[1].comm = 0;
  emp_records[1].deptno = 30; emp_rec_inds[1].deptno = 0;
  #endif   

  for (i=0;i<ARRAY_SIZE;i++)
  {

    /* ... prompt user for data necessary */
    /* ... to fill emp_records and        */
    /* ... emp_records_inds arrays        */

  }
}

/* 
 * Function: err_report
 * Description: This routine prints out the most recent OCI error
 */

void err_report(t_op_h *op_h, Cda_Def *cda) {
 auto sword n;

 auto text msg[512];                      /* message buffer to hold

error text */
  if (cda->fc > 0) {
    printf("\n-- ORACLE error when processing OCI function %s \n\n", oci_func_tab[cda->fc]);
  } else {
    printf("\n-- ORACLE error\n");
  }
# if ONE_LOGON

  n = (sword)oerhms(&lda, cda->rc, msg, (sword) sizeof msg);
# else

  n = (sword)oerhms(&(op_h->lda), cda->rc, msg, (sword) sizeof msg);
# endif

  fprintf(stderr, "%s\n", msg);
}

/* 
 * Function: do_exit
 * Description: This routine exits with a status
 */

void do_exit(eword status)
{
  if (status == OCI_EXIT_FAILURE) {
    printf("\n Exiting with FAILURE status %d\n", status);   } else {
    printf("\n Exiting with SUCCESS status %d\n", status);   }
  exit(status);
}

void BindVars(t_op_h *op_h)
{
  if (op_h->oerr=obindps(&(op_h->cda), 1, (text *)":empno",

              strlen(":empno"), (ub1 *)&emp_records[0].empno, 
              sizeof(emp_records[0].empno), 
              SQLT_INT, (sword)0, (sb2 *) &emp_rec_inds[0].empno,
              (ub2 *)0, (ub2 *)0, (sb4) sizeof(emp_record),
              (sb4) sizeof(emp_record_indicators), 0, 0,
              0, (ub4 *)0, (text *)0, 0, 0))

    { err_report(op_h, &(op_h->cda)); do_exit(OCI_EXIT_FAILURE); }   if (op_h->oerr=obindps(&(op_h->cda), 1, (text *)":ename",

              strlen(":ename"), (ub1 *)emp_records[0].ename, 
              sizeof(emp_records[0].ename), 
              SQLT_STR, (sword)0, (sb2 *) &emp_rec_inds[0].ename,
              (ub2 *)0, (ub2 *)0, (sb4) sizeof(emp_record),
              (sb4) sizeof(emp_record_indicators), 0, 0,
              0, (ub4 *)0, (text *)0, 0, 0))

    { err_report(op_h, &(op_h->cda)); do_exit(OCI_EXIT_FAILURE); }   if (op_h->oerr=obindps(&(op_h->cda), 1, (text *)":deptno",

              strlen(":deptno"), (ub1 *)&emp_records[0].deptno, 
              sizeof(emp_records[0].deptno), 
              SQLT_INT, (sword)0, (sb2 *) &emp_rec_inds[0].deptno,
              (ub2 *)0, (ub2 *)0, (sb4) sizeof(emp_record),
              (sb4) sizeof(emp_record_indicators),
              0, 0, 0, (ub4 *)0, (text *)0, 0, 0))

    { err_report(op_h, &(op_h->cda)); do_exit(OCI_EXIT_FAILURE); } }

void DefinVars(t_op_h *op_h)
{
  if (op_h->oerr=odefin(&(op_h->cda), 1, (ub1 *) &emp_records[0].empno, (sword) sizeof(sword),

             (sword) INT_TYPE,
             (sword) -1, (sb2 *) &emp_rec_inds[0].empno, (text *) 0, -1,
-1,
             (ub2 *) 0, (ub2 *) &emp_rec_retc[0].empno))

    { err_report(op_h, &(op_h->cda)); do_exit(OCI_EXIT_FAILURE); } }

/* Function begin_sel(): This function selects the array of records passed to it. */
/* Retrieve the current maximum employee number. */ void begin_sel(t_op_h *op_h, int n, emp_record emp_records[], emp_record_indicators emp_rec_inds[])
{
 auto text *sqlstmt=(text *)"SELECT empno,ename,deptno INTO :empno,:ename,:deptno FROM emp " \

                             "WHERE deptno=11";

# if ONE_LOGON

  if (op_h->oerr=oopen(&(op_h->cda), &lda, (text *) 0, -1, -1, (text *)

0, -1)) {                /* open */

# else

  if (op_h->oerr=oopen(&(op_h->cda), &(op_h->lda), (text *) 0, -1, -1, (text *) 0, -1)) { /* open */
# endif

    err_report(op_h, &(op_h->cda)); do_exit(OCI_EXIT_FAILURE);   }
  if (op_h->oerr=oparse(&(op_h->cda), sqlstmt, (sb4) -1, DEFER_PARSE, (ub4) VERSION_7)) /* parse */

    { err_report(op_h, &(op_h->cda)); do_exit(OCI_EXIT_FAILURE); }

  DefinVars(op_h);   

  op_h->op_fetch_h.op_fetch=oexfet; op_h->op_fetch_h.nrows =1; op_h->op_fetch_h.cancel=FALSE; op_h->op_fetch_h.exact =FALSE;   op_h->oerr=oexfet(&(op_h->cda), (ub4) 1, FALSE, FALSE);   switch(op_h->oerr) {
    case BLOCKED:/* will come through here multiple times, print msg once */

      printf("Blocked - do something else while SQL stmt executes...\n");

      op_h->done=0;
      break;
    case SUCCESS:
      printf("selected rows...\n");
      op_h->done = !0;
      break;
    default:
      if (op_h->cda.rc == NO_DATA_FOUND) {
        emp_records[0].empno = 10;
      } else {
        err_report(op_h, &(op_h->cda)); do_exit(OCI_EXIT_FAILURE);    /*
get out of application */
      }
      op_h->done = !0;
      break;

  }
}

/* Function end_sel(): This function selects the array of records passed to it. */
/* Retrieve the current maximum employee number. */ void end_sel(t_op_h *op_h, int n, emp_record emp_records[], emp_record_indicators emp_rec_inds[])
{
  while (! op_h->done) {
    op_h->oerr= oexfet /* op_h->op_fetch_h.op_fetch */ (

                 &(op_h->cda),
                 op_h->op_fetch_h.nrows,
                 op_h->op_fetch_h.cancel,
                 op_h->op_fetch_h.exact
               );
    switch(op_h->oerr) {
      case BLOCKED:/* will come through here multiple times, print msg
once */
        printf("Blocked - do something else while SQL stmt
executes...\n"); sleep(1);
        break;
      case SUCCESS:
        printf("selected rows...\n"); op_h->done = !0;
        break;
      default:
        if (op_h->cda.rc == NO_DATA_FOUND) {
          emp_records[0].empno = 10;
        } else {
          err_report(op_h, &(op_h->cda)); do_exit(OCI_EXIT_FAILURE);   
/* get out of application */
        }
        op_h->done = !0;
        break;

    }
  }
}

/* Function begin_upd(): This function updates the array of records passed to it. */
void begin_upd(t_op_h *op_h, int n, emp_record emp_records[], emp_record_indicators emp_rec_inds[])
{
 auto text *sqlstmt=(text *) "UPDATE emp SET ename=:ename,deptno=:deptno WHERE empno=:empno";
# if ONE_LOGON

  if (op_h->oerr=oopen(&(op_h->cda), &lda, (text *) 0, -1, -1, (text *) 0, -1)) { /* open */
# else

  if (op_h->oerr=oopen(&(op_h->cda), &(op_h->lda), (text *) 0, -1, -1, (text *) 0, -1)) { /* open */
# endif

    err_report(op_h, &(op_h->cda)); do_exit(OCI_EXIT_FAILURE);   }
  if (op_h->oerr=oparse(&(op_h->cda), sqlstmt, (sb4)-1, DEFER_PARSE, (ub4)VERSION_7))

    { err_report(op_h, &(op_h->cda)); do_exit(OCI_EXIT_FAILURE); }   BindVars(op_h);
  op_h->op_write_h.op_write=oexn;op_h->op_write_h.iters=(sword) n;op_h->op_write_h.rowoff=(sword) 0;  

op_h->oerr=oexn(&(op_h->cda),op_h->op_write_h.iters,op_h->op_write_h.rowoff);   switch(op_h->oerr) {
    case BLOCKED:/* will come through here multiple times, print msg once */

      printf("Blocked - do something else while SQL stmt executes...\n");

      op_h->done=0;
      break;
    case SUCCESS:
      printf("updated rows...\n");
      op_h->done = !0;
      break;
    default:
      if (op_h->cda.rc == NO_DATA_FOUND) {
        emp_records[0].empno = 0;
      } else {
        err_report(op_h, &(op_h->cda)); do_exit(OCI_EXIT_FAILURE);    /*
get out of application */
      }
      op_h->done = !0;
      break;

  }
}

/* Function end_upd(): This function updates the array of records passed to it. */
void end_upd(t_op_h *op_h, int n, emp_record emp_records[], emp_record_indicators emp_rec_inds[])
{
  while (! op_h->done) {
    op_h->oerr=oexn /* op_h->op_write_h.op_write */ (

                 &(op_h->cda),
                 n /* op_h->op_write_h.iters */,
                 0 /* op_h->op_write_h.rowoff */
                 );
    switch(op_h->oerr) {
      case BLOCKED:/* will come through here multiple times, print msg
once */
        printf("Blocked - do something else while SQL stmt
executes...\n"); sleep(1);
        op_h->done=0;
        break;
      case SUCCESS:
        printf("updated rows...\n"); op_h->done = !0;
        break;
      default:
        if (op_h->cda.rc == NO_DATA_FOUND) {
          emp_records[0].empno = 0;
        } else {
          err_report(op_h, &(op_h->cda)); do_exit(OCI_EXIT_FAILURE);   
/* get out of application */
        }
        op_h->done = !0;
        break;

    }
  }
}

/* Function begin_ins(): This function inserts the array of records passed to it. */
void begin_ins(t_op_h *op_h, int n, emp_record emp_records[], emp_record_indicators emp_rec_inds[])
{
 auto text *sqlstmt=(text *) "INSERT INTO EMP (empno,ename,deptno) VALUES (:empno,:ename,:deptno)";
# if ONE_LOGON

  if (op_h->oerr=oopen(&(op_h->cda), &lda, (text *) 0, -1, -1, (text *) 0, -1)) { /* open */
# else

  if (op_h->oerr=oopen(&(op_h->cda), &(op_h->lda), (text *) 0, -1, -1, (text *) 0, -1)) { /* open */
# endif

    err_report(op_h, &(op_h->cda)); do_exit(OCI_EXIT_FAILURE);   }
  if (op_h->oerr=oparse(&(op_h->cda), sqlstmt, (sb4) -1, DEFER_PARSE, (ub4) VERSION_7))

    { err_report(op_h, &(op_h->cda)); do_exit(OCI_EXIT_FAILURE); }   BindVars(op_h);
  op_h->op_write_h.op_write=oexn;op_h->op_write_h.iters=(sword) n;op_h->op_write_h.rowoff=(sword) 0;  

op_h->oerr=oexn(&(op_h->cda),op_h->op_write_h.iters,op_h->op_write_h.rowoff);   switch(op_h->oerr) {
    case BLOCKED:/* will come through here multiple times, print msg once */

      printf("Blocked - do something else while SQL stmt executes...\n");

      op_h->done=0;
      break;
    case SUCCESS:
      printf("updated rows...\n"); op_h->done = !0;
      break;
    default:
      if (op_h->cda.rc == NO_DATA_FOUND) {
        emp_records[0].empno = 0;
      } else {
        err_report(op_h, &(op_h->cda)); do_exit(OCI_EXIT_FAILURE);    /*
get out of application */
      }
      op_h->done = !0;
      break;

  }
}

/* Function end_ins(): This function inserts the array of records passed to it. */
void end_ins(t_op_h *op_h, int n, emp_record emp_records[], emp_record_indicators emp_rec_inds[])
{
  while (! op_h->done) {
    op_h->oerr=oexn /* op_h->op_write_h.op_write */ (

                 &(op_h->cda),
                 n /* op_h->op_write_h.iters */,
                 0 /* op_h->op_write_h.rowoff */
                 );
    switch(op_h->oerr) {
      case BLOCKED:/* will come through here multiple times, print msg
once */
        printf("Blocked - do something else while SQL stmt
executes...\n"); sleep(1);
        op_h->done=0;
        break;
      case SUCCESS:
        printf("updated rows...\n"); op_h->done = !0;
        break;
      default:
        if (op_h->cda.rc == NO_DATA_FOUND) {
          emp_records[0].empno = 0;
        } else {
          err_report(op_h, &(op_h->cda)); do_exit(OCI_EXIT_FAILURE);   
/* get out of application */
        }
        op_h->done = !0;
        break;

    }
  }
}

void logon(t_op_h *op_h)
{
# if ONE_LOGON

  if ( ! *hda ) {
    if
(op_h->oerr=olog(&lda,hda,(text*)"scott/tiger_at_demo",-1,0,-1,0,-1,OCI_LM_DEF)) /* log on to the database */

      { printf("Logon failed: %25i\n", lda.rc); err_report(op_h, &lda); do_exit(OCI_EXIT_FAILURE); }
  }
# else

  if
(op_h->oerr=olog(&(op_h->lda),op_h->hda,(text*)"scott/tiger_at_demo",-1,0,-1,0,-1,OCI_LM_DEF)) /* log on to the database */

    { printf("Logon failed: %25i\n", op_h->lda.rc); err_report(op_h, &(op_h->lda)); do_exit(OCI_EXIT_FAILURE); }
# endif

}

/* 
 * Function: setup
 * Description: This routine does the necessary setup to execute the SQL
 *              statement. Specifically, it does the open, parse, bind
and
 *              define phases as needed.

 */
void setup(t_op_h *op_h)
{
# if ONE_LOGON

    if (op_h->oerr=onbset(&lda))

{                                        /* make the connection
non-blocking */
      err_report(op_h,(Cda_Def *)&lda);
do_exit(OCI_EXIT_FAILURE);                              
   
}                                                                           
    if (op_h->oerr=onbtst(&lda))
{                                        /* verify that it is
non-blocking */
      printf("connection is still blocking!!!\n"); err_report(op_h,

&lda); do_exit(OCI_EXIT_FAILURE);

    }
# else

    if (op_h->oerr=onbset(&(op_h->lda)))

{                                        /* make the connection
non-blocking */
      err_report(op_h,(Cda_Def *)&(op_h->lda));
do_exit(OCI_EXIT_FAILURE);                              
   
}                                                                           
    if (op_h->oerr=onbtst(&(op_h->lda)))
{                                        /* verify that it is
non-blocking */
      printf("connection is still blocking!!!\n"); err_report(op_h,

&(op_h->lda)); do_exit(OCI_EXIT_FAILURE);

    }
#endif

}

void logoff(t_op_h *op_h)
{
  /* log off from the database */
# if ONE_LOGON

  if (op_h->oerr=ologof(&lda)) {
    printf("Logoff failed: %25i\n", lda.rc);   }
# else

  if (op_h->oerr=ologof(&(op_h->lda))) {     printf("Logoff failed: %25i\n", op_h->lda.rc);   }
# endif

}

void begin_ta(t_op_h *op_h)
{
}

void end_ta(t_op_h *op_h)
{
# if ONE_LOGON

  op_h->oerr=ocom(&lda);                                   /* commit

transaction */
  if (op_h->oerr=oclose(&(op_h->cda))) { err_report(op_h, &lda); do_exit(OCI_EXIT_FAILURE); } /* close cursor */
# else

  op_h->oerr=ocom(&(op_h->lda));                                   /*

commit transaction */
  if (op_h->oerr=oclose(&(op_h->cda))) { err_report(op_h, &(op_h->lda)); do_exit(OCI_EXIT_FAILURE); } /* close cursor */
# endif

}

#define ONE_CURSOR !0
#if ONE_CURSOR

  t_op_h ta;

  #define sel_ta ta
  #define ins_ta ta
  #define upd_ta ta

#else

  t_op_h sel_ta, ins_ta, upd_ta;
#endif

int main(int argc, char *argv[])
{
  FillData();
  {
    logon(&sel_ta); logon(&ins_ta); logon(&upd_ta);     setup(&sel_ta); setup(&ins_ta); setup(&upd_ta);

    begin_ta(&sel_ta);

      /* fork queries in oracle Server */
      begin_sel(&sel_ta, ARRAY_SIZE,&emp_records, &emp_rec_inds);
      begin_ins(&ins_ta, ARRAY_SIZE,&emp_records, &emp_rec_inds);
      begin_upd(&upd_ta, ARRAY_SIZE,&emp_records, &emp_rec_inds);
      /* wait for query-results from oracle Server */
      end_sel  (&sel_ta, ARRAY_SIZE,&emp_records, &emp_rec_inds);
      end_ins  (&ins_ta, ARRAY_SIZE,&emp_records, &emp_rec_inds);
      end_upd  (&upd_ta, ARRAY_SIZE,&emp_records, &emp_rec_inds);

    end_ta(&sel_ta);

    logoff(&sel_ta); logoff(&ins_ta); logoff(&upd_ta);   }

  return 0;
}

Received on Wed Feb 02 2000 - 15:32:47 CST