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.

Tip 

Oracle supplies JDBC OCI drivers that provide access to the Oracle database using Java on the application side and Oracle's OCI on the other; it allows you to call OCI interface directly, without incurring the overhead of JDBC.

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.

Table 16-5: OCI Compatibility Between Different Versions of Server and Client Software

Client Version/Server Version

7.3.4

8.0.6

8.1.7

9.0.1

9.2

7.3.4

EMS

EMS

EMS

EMS

EMS

8.0.6

EMS

EMS

EMS

EMS

EMS

8.1.7

EMS

EMS

Supported

Supported

Supported

9.0.1

EMS

EMS

Supported

Supported

Supported

9.2

Not Supported

EMS

Supported

Supported

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.

Tip 

It is possible to mix OCI calls and Embedded SQL in a program. Please refer to the vendor documentation for more information.

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

Table 16-6: Selected Oracle OCI Functions

Functional Group

Examples

Uses

Relational Functions

OCIAttrGet(), OCIAttrSet(),
OCIBreak(), OCILogoff(),
OCILogon(), OCISessionBegin(),
OCISessionEnd(), OCIStmtExecute(),
OCIStmtFetch(), OCIStmtPrepare(),
OCIStmtSetPieceInfo(),
OCITransCommit(), OCITransPrepare(),
OCITransRollback(), OCITransStart()

Manage database access and processing SQL statements.

Navigational Functions

OCICacheFlush(), OCICacheFree(),
OCICacheRefresh(), OCICacheUnmark(),
OCICacheUnpin(), OCIObjectArrayPin(),
OCIObjectCopy(), OCIObjectExists(),
OCIObjectFlush(), OCIObjectFree(),
OCIObjectGetAttr(), OCIObjectRefresh(),
OCIObjectSetAttr(), OCIObjectUnmark(),
OCIObjectUnmarkByRef(),
OCIObjectUnpin(), OCITypeArrayByName(),
OCITypeArrayByRef(), OCITypeByName(),
OCITypeByRef()

Manipulate the records and objects retrieved from the Oracle RDBMS.

Data-type Mapping and Manipulation Functions

OCICollAppend(), OCICollAssign(),
OCICollAssignElem(), OCIDateGetDate(),
OCIDateGetTime(), OCIDateLastDay(),
OCIDateNextDay(), OCIDateSetDate(),
OCINumberIntPower(), OCINumberIsZero(),
OCIStringSize(), OCITableDelete(),
OCITableExists(), OCITableFirst(),
OCITableLast(), OCITableNext(),
OCITablePrev(), OCITableSize()

Manipulate the data attributes and ensure proper data casting.

External Procedure Functions

OCIExtProcAllocCallMemory(),
OCIExtProcRaiseExcp(),
OCIExtProcRaiseExcpWithMsg(),
OCIExtProcGetEnv()

Implement C-type callbacks from PL/SQ programs.

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.




SQL Bible
Microsoft SQL Server 2008 Bible
ISBN: 0470257040
EAN: 2147483647
Year: 2005
Pages: 208

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net