DevOps, CI/CD and Automation

The oci14 sample that comes with the OCI ($ORACLE_HOME/rdbms/demo) shows how to do array inserts.

This sample uses OCI7 interface. What about OCI8? How to make bulk insert of records with SQLT_CHR type? I'll appreciate sample of code with OCIBindByName, OCIStmtEcecute and any additional OCI calls needed.

Here is a simplistic array (struct) example

Name: OCIArray
Category: OCI
Port: Generic
Description: This is a sample code that inserts arrays of ENAMEs and EMPNOs into EMP, using OCIBindByName and OCIBindArrayOfStruct. - Based on the sample code $ORACLE_HOME/rdbms/demo/oci81.c - Compile using Oracle makefile: make -f $ORACLE_HOME/rdbms/demo/demo_rdbms.mk build \ EXE=i_arrayofstruct OBJS=i_arrayofstruct.o -Run by entering three names and a valid department. -Check in sql-plus (scott/tiger): select empno, ename from emp;




--------------------------------------------------------------------------------

/*
DESCRIPTION
Based on oci81.c

changes:
- Working with a 3-element array ... enames and empnos.
- reduced number-of-columns in update to just ename, empno.
- Array of enames / empnos are inserted
- added length array: alenename.
- Changed OCIStmtExecute to include 3 iterations
- BindArrayOfStruct required for the characters binds.
- Does NOT use the HandleAlloc to provide user memory

Demonstrates creating a connection, a session and executing some SQL.

*/

/* HARD-CODED VALUES for lengths (ename, deptname) , array size (3) */

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <oci.h>

static text username = (text ) "SCOTT";
static text password = (text ) "TIGER";

/* Define SQL statements to be used in program. */
static text insert = (text )"INSERT INTO emp(empno, ename)\
VALUES (:empno, :ename)";
static text seldept = (text )"SELECT dname FROM dept WHERE deptno = :1";

static OCIEnv *envhp;
static OCIError *errhp;

static void checkerr(/*_ OCIError *errhp, sword status _*/);
static void cleanup(/*_ void _*/);
static void myfflush(/*_ void _*/);
int main(/*_ int argc, char *argv[] _*/);

static sword status;

int main(argc, argv)
int argc;
char *argv[];
{
int j;
sword empno[3]={111,222,333}, sal, deptno;
sword len, len2, rv, dsize, dsize2;
sb4 enamelen = 10;
sb4 deptlen = 14;
sb2 sal_ind, job_ind;
sb2 db_type, db2_type;
sb1 name_buf[20], name2_buf[20];
text cp, ename[3][10+2], job, *dept;
sb2 ind[2]; /* indicator */
ub2 alenename[3];
ub2 alen[2]; /* actual length */
ub2 rlen[2]; /* return length */

OCIDescribe dschndl1 = (OCIDescribe ) 0,
dschndl2 = (OCIDescribe ) 0,
dschndl3 = (OCIDescribe ) 0;

OCISession authp = (OCISession ) 0;
OCIServer *srvhp;
OCISvcCtx *svchp;
OCIStmt *inserthp,
*stmthp,
*stmthp1;
OCIDefine defnp = (OCIDefine ) 0;

OCIBind bnd1p = (OCIBind ) 0; /* the first bind handle */
OCIBind bnd2p = (OCIBind ) 0; /* the second bind handle */
OCIBind bnd3p = (OCIBind ) 0; /* the third bind handle */
OCIBind bnd4p = (OCIBind ) 0; /* the fourth bind handle */
OCIBind bnd5p = (OCIBind ) 0; /* the fifth bind handle */
OCIBind bnd6p = (OCIBind ) 0; /* the sixth bind handle */

(void) OCIInitialize((ub4) OCI_DEFAULT, (dvoid *)0,
(dvoid * (*)(dvoid *, size_t)) 0,
(dvoid * (*)(dvoid *, dvoid *, size_t))0,
(void (*)(dvoid *, dvoid *)) 0 );

(void) OCIEnvInit( (OCIEnv **) &envhp, OCI_DEFAULT, (size_t) 0,
(dvoid **) 0 );

(void) OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &errhp, OCI_HTYPE_ERROR,
(size_t) 0, (dvoid **) 0);

