Oracle Call Interfaces

The Oracle Call Interfaces (OCI) is a set of low-level APIs (Application Programming Interface Calls) used to interact with the Oracle Database. It allows one to use operations like logon, execute, parse, fetch, etc. OCI programs are normally written in C or C++, although they can be written in almost any programming language.

Unlike with the Oracle Precompilers (like Pro*C and Pro*Cobol), OCI programs are not precompiled.

OCI compared to Pro*C

OCI is superior to Pro*C in the following ways:

• Performance is much better with OCI
• Reduced code size
• Direct access to built-in functions (no intermediate files or substitutions)
• Piecewise Operation on LONG fields (All LONG field problems are solved)
• In Pro*C one cannot dynamically allocate memory to be used as bind variables
• You cannot control the Pro*C precompiler to provide better and more compilable C-code

Common problems with OCI:

• OCI code is difficult to write and to maintain
• Very few people can write, let alone maintain OCI code

Programming

An OCI application program must do the following:

• Connect to one or more databases: call the OCILogon (olog, olon or orlon) routines
• Open the cursors needed by the program: use oexec, oexn, ofen or oftech calls.
• Process the SQL statements that are needed to perform the application's tasks.
• Close the cursors using the oclose routine.
• Disconnect from the databases: use ologoff to close an open connection to oracle.

Look at this very basic sample Oracle OCI8 program:

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

#pragma comment(lib, "d:\orant\oci80\lib\msvc\ora803.lib")

static OCIEnv           *p_env;
static OCIError         *p_err;
static OCISvcCtx        *p_svc;
static OCIStmt          *p_sql;
static OCIDefine        *p_dfn    = (OCIDefine *) 0;
static OCIBind          *p_bnd    = (OCIBind *) 0;

void main()
{
  int             p_bvi;
  char            p_sli[20];
  int             rc;
  char            errbuf[100];
  int             errcode;

  rc = OCIInitialize((ub4) OCI_DEFAULT, (dvoid *)0,  /* Initialize OCI */
          (dvoid * (*)(dvoid *, size_t)) 0,
          (dvoid * (*)(dvoid *, dvoid *, size_t))0,
          (void (*)(dvoid *, dvoid *)) 0 );

  /* Initialize evironment */
  rc = OCIEnvInit( (OCIEnv **) &p_env, OCI_DEFAULT, (size_t) 0, (dvoid **) 0 );

  /* Initialize handles */
  rc = OCIHandleAlloc( (dvoid *) p_env, (dvoid **) &p_err, OCI_HTYPE_ERROR,
          (size_t) 0, (dvoid **) 0);
  rc = OCIHandleAlloc( (dvoid *) p_env, (dvoid **) &p_svc, OCI_HTYPE_SVCCTX,
          (size_t) 0, (dvoid **) 0);

  /* Connect to database server */
  rc = OCILogon(p_env, p_err, &p_svc, "scott", 5, "tiger", 5, "d458_nat", 8);
  if (rc != 0) {
     OCIErrorGet((dvoid *)p_err, (ub4) 1, (text *) NULL, &errcode, errbuf, (ub4) sizeof(errbuf), OCI_HTYPE_ERROR);
     printf("Error - %.*sn", 512, errbuf);
     exit(8);
  }

  /* Allocate and prepare SQL statement */
  rc = OCIHandleAlloc( (dvoid *) p_env, (dvoid **) &p_sql,
          OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0);
  rc = OCIStmtPrepare(p_sql, p_err, "select ename from emp where deptno=:x",
          (ub4) 37, (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT);

  /* Bind the values for the bind variables */
  p_bvi = 10;     /* Use DEPTNO=10 */
  rc = OCIBindByName(p_sql, &p_bnd, p_err, (text *) ":x",
          -1, (dvoid *) &p_bvi, sizeof(int), SQLT_INT, (dvoid *) 0,
          (ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT);

  /* Define the select list items */
  rc = OCIDefineByPos(p_sql, &p_dfn, p_err, 1, (dvoid *) &p_sli,
          (sword) 20, SQLT_STR, (dvoid *) 0, (ub2 *)0,
          (ub2 *)0, OCI_DEFAULT);

  /* Execute the SQL statment */
  rc = OCIStmtExecute(p_svc, p_sql, p_err, (ub4) 1, (ub4) 0,
          (CONST OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_DEFAULT);

  while (rc != OCI_NO_DATA) {             /* Fetch the remaining data */
     printf("%sn",p_sli);
     rc = OCIStmtFetch(p_sql, p_err, 1, 0, 0);
  }

  rc = OCILogoff(p_svc, p_err);                           /* Disconnect */
  rc = OCIHandleFree((dvoid *) p_sql, OCI_HTYPE_STMT);    /* Free handles */
  rc = OCIHandleFree((dvoid *) p_svc, OCI_HTYPE_SVCCTX);
  rc = OCIHandleFree((dvoid *) p_err, OCI_HTYPE_ERROR);

  return;
}

Compile and link

Follow these steps to compile and link your OCI programs:

• Write your OCI program (e.g. myoci.c). You can find examples in the $ORACLE_HOME/rdbms/demo directory.

• Copy the make file $ORACLE_HOME/rdbms/demo/demo_rdbms.mk into the directory where your OCI program is located. You can also copy some of the sample OCI files (cdemo1.c, etc.) provided by Oracle to compile and link.

• Issue the following command:

make -f demo_rdbms.mk build EXE=cdemo1 OBJS=cdemo1.o

OCCI

Oracle also provides an enhanced OCI library - called the Oracle C++ Call Interface (OCCI). OCCI provides an object-oriented interface to the object-relational features of the Oracle database.

Wrapper classes

OCI is an extremely powerful API, but it can take quite some time to master. To ease the learning curve, several wrapper classes were developed to hide all the complexities and make OCI easier to use.

Some of the available libraries are:

• libsqlora, written by Kai Poitschke (open source)
• The C Driver for Oracle, written by Vincent Rogier (open source)

External links

• Oracle Call Interface Programmer's Guide
• OCI++ - Download free C++ OCI Wrapper
• Oracle and ODBC Template Library - C++ library based on templates
• libsqlora8 - Simple C-library to access Oracle via the OCI interface
• OCILIB - Open source library, written in C on top of OCI, that access Oracle Databases

Glossary of Terms
A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	#