SQL Bible [Electronic resources] نسخه متنی

Oracle Call Interface (OCI)

The Oracle Call Interface (OCI) is a set
of APIs (application programming interfaces) that allow a developer to
programmatically access Oracle RDBMS and submit and control all the phases of
the SQL statement execution.

The OCI provides a standard dynamic
runtime library, OCILIB, that an application can link to. Essentially, this
means that the embedded SQL is a thing of the past. The OCI interface can be
used by a number of third-generation programming languages like C/C++, COBOL,
and Fortran. To use fourth-generation languages (4GLs) like Java, Visual Basic,
C#, and so forth, a programmer must use some other interface like ODBC, JDBC,
or OLEDB. Even in that case, while the application is using its respective
interface driver, Oracle is using OCI.

Oracle tests client and server
compatibility with every new version released. There is always an issue of
compatibility between the version of the client software with that of the
server.
Table
16-5 shows compatibility between various versions of the Oracle
software. The EMS acronym refers to an extended maintenance contract, a special
arrangement for the existing customers — there is usually cut-off date after
which the system is no longer supported.

The typical OCI development process — that
is, the stages involved in building embedded SQL applications — is bypassed,
and an application can link into the OCI library directly.

Oracle OCI functions can be grouped by
functionality;
Table
16-6 shows main groups of functions as well as some examples from each
group.

To use OCI, a C/C++ program, at the very
minimum, should include an OCI.H header file and link with OCI.LIB. Here is
example of a very basic C program using OCI:

#include
<stdio.h> #include <stdlib.h> #include
<string.h> #include <oci.h> static OCIEnv *p_OCI_env; static
OCIError *p_OCI_error; static OCISvcCtx *p_OCI_svc; static OCIStmt *p_OCI_sql;
static OCIDefine *p_OCI_def = (OCIDefine *) 0; void main() { char p_data [30];
int ret; /* Initialize OCI */ ret = OCIInitialize((ub4) OCI_DEFAULT, (dvoid
*)0, (dvoid * (*)(dvoid *, size_t)) 0, (dvoid * (*)(dvoid *, dvoid *,
size_t))0, (void (*)(dvoid *, dvoid *)) 0 ); /* Initialize the environment */
ret = OCIEnvInit( (OCIEnv **) &p_OCI_env, OCI_DEFAULT, (size_t) 0, (dvoid
**) 0 ); /* Initialize handles */ ret = OCIHandleAlloc((dvoid *) p_OCI_env,
(dvoid **)&p_OCI_err, OCI_HTYPE_ERROR,(size_t) 0, (dvoid **) 0); ret =
OCIHandleAlloc((dvoid *)p_OCI_env, (dvoid **)&p_OCI_svc, OCI_HTYPE_SVCCTX,
(size_t) 0, (dvoid **) 0); /* Connect to the RDBMS */ ret = OCILogon(p_OCI_env,
p_OCI_err, &p_OCI_svc, "acme", 4, "acme", 4, "acme", 4); if (ret!= 0) { /*
handle the possible errors */ } /*Prepare the SQL statement */ ret =
OCIHandleAlloc( (dvoid *) p_OCI_env, (dvoid **)
&p_OCI_sql, OCI_HTYPE_STMT, (size_t) 0,(dvoid **) 0); ret =
OCIStmtPrepare(p_OCI_sql,p_OCI_err, "SELECT cust_name_s FROM customer WHERE
cust_id_n = 1", (ub4) 37, (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT); /* Define
the select list items */ ret = OCIDefineByPos(p_OCI_sql, &p_OCI_def,
p_OCI_err, 1, (dvoid *)&p_data,(sword) 20, SQLT_STR,(dvoid *) 0, (ub2
*)0,(ub2 *)0,OCI_DEFAULT); /* Execute the SQL statement */ ret =
OCIStmtExecute(p_OCI_svc, p_OCI_sql, p_OCI_err, (ub4) 1,(ub4) 0, (CONST
OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_DEFAULT); /* Fetch the data */
while (ret != OCI_NO_DATA) { printf("%s\n",p_data); ret =
OCIStmtFetch(p_OCI_sql, p_OCI_err, 1, 0, 0); } /* Disconnect */ ret =
OCILogoff(p_OCI_svc, p_OCI_err); ret = OCIHandleFree((dvoid *) p_OCI_sql,
OCI_HTYPE_STMT); /* Free handles */ ret = OCIHandleFree((dvoid *) p_OCI_svc,
OCI_HTYPE_SVCCTX); ret = OCIHandleFree((dvoid *) p_OCI_err, OCI_HTYPE_ERROR);
return; }

The sequence of the program above
follows:

OCIInitialize. Initializes the OCI
process environment.

OCIEnvInit. Allocates and
initializes an OCI environment handle.

OCIHandleAlloc. Returns a pointer to
an allocated and initialized handle.

OCILogon. Creates a simple logon
session.

OCIStmtPrepare. Prepares an
SQL or a PL/SQL statement for execution.

OCIDefineByPos. Associates an item
in a select-list with the type and output data buffer.

OCIStmtExecute. Associates an
application request with a server.

OCIStmtFetch. Fetches rows from a
query.

OCILogoff. Terminates a connection
and session created with
OCILogon().

OCIHandleFree. Explicitly
deallocates a handle and frees up resources.

The use of OCI programming interface
results in faster programs, though most often than not this advantage is
minuscule compared to the hurdles to create it. Most database applications
today use a 4GL like Java, Visual Basic, or PowerBuilder. If you need to
squeeze out the last drop of performance for your application, use OCI. Refer
to the Oracle documentation for more information.