/* server contexts */
(void) OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &srvhp, OCI_HTYPE_SERVER,
(size_t) 0, (dvoid **) 0);

(void) OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &svchp, OCI_HTYPE_SVCCTX,
(size_t) 0, (dvoid **) 0);

(void) OCIServerAttach( srvhp, errhp, (text *)"", strlen(""), 0);

/* set attribute server context in the service context */
(void) OCIAttrSet( (dvoid *) svchp, OCI_HTYPE_SVCCTX, (dvoid *)srvhp,
(ub4) 0, OCI_ATTR_SERVER, (OCIError *) errhp);

(void) OCIHandleAlloc((dvoid *) envhp, (dvoid **)&authp,
(ub4) OCI_HTYPE_SESSION, (size_t) 0, (dvoid **) 0);

(void) OCIAttrSet((dvoid *) authp, (ub4) OCI_HTYPE_SESSION,
(dvoid *) username, (ub4) strlen((char *)username),
(ub4) OCI_ATTR_USERNAME, errhp);

(void) OCIAttrSet((dvoid *) authp, (ub4) OCI_HTYPE_SESSION,
(dvoid *) password, (ub4) strlen((char *)password),
(ub4) OCI_ATTR_PASSWORD, errhp);

checkerr(errhp, OCISessionBegin ( svchp, errhp, authp, OCI_CRED_RDBMS,
(ub4) OCI_DEFAULT));

(void) OCIAttrSet((dvoid *) svchp, (ub4) OCI_HTYPE_SVCCTX,
(dvoid *) authp, (ub4) 0,
(ub4) OCI_ATTR_SESSION, errhp);

checkerr(errhp, OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &stmthp,
OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0));

checkerr(errhp, OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &stmthp1,
OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0));



checkerr(errhp, OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &inserthp,
OCI_HTYPE_STMT, 0,
(dvoid **) 0));


checkerr(errhp, OCIStmtPrepare(stmthp, errhp, insert,
(ub4) strlen((char *) insert),
(ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT));

checkerr(errhp, OCIStmtPrepare(stmthp1, errhp, seldept,
(ub4) strlen((char *) seldept),
(ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT));


/* Bind the placeholders in the INSERT statement. */
if ((status = OCIBindByName(stmthp, &bnd1p, errhp, (text *) ":ENAME",
6, (dvoid *) ename,
enamelen+1, SQLT_STR, (dvoid *) 0,
(ub2 *) alenename, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT)) ||

(status = OCIBindByName(stmthp, &bnd5p, errhp, (text *) ":EMPNO",
-1, (dvoid *) &empno[0],
(sword) sizeof(empno[0]), SQLT_INT, (dvoid *) 0,
(ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT)))
{
checkerr(errhp, status);
cleanup();
return OCI_ERROR;
}

if (status = OCIBindArrayOfStruct(bnd1p, errhp, sizeof(ename[0]), 0, 0, 0)){
checkerr(errhp, status);
cleanup();
return OCI_ERROR;
} ;

if (status = OCIBindArrayOfStruct(bnd5p, errhp, sizeof(empno[0]), 0, 0, 0)){
checkerr(errhp, status);
cleanup();
return OCI_ERROR;
} ;


/* Bind the placeholder in the "seldept" statement. */
if (status = OCIBindByPos(stmthp1, &bnd6p, errhp, 1,
(dvoid *) &deptno, (sword) sizeof(deptno),SQLT_INT,
(dvoid *) 0, (ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT))
{
checkerr(errhp, status);
cleanup();
return OCI_ERROR;
}

/* Allocate the dept buffer now that you have length. */
/* the deptlen should eventually get from dschndl3. */
deptlen = 14;
dept = (text *) malloc((size_t) deptlen + 1);

/* Define the output variable for the select-list. */
if (status = OCIDefineByPos(stmthp1, &defnp, errhp, 1,
(dvoid *) dept, deptlen+1, SQLT_STR,
(dvoid *) 0, (ub2 *) 0, (ub2 *) 0, OCI_DEFAULT))
{
checkerr(errhp, status);
cleanup();
return OCI_ERROR;
}

for(j=0;j<3;j++)alenename[j] = enamelen+1;

for (;;) {
/* Prompt for employee name. Break on no name. */
printf("\nEnter employee name (or CR to EXIT): ");
fgets((char *) &ename[0], (int) enamelen+1, stdin);

cp = (text *) strchr((char *) ename[0], '\n');
if (cp == ename[0]) {
printf("Exiting... \n");
cleanup();
return OCI_SUCCESS;
}
if (cp)
*cp = '\0';
else {
printf("Employee name may be truncated.\n");
myfflush();
}
printf("\nEnter employee name1 ");
fgets((char *) &ename[1], (int) enamelen+1, stdin);
printf("\nEnter employee name2 ");
fgets((char *) &ename[2], (int) enamelen+1, stdin);
printf(" \nDiagnostic of addresses of chars:\n");
printf(" ename=%s addr=%x\n",ename, &ename);
printf(" ename[0]=%s addr=%x\n",ename[0], &ename[0]);
printf(" ename[1]=%s addr=%x\n", ename[1], &ename[1]);
printf(" ename[2]=%s addr=%x\n", ename[2], &ename[2]);
printf(" end Diagnostic \n\n\n");

do {
printf("Enter employee dept: ");
scanf("%d", &deptno);
myfflush();
if ((status = OCIStmtExecute(svchp, stmthp1, errhp, (ub4) 1, (ub4) 0,
(CONST OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_DEFAULT))
&& (status != OCI_NO_DATA))
{
checkerr(errhp, status);
cleanup();
return OCI_ERROR;
}
if (status == OCI_NO_DATA)
printf("The dept you entered doesn't exist.\n");
} while (status == OCI_NO_DATA);

/*
* Increment empno by 10, and execute the INSERT
* statement. If the return code is 1 (duplicate
* value in index), then generate the next
* employee number.
*/
if ((status = OCIStmtExecute(svchp, stmthp, errhp, (ub4) 3, (ub4) 0,
(CONST OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_DEFAULT))
&& status != 1)
{
checkerr(errhp, status);
cleanup();
return OCI_ERROR;
}
while (status == 1) {
empno[0] += 10;
empno[1] = empno[0]+1;
empno[2] = empno[1]+1;
if ((status = OCIStmtExecute(svchp, stmthp, errhp, (ub4) 3, (ub4) 0,
(CONST OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_DEFAULT))
&& status != 1)
{
checkerr(errhp, status);
cleanup();
return OCI_ERROR;
}
} /* end for (;;) */

/* Commit the change. */
if (status = OCITransCommit(svchp, errhp, 0)) {
checkerr(errhp, status);
cleanup();
return OCI_ERROR;
}
printf("\n\n%s added to the %s department as employee number %d\n",
ename[2], dept, empno[2]);
}
}


void checkerr(errhp, status)
OCIError *errhp;
sword status;
{
text errbuf[512];
sb4 errcode = 0;

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:
(void) OCIErrorGet((dvoid *)errhp, (ub4) 1, (text *) NULL, &errcode,
errbuf, (ub4) sizeof(errbuf), OCI_HTYPE_ERROR);
(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;
}
}


/*
* Exit program with an exit code.
*/
void cleanup() {
if (envhp)
(void) OCIHandleFree((dvoid *) envhp, OCI_HTYPE_ENV);
return;
}


void myfflush() {
eb1 buf[50];
fgets((char *) buf, 50, stdin);
}

Thank you. There is important limitation in presented sample: data - for example strings - has to be placed in continuos memory segment, like static array. For example: lets have 1000 strings of 10 characters and 1 of 1000 characters. If I want to make bulk insert of them, I'll have to allocate memory for 1001 * 1000 characters, not for 1000 * 10 + 1000 only. How to deal with it?

One artificial way - pass all the bind buffers to a "custom" pl/sql procedure and do the inserts on the server side. Thus, you donot incur multiple function round-trip overhead.
For this particular eg. the procedure can take one varchar2 IN variable for the n*10 class of strings and one varchar2 IN variable for the n*1000 class of strings.

However, the right way to handle these would be to have a proper interface in the client itself, to probably do "dynamic" binds(current BindDynamic is for piecewise support and not suitable here) or some kind of a scatter/gather bind/define.