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

اینجــــا یک کتابخانه دیجیتالی است

با بیش از 100000 منبع الکترونیکی رایگان به زبان فارسی ، عربی و انگلیسی

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

Alex Kriegel

| نمايش فراداده ، افزودن یک نقد و بررسی
افزودن به کتابخانه شخصی
ارسال به دوستان
جستجو در متن کتاب
بیشتر
تنظیمات قلم

فونت

اندازه قلم

+ - پیش فرض

حالت نمایش

روز نیمروز شب
جستجو در لغت نامه
بیشتر
لیست موضوعات
توضیحات
افزودن یادداشت جدید






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.

/ 207