Transcript Introduction to Oracle Call Interface

http://martin.alikuvkoutek.cz
Introduction to
Oracle Call Interface
© Martin Cetkovský, 2005
What is OCI
an API that lets you access an Oracle
database server and control all
phases of SQL statement execution.
 supports the datatypes, calling
conventions, syntax, and semantics of
C and C++.

OCI vs. Precompilers
Advantage
 Better performance
 Smaller code length
 Direct access to the
inbuilt function
Disadvantage
 More complicated
development and
maintenance
Building an OCI Application
Parts of OCI

APIs


SQL access functions


manipulating data attributes of Oracle types.
Data loading functions


managing database access, processing SQL statements,
and manipulating objects retrieved from an server.
Datatype mapping and manipulation functions


design a scalable, multithreaded application that can
support large numbers of users securely.
loading data directly without using SQL statements.
External procedure functions

writing C callbacks from PL/SQL.
Encapsulated Interfaces

All the data structures encapsulated


opaque interfaces called handles.
Handle
an opaque pointer to a storage area
allocated by the OCI library
 stores context, connection, error or bind
information about a SQL or PL/SQL
statement.

Encapsulated Interfaces

Client




Application


allocates a certain type of handle
populates one or more of those handles through
well-defined interfaces
sends requests to the server using those
handles.
access the specific information contained in the
handle by using accessor functions.
OCI library

manages a hierarchy of handles.
Encapsulated benefit

Reduction


Improved application developer
productivity




amount of server side state information =>
reducing server side memory usage.
eliminating the need for global variables
making error reporting easier
providing consistency
Allowing changes

underlying structure changes without affecting
applications
Handle attributes
represent data stored in that handle
 readable by OCIAttrGet()
 writable by OCIAttrSet().

Handle attributes example

set the username in the session handle by writing
to the OCI_ATTR_USERNAME attribute
text username[] = "scott";
err = OCIAttrSet ((dvoid*) mysessp,
OCI_HTYPE_SESSION, (dvoid*) username,
(ub4) strlen(username),
OCI_ATTR_USERNAME,
(OCIError *) myerrhp);
Handle Type Examples
Description
C Type
Handle Type
OCI environment
handle
OCIEnv
OCI_HTYPE_ENV
OCI error handle
OCIError
OCI_HTYPE_ERROR
OCI service context
handle
OCISvcCtx
OCI_HTYPE_SVCCTX
OCI statement
handle
OCIStmt
OCI_HTYPE_STMT
OCI bind handle
OCIBind
OCI_HTYPE_BIND
OCI define handle
OCIDefine
OCI_HTYPE_DEFINE
OCI describe handle
OCIDescribe
OCI_HTYPE_DESCRIBE
Environment Handle


Defines a context for invoking all OCI
functions
Contains a memory cache



Serialize access to the cache


allows fast memory access
all memory allocation is done from this cache
if multiple threads try to allocate memory under
the same environment handle.
Multiple threads sharing a single
environment handle may block on access
to the cache
Environment handle
Passed as the parent parameter to
the OCIHandleAlloc() call to allocate
all other handle types.
 Bind and define handles are allocated
implicitly.

Error Handle




Passed as a parameter to most OCI calls.
Maintains information about errors
Can be passed to OCIErrorGet() to obtain
additional information
Allocating the error handle is one of the
first steps in an OCI application because
most OCI calls require an error handle as
one of its parameters.
Service Context and Associated
Handles

Defines attributes that determine the
operational context for OCI calls to a
server.
Service Context
Contains three handles and their
attributes
 A server handle identifies a
connection to a database. It
translates into a physical connection
in a connection-oriented transport
mechanism.

Service Context


A user session handle defines a user's roles
and privileges (also known as the user's
security domain), and the operational
context on which the calls execute.
A transaction handle defines the
transaction in which the SQL operations are
performed. The transaction context
includes user session state information,
including the fetch state and package
instantiation, if any.
Statement, Bind and Define
Handle



A statement handle is the context that
identifies a SQL or PL/SQL statement and
its associated attributes.
Information about input and output bind
variables is stored in bind handles.
Fetched data returned by a query (select
statement) is converted and retrieved
according to the specifications of the define
handles.
Statement, Bind and Define
Handle
OCI allocate Define Handle for each
output variable defined using
OCIDefineByPos()
 Bind and Define variables are freed
when new statement is ready on the
Statement Handle

OCI Environment Initialization
Each OCI function call is executed in
the context of an environment that is
created with the OCIEnvCreate() call.
 This call must be invoked before any
other OCI call.

Allocating Handles


Handles must be allocated using
OCIHandleAlloc() before passing them into
an OCI call
Exceptions




Environment Handle - OCIEnvCreate()
Bind and Degine Handle - special call,
OCIBindByPos() etc.
Thread Handle - OCIThreadHndInit()
Free handle by OCIHandleFree()
OCI Program Steps

Create OCI Environment


Alocate handles and descriptors


OCIEnvCreate(), OCIInitialize()
OCIHandleAlloc()
Initialize application, connection and
session

OCILogon(), OCISessionBegin()
OCI Program Step
Execute SQL
 Finalize connection



OCISessionEnd(), OCIServerDetach()
Free handles

OCIHandleFree()
Program Example
#include
#include
#include
#include
static
static
static
static
static
static
<stdio.h>
<stdlib.h>
<string.h>
<oci.h>
OCIEnv *p_env;
OCIError *p_err;
OCISvcCtx *p_svc;
OCIStmt *p_sql;
OCIDefine *p_dfn = (OCIDefine *) 0;
OCIBind *p_bnd = (OCIBind *) 0;
Program Example
/* Initialize OCI */
rc = OCIInitialize((ub4) OCI_DEFAULT, (dvoid
*)0,
(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 );
Program Example
/* 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);
Program Example
/* 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);
}
Program Example
/* Allocate & 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);
Program Example
/* 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);
Program Example
/* 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);
}
Program Example
/* Disconnect */
rc = OCILogoff(p_svc, p_err);
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);
Examples
%ORACLE_HOME%\Oci\Samples
 $ORACLE_HOME/rdbms/demo
 http://mates.ms.mff.cuni.cz/intra/ora
cle/doc/ora920doc/appdev.920/a9658
4/ociabdem.htm

Resources

Oracle Call Interface Programmer's
Guide – Chapter 1, 2


http://mates.ms.mff.cuni.cz/intra/oracle/doc/ora920doc/
appdev.920/a96584/toc.htm
Oracle Call Interface FAQ

http://www.orafaq.com/faqoci.htm