Oracle Call Interface FAQ

What is the Oracle Call Interface?
The Oracle Call Interface (OCI) is a set of low-level APIs to perform Oracle database operations (eg. logon, execute, parse, fetch records).
OCI programs are normally written in C or C++, although they can be written in almost any programing language. Unlike with the Oracle Precompilers (like Pro*C), OCI programs are not precompiled.
Back to top of file


Should one use OCI or the Oracle Precompilers?
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.
 etc...
Common problems with OCI:
 OCI code is difficult to write and to maintain
 Very few people can write, let alone maintain OCI code
 etc...
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.
For more information about Pro*C and other language precompilers, read the Oracle Precompilers FAQ.
Back to top of file


How does one write an OCI program?
Look at this very basic sample Oracle OCI8 program:
#include
#include
#include
#include

#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 - %.*s\n", 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("%s\n",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;
}
Back to top of file


How does one compile and link an OCI program?

1. Write an OCI program (e.g. myoci.c). You can find examples in the $ORACLE_HOME/rdbms/demo directory.
2. Copy the make file $ORACLE_HOME/rdbms/demo/demo_rdbms.mk into the directory where your OCI program is. You can also copy some of the sample OCI files (cdemo1.c, etc.) provided by Oracle to compile and link.
3. Issue the following command make -f demo_rdbms.mk build EXE=cdemo1 OBJS=cdemo1.o
Back to top of file


Where can I get more info about OCI?

 Oracle Call Interface Programmer's Guide
 OCI++ - Download free C++ OCI Wrapper
 Oracle Call Interface Template Library
Back to top of file