ORACLE C

1
ORACLE C CALL INTERFACE(OCCI) Shankar Iyer,
Oracle India.
2
OCCI Todays Agenda

• Session I
• - Introduction
• - SQL/PLSQL Execution
• - Data classes
• Session II
• - Object Features
• - MetaData access

• Session III
• - Scalability Features
• - 10i features
• Session IV
• - Interoperability with OCI
• - OCCI Performance Tuning
• - Demo application
• - Q A

3
OCCI Introduction

• C API to access Oracle database
• Designed as small set of well encapsulated
  classes and interfaces for ease of use
• Extensive features for relational access,
  object-relational access and scalability
• Introduced in 9i, growing customer base

4
OCCI - Benefits

• Easy to learn and use, similar to JDBC in
  relational access
• Based on Standard C and object oriented design
• Higher productivity and quality in application
  development
• Develop client-server, middle-tier and complex
  object modeling applications
• Continuing enhancements by Oracle to add more
  features

5
OCCI Features

• Complete SQL/PLSQL execution support
• Scalability options to serve increasing number of
  users and requests
• Seamless interface to manipulate objects of
  user-defined types as C class instances
• Support for all Oracle data types and large
  object(LOB) types
• Database metadata access

6
OCCI Building an application
Application source files (use OCCI API and
classes)
OCCI API header files
OTT generated C class headers
C compiler
OTT generated C class implementations

• OCCI header files
• occi.h
• occiCommon.h
• occiControl.h
• occiData.h
• occiObjects.h
• OCCI library
• libocci.so/libocci.a/oraocci9.dll

OCCI library (static or dynamic)
Linker
Application
7
OCCI Application Initialization

• A OCCI application is initialized by creating an
  Environment class instance
• A Environment instance specifies -
• - Application modes OBJECT/THREADED/MUTEXED etc
• - object cache settings
• - memory heap for OCCI classes
• The Environment is the base for creating
  connections for further database access
• To create an Environment, call createEnvironment
  static method of Environment class

8
OCCI Initialize Environment Examples

• Creating default Environment

//include 1 header file for all OCCI
classes/interfaces include ltocci.hgt //create
Environment Environment env Environmentcreate
Environment() //use the Environment instance to
create connections, //database access //termina
te Environment by calling static
method //EnvironmentterminateEnvironment Environ
mentterminateEnvironment(env)

• Creating Environment for object access

//create Environment specify OBJECT
mode Environment env EnvironmentcreateEnviro
nment(EnvironmentOBJECT)
9
OCCI Control classes
Environment
Create
Create
Create
StatelessConnectionPool
Connection
ConnectionPool
Get
Get
Get
Create
MetaData
Statement
Get
Execute
ResultSet
10
OCCI Connecting to database

• A user connection is represented by a Connection
  class instance
• Call the createConnection method of Environment
  class to create a connection
• - Connection EnvironmentcreateConnection(
• const string userName, const string password,
• const string connectString)
• Use the Connection object to access data, execute
  SQL commands, work with objects
• End connection by calling EnvironmentterminateCo
  nnection
• Advanced mechanisms like connection pooling,
  session pooling, proxy authentication also
  supported

11
OCCI Create Connection - Example
//First need Environment Environment env
EnvironmentcreateEnvironment() Connection
connenv-gtcreateConnection(scott,tiger,) /
/3rd parameter is db name/TNS alias ..//database
access use the Connection object .. .. //logoff
and terminate connection env-gtterminateConnection(
conn)
12
OCCI Executing SQL/PLSQL

• Execute DDL/DML statements, SELECT queries,
  PL/SQL blocks and retrieve results
• Statement class for preparing executing
  SQL/PLSQL statements, getting PL/SQL OUT results
• ResultSet class for fetching SELECT query results
• Uniform interface for binding and getting values
  of all data types
• - setXXX methods of Statement
• - getXXX methods of Statement ResultSet
• Data type conversions automatically handled by
  OCCI

13
OCCI Executing SQL Usage

• Create a Statement object with ConnectioncreateS
  tatement()
• Specify SQL command(DDL/DML/query) as argument to
  -
• ConnectioncreateStatement(string sql)
• StatementsetSQL(string sql)
• Statementexecute(string sql) - can be used
  for any SQL, returns status
• StatementexecuteUpdate(string sql) -
  returns Insert/Update/Delete count
• StatementexecuteQuery(string sql) -
  returns ResultSet
• Use setXXX methods of Statement to pass input
  bind values
• Execute the SQL statement using one of the
  execute methods of Statement
• For SELECT queries, fetch the results using
  ResultSet class object

14
OCCI Executing SQL Examples

• Simple DML Insert

//createStatement() on Connection class gives a
Statement //instance Statement stmt
conn-gtcreateStatement( insert into Dept(Deptno,Dn
ame, Loc) values (1, ACCOUNTS, ZONE1
) //executeUpdate for all INSERT/UPDATE/DELETE s
tmt-gtexecuteUpdate() conn-gtterminateStatement(stm
t)

• DML Insert with bind

Statement stmt conn-gtcreateStatement( insert
into Emp(EmpNo,Ename) values(1, 2) ) //1 and
2 are bind placeholders int empno 2 string
empname JOHN W //first parameter is bind
position, second is value stmt-gtsetInt(1, empno)
stmt-gtsetString(2, empname) stmt-gtexecuteUpdate(
)
15
OCCI Executing SELECT Examples

• Executing Select queries and fetching results

Statement stmt conn-gtcreateStatement( select
Empno, Ename, Sal from Emp where Hiredate gt
1) //automatically converted to
Date stmt-gtsetString(1, 01-JAN-1987) //execute
Query returns a ResultSet ResultSet rs
stmt-gtexecuteQuery() //ResultSetnext fetches
rows and returns FALSE //when no more rows while
(rs-gtnext() true) //get values using the
getXXX methods of ResultSet empno
rs-gtgetInt(1) empname rs-gtgetString(2)
empsalary rs-gtgetFloat(3) stmt-gtcloseResultSe
t(rs)//to free resources
16
OCCI DML on multiple rows using Iterations

• DML(INSERT/UPDATE/DELETE) of multiple rows in
  single roundtrip

Statement stmt conn-gtcreateStatement(insert
into emp (empno, ename) values (1,
2)) //specify max iterations stmt-gtsetMaxIterat
ions(10)//number of rows //specify maximum data
size for types like string stmt-gtsetMaxParamSize(2
, 100) //set values and add iterations stmt-gtsetI
nt(1, 1001) stmt-gtsetString(2,
JOHN) stmt-gtaddIteration() stmt-gtsetInt(1,
1002) stmt-gtsetString(2, JOE) stmt-gtaddIterati
on() //repeat iterations,do not call
addIteration after last set stmt-gtexecuteUpdate()
//will insert 10 rows in single trip
17
OCCI SQL Execution Streaming

• Bind/fetch data in pieces, typically used for
  LONG columns
• Set binary/character streaming mode on
  Statement/ResultSet and use getStream() to get
  Stream
• Use read/write methods of Stream

Statement stmt conn-gtcreateStatement(Select
LongCol) ResultSet rs rs-gtexecuteQuery() //
indicate character streaming mode rs-gtsetCharacter
StreamMode(1, 100000)//col1,maxsize100000 while
(rs-gtnext()) Stream col
rs-gtgetStream(1) char buffer1024 while
(col-gtreadBuffer(buffer, 1024) ! -1)
//process data //similary use
StreamwriteBuffer(),writeLastBuffer()
18
OCCI Executing PL/SQL

• Create a Statement object and specify PL/SQL
  block to be executed
• Pass any input arguments(IN and IN/OUT) to the
  PLSQL function/procedure/block by setXXX methods
  of Statement
• Specify any OUT parameters by StatementregisterO
  utParam, sizes of OUT parameters by
  StatementsetMaxParamSize
• Execute the PL/SQL block using Statementexecute(
  )
• Retrieve function result/OUT/IN OUT parameters by
  getXXX methods of Statement

19
OCCI PLSQL Examples

• Calling PL/SQL function/procedure

//PLSQL function function CalculateBonus(EmpNo
IN Number, // EmpStatus
IN OUT VARCHAR2, //
Bonus OUT Number) RETURN VARCHAR2 //call function
using anonymous block Statement stmt
conn-gtcreateStatement( begin 1
CalculateBonus( 2, 3, 4)
end) //bind position 1 is the functions
return value stmt-gtsetInt(2, 100) //IN
parameter stmt-gtsetString(3, Active) //IN OUT
parameter //call registerOutParam for each OUT
parameter stmt-gtregisterOutParam(1, OCCISTRING,
1000)//functions return value stmt-gtsetMaxParamS
ize(1, 100)//setMaxParamSize for STRING
types stmt-gtregisterOutParam(4,
OCCIFLOAT) stmt-gtexecute() //use getXXX methods
of Statement to get OUT parameters, return
value string msg stmt-gtgetString(1) //function
return value string newstatus
stmt-gtgetString(3)//IN OUT parameter float bonus
stmt-gtgetFloat(4) //OUT parameter
20
OCCI SQL/PLSQL Data buffer interface

• To provide and receive data in user buffers for
  Statement and ResultSet
• Bypasses OCCI and C specific datatypes like
  string/Date etc, minimizing data copies
• Used in array inserts(any DML) and array fetches
• - array DML StatementexecuteArrayUpdate(int
  nrows)
• - array fetch ResultSetnext(int nrows)
• setXXX and getXXX methods should not be used if
  data buffer interface is used for a column

21
OCCI SQL/PLSQL Array fetch example
char enames1020//10 elements of length 20
chars each ub2 elens10//to receive the length
of each element Statement stmt
conn-gtcreateStatement("Select Ename from
EMP") ResultSet rs stmt-gtexecuteQuery() //Re
sultSetsetDataBuffer(colIndex,buffer,type,elemsi
ze, //lengths,ind,rc) //OCCI_SQLT_STR for char
buffer rs-gtsetDataBuffer(1, enames,
OCCI_SQLT_STR, 20, elens, NULL, NULL) rs-gtnext(5
) //will fetch 5 rows in enames //do not call
rs-gtgetString(1)
22
OCCI SQL/PLSQL Array insert example
int empnos5 empnos0 801 empnos1
802 stmt conn-gtcreateStatement("insert into
emp (empno) values (1)") //StatementsetDataBuf
fer(colIndex,buffer,type,elemsize, //lengths,ind,r
c) //OCCIINT for int datatype stmt-gtsetDataBuffer(
1, empnos, OCCIINT, sizeof(int), NULL, NULL,
NULL) stmt-gtexecuteArrayUpdate(2) //inserts 2
rows conn-gtcommit()
23
OCCI Error Handling

• OCCI uses C exception mechanism to return all
  errors(in Oracle client/server or C STL)
• Applications should have a try-catch block to
  handle exceptions
• The exception object thrown is of SQLException
  class if error is in Oracle
• SQLException is derived from standard C
  exception class
• getErrorCode and getMessage methods of
  SQLException return Oracle error information

24
OCCI Error Handling Example

• Handling Oracle and C STL errors separately

try ResultSet rs stmt-gtexecuteQuery()
while (rs-gtnext()) . catch (SQLException
oraex) //Oracle/OCCI errors int errno
oraex-gtgetErrorCode()//returns the ORA number
string errmsg oraex-gtgetMessage() //more
application error handling catch (exception
ex) //any other C/STL error cout ltlt Error
ltlt ex.what() ltlt endl
25
OCCI Data classes

• Classes for using Oracle data types

• Easy to use with comprehensive functionality
• Used with Statement and ResultSet to insert/fetch
  values of these types

26
OCCI Data classes - Number
// inserting a number Number bignum bignum.fromTe
xt(env, 43613923333.233, 99999999999999.999)
stmt-gtsetNumber(1, bignum)//stmt is
Statement // Create a Number from a double value
double value 2345.123 Number nu1 (value) //
Some common Number methods Number abs
nu1.abs() / absolute value / Number sin
nu1.sin() / sine / // Cast operators can be
used long lnum (long) nu1 // Unary
increment/decrement prefix/postfix notation nu1
27
OCCI Data classes Date
// inserting a Date // Create a Date object and
bind it to the statement Date edate(env, 2000, 9,
3, 23, 30, 30) stmt-gtsetDate(1, edate)//stmt is
Statement // fetching and displaying a Date Date
odate rs-gtgetDate(1) //rs is ResultSet Cout ltlt
odate.toText(DD-MON-YYYY, GERMAN) //
interval between 2 dates IntervalDS diff diff
odate.daysBetween(edate)
28
OCCI Large Object(LOB) support

• Support for BLOB, CLOB/NCLOB and BFILE data types
• Simple, consistent, object-oriented interface
• OCCI classes - Bfile, Clob, Blob
• Used in relational insert/fetch(column of LOB
  type) or as object attributes -
• - StatementsetBfile(int column, Bfile
  value)
• - StatementsetClob(int column, Clob value)
• - StatementsetBlob(int column, Blob value)
• - Bfile Statement/ResultSetgetBfile(int
  column)
• - Clob Statement/ResultSetgetClob(int
  column)
• - Blob Statement/ResultSetgetBlob(int
  column)
• Streamed read/write support

29
OCCI LOB support Examples

• Reading a BLOB

Statement stmt conn-gtcreateStatement(select
resume from emp) ResultSet rs
stmt-gtexecuteQuery() rs-gtnext() //fetch 1
row //Blob ResultSetgetBlob(int column) Blob
resume rs-gtgetBlob(1) char buffer100 int
bytesRead, offset 1 while ((bytesRead
resume.read(100, buffer, 100, offset)) gt 0)
//process data read //move offset to read
next offset offset bytesRead
30
OCCI LOB support - Examples

• Inserting/updating a CLOB

Statement stmt conn-gtcreateStatement( insert
into documents values (1) ) //create empty
Clob Clob doccontents(conn) doccontents.setEmpty(
) //1st insert will create LOB locator in
database stmt-gtsetClob(1, doccontents) stmt-gtexec
uteUpdate() conn-gtcommit() //now select the
Clob again and add contents stmt
conn-gtcreateStatement(select body from documents
for update) ResultSet rs stmt-gtexecuteQuery()
doccontents rs-gtgetClob(1) doccontents.write
(100, buffer, 100, 1) conn-gtcommit()
31
OCCI Objects

• Access data from tables as C class instances
• Automatic and intuitive mapping of object types
  to C class, no user code
• Single data model for application and database
• Develop complex and powerful object-oriented
  applications using Oracles object features, OCCI
  and C
• Client-side cache for database transparency and
  performance

32
OCCI Objects Oracle basics

• Create object types to model application entities
  -
• create type employee as object
• (
• empno number(5),
• name varchar2(30),
• hiredate date
• )
• Object types can be used to create object tables
  or as column types in relational table -
• create table emptab of employee -- object table
• create table person (name varchar2, age number,
  addr Address) -- column
• Terminology -
• - Object a row/item in a object table
• - Reference logical pointer/address of a
  object.
• Every object in a object table has a reference

33
OCCI Objects Oracle basics

• REF datatype represents a reference to a object
  in a object table
• create type employee as object
• (
• Dept REF Department, -- Reference to
  another object
• )
• Use REFs to model relationships (one-to-one or
  one-to-many)
• - create type LineItem as object
• (
• Item REF Product -- reference to
  Product in Products table
• )
• - create LineItems as varray(1000) of REF
  LineItem -- collection type
• create type PurchaseOrder as object
• (
• ItemList LineItems -- references to all
  LineItems of this order
• In a client application, references are used to
  fetch objects and navigate object relationships

34
OCCI Objects Access methods

• Navigational access -
• - no SQL
• - Access database objects through references
  (REFs)
• - create/modify/delete/fetch persistent
  objects with normal C code
• - complete transaction support
• - objects are maintained in the client cache
• Associative access -
• - use SQL
• - create/fetch object values with
  StatementsetObject/StatementgetObject
• and ResultSetgetObject
• - no caching of objects since they are
  values
• Objects in a object table are accessed with
  navigational access. Columns of object types can
  be accessed with associative access

35
OCCI Object Type Translator(OTT)

• Generates C class representations for Oracle
  object types
• Application uses the C classes for creating
  accessing persistent and transient objects
• Object attributes are declared as member
  variables, with optional get/set access methods
• Simple format input specification file
• Generated code to be compiled linked with the
  application

36
OCCI OTT Data type mappings
37
OCCI OTT Example generated class
//Database type - create type employee as
object ( empno number(5), name
varchar2(30), hiredate date ) //C class
generated by OTT - class EmployeeT public
oracleocciPObject private
oracleocciNumber EMPNO string NAME
oracleocciDate HIREDATE public
oracleocciNumber getEmpno() const void
setEmpno(const oracleocciNumber value)
string getName() const void setName(const
string value) oracleocciDate
getHiredate() const void setHiredate(const
oracleocciDate value)
38
OCCI Using OTT
input type specification file (emp.typ) cat
emp.typ TYPE employee As EmployeeT Running
OTT ott attraccessprivate codecpp
cppfileempo.cpp hfileemph.h intypeemp.typ
mapfileempm.cpp useridscott/tiger generates
emph.h, empo.cpp, empm.h, empm.cpp OTT
command-line options - attraccess attributes
to be private(with get/set methods) or
protected code cpp for OCCI cppfile name of
C file for class implementations hfile name
of include file containing class
declarations intype input type specification
file mapfile name of C file containing the
mapping register function userid userid
password of the schema owning the object types
39
OCCI OTT Example code fragments
1.//Database type - create type employee as
object ( Dept REF Department, -- Reference to
another object ) //C class generated by OTT
- class EmployeeT public oracleocciPObject
private Reflt DepartmentT gt
DEPT 2.//Database type - create type employee
as object ( Addr Address, -- embedded
object ) //C class generated by OTT - class
EmployeeT public oracleocciPObject
private AddressT ADDR
40
OCCI OTT Example code fragments
3.//Database type - create type PhoneList as
varray(10) of varchar2(30) create type customer
as object ( PhoneNumbers PhoneList, --
attribute of collection type ) //C class
generated by OTT - class CustomerT public
oracleocciPObject private vectorlt
string gt PHONENUMBERS 4.//Database type
- create type contractemployee under employee --
inheritance ( ) //C class generated by OTT
- class ContractEmployeeT public EmployeeT
//C inheritance
41
OCCI Navigational Access

• Retrieve objects and navigate relationships using
  references
• A reference is represented in OCCI by RefltTgt
  class type, where T is the class generated by OTT
  for the object type
• - Ref ltAddressTgt addrref
• Fetch initial REFs using SQL
• - Statement stmt conn-gtcreateStatement(Selec
  t Ref(a) from EmpTab a)
• ResultSet rs rs-gtexecuteQuery()
• rs-gtnext()
• RefltEmployeeTgt empref rs-gtgetRef(1)
• Access the object (pin) using the C
  dereference operator
• (-gt) on the RefltTgt variable
• string empname empref-gtgetName()
• //the -gt operator returns a object pointer to
  type T

42
OCCI Object cache

• Client-side memory and fast lookup for objects
• Maintains a fast lookup table between a reference
  and the corresponding C object
• When a reference is dereferenced the first time,
  the cache fetches the object from the server,
  subsequent accesses get the object from the cache
• Objects modified in the cache are updated in the
  database on transaction commit
• When the cache exhausts memory, it frees up
  unused objects in a LRU style garbage collector

43
OCCI Pinning/unpinning of objects

• A object is pinned in the object cache when a
  reference (RefltTgt) to the object is dereferenced
  (-gt operator)
• The pin count of a object is incremented when
  additional RefltTgts point to the same object
• Application can access/modify(get/set attributes)
  a object after it is dereferenced and pinned
• A object is unpinned and pin count decremented
  when the reference to it goes out of scope or
  points to a different object
• When the pin count of a object is zero, it is
  eligible for garbage collection and will be freed
  when cache memory is full

44
OCCI Object cache Example
RefltEmployeeTgt empref rs-gtgetRef(1)
//fetch and pin object string ename
empref-gtgetName()
Oracle Database
Client Object Cache
//pass to another function by value //pin count
increases by 1 PrintEmployee(empref)
0
1
2
1
//pin count decreases by 1 on return
function //navigate to address object Ref
ltAddressTgt addrref empref-gtgetAddr() string
city addrref-gtgetCity()
//Modify street in address addrref-gtsetStreet(1,
PARK AV) addrref-gtmarkModify()
//process another Employee object //earlier
object is unpinned empref rs-gtgetRef(1)
45
OCCI Modifying/Deleting objects Example
//fetch initial REF using SQL Statement stmt
conn-gtcreateStatement(Select Ref(a) From EmpTab
a) ResultSet rs stmt-gtexecuteQuery() rs-gtnex
t() //fetch the Ref RefltEmployeeTgt empref
rs-gtgetRef(1) //to modify a object, change
attributes using set methods empref-gtsetSalary(new
sal)//pin and modify object //call
markModified() method to indicate to
OCCI/cache empref-gtmarkModified() //Modified
object will be written to database on
commit conn-gtcommit() //to delete a object,
call markDelete on Ref or pinned
object empref.markDelete() //or empref-gtmarkDelet
e()
46
OCCI Creating new persistent objects

• OTT generates a overloaded new operator in each
  class -
• void operator new(size_t size, const
  oracleocciConnection sess,
• const string
  table)
• Use the new operator to create persistent objects
  in a database table -
• EmployeeT newemp new (conn,
  SCOTT.EMPTAB) EmployeeT()
• Set attribute values of the new object
• Object is saved in database when commit is done
• New object will be managed by client cache after
  commit

47
OCCI Creating persistent object - Example
Connection conn env-gtcreateConnection(scott,
tiger) //EmployeeT class generated by OTT for
type Employee //EMPTAB is object table of type
Employee EmployeeT newemp new (conn,
SCOTT.EMPTAB) EmployeeT() newemp-gtsetEmpno(100
0) newemp-gtsetEmpname(JOHN W) //use OCCI
Date class Date hdate(env, 1, 1,
2003) newemp-gtsetHiredate(hdate) //object will
be saved in database on commit conn-gtcommit() //
to get REF of object, use getRef() Ref
ltEmployeeTgt newemp-gtgetRef()
48
OCCI Objects Associative Access

• Use SQL to create and fetch object values
• Can be used with object tables or with object
  type columns
• - create type DepartmentT
• - create table DeptTab of DepartMentT --
  object table
• - create table Region (., Dept DepartmentT,
  ) -- object type column
• Objects are not managed by cache since they are
  transient
• Extensive support for collections
• Easy way of using Oracle Object-Relational
  features

49
OCCI Associative Access Examples

• Insert into a object table

Statement stmt conn-gtcreateStatement( insert
into DeptTab values (1) ) DepartmentT dept
new DepartmentT()//create transient
instance //set values into dept
members dept-gtsetDeptNo(1) dept-gtsetDeptName(HR
) stmt-gtsetObject(1, dept) stmt-gtexecuteUpdate(
)

• Insert into a table with object type column

Statement stmt conn-gtcreateStatement( insert
into Region(Area, Dept) values (1, 2)
) stmt-gtsetString(1, North) DepartmentT
dept new DepartmentT()//create transient
instance //set values into dept
members stmt-gtsetObject(2, dept) stmt-gtexecuteUp
date()
50
OCCI Associative Access Examples

• Selecting a object value

Statement stmt conn-gtcreateStatement(select
value(a) from DeptTab a) //or select dept from
Region ResultSet rs stmt-gtexecuteQuery() rs-
gtnext()//fetch DepartmentT dept
rs-gtgetObject(1) //access dept members cout ltlt
dept-gtgetDeptNo() ltlt endl cout ltlt
dept-gtgetDeptName() ltlt endl
51
OCCI Collections

• Oracle provides VARRAY and Nested Table data
  types for modeling a set/list of values of a type
  
• OCCI uses the C STL vector class for
  representing varray and nested table of any type
  -
• - Database type create type PhoneList as
  varray(50) of varchar2(100)
• OCCI representation vectorlt string gt
• - Database type create type Addresses as
  table of Address
• OCCI representation vectorlt AddressT gt
• vectorltTgt can be used in relational/associative
  or navigational access -
• - Relational/Associative access - Statement
  IN/OUT binds and ResultSet fetch
• - Navigational access Attributes of object
  types

52
OCCI Collections Relational access

• setVector and getVector interfaces on Statement
  and ResultSet for inserting/fetching data from
  varray/nested table columns
• Comprehensive support for collections of all
  Oracle data types -
• //to insert/pass a collection
• - void setVector(Statement stmt, unsigned
  int column,
• const vectorltTgt vect,
• const string sqltype)
• //to get a PL/SQL OUT param/result of
  collection type
• - void getVector( Statement rs, unsigned
  int column,
• vectorltTgt vect)
• //to fetch a collection column
• - void getVector(ResultSet rs, unsigned
  int column,
• vectorltTgt vect)
• T can be any type int/Number/Date/Blob/s
  tring/PObject /RefltTgt

53
OCCI Collections Example

• Insert into a VARRAY column

-- create type PhoneList as varray(50) of
varchar2(100) -- create table customer(cname
varchar2(100), cphones PhoneList) Statement
stmt conn-gtcreateStatement ( insert into
Customer values (1, 2) ) stmt-gtsetString
(1, JOHN W) //add 3 phone numbers vectorltstrin
ggt phonenos phonenos.push_back(111-222-3333) p
honenos.push_back(447-555-2323) phonenos.push_b
ack(575-232-5885) //setVector(Statement ,
int col, vectorltTgt val, string
dbtype) setVector(stmt, 2, phonenos,
SCOTT.PHONELIST) stmt-gtexecuteUpdate()
54
OCCI Collections Example

• Selecting a VARRAY column

-- create type Marks as varray(50) of Number --
create table student(cname varchar2(100), cphones
PhoneList) Statement stmt conn-gtcreateStateme
nt ( select from customer) ResultSet rs
stmt-gtexecuteQuery() while (rs-gtnext())
cout ltlt customer name ltlt rs-gtgetString(1)
vectorltstringgt phonenos getVector(rs, 2,
phonenos) for (int i 0 i lt
phonenos.size() i) cout ltlt phonenosi
ltlt ,
55
OCCI Collections Objects support

• VARRAY and Nested table types can be used as
  attribute types of objects
• In the OTT generated C class, collection type
  attributes are declared as vectorltTgt type members
• create type PhoneNumbersType as varray(10) of
  varchar2(20)
• create type Customer as object
• (
• PhoneNumbers PhoneNumbersType
• )
• OTT class -
• class CustomerT public oracleocciPObject
  
• private
• vectorlt string gt PHONENUMBERS

56
OCCI MetaData access

• Dynamically discover the attributes(e.g
  name,type,size, count) of database objects(e.g
  tables,procedures,types) and query results
• MetaData class and its getXXX methods provide
  extensive attribute information of database
  objects and query result columns
• Use ConnectiongetMetaData() and
  ResultSetgetColumnListMetaData() to retrieve
  needed MetaData objects

57
OCCI MetaData access Examples

• MetaData of a schema object

//ConnectiongetMetaData(string object,
ParamType ptype) //ptype PTYPE_TABLE/PTYPE_VIEW
etc or PTYPE_UNK MetaData md conn-gtgetMetaData(
EMP, PTYPE_UNK) //ATTR_OBJ_PTYPE returns
PTYPE_TABLE/PTYPE_VIEW int objectType
md.getInt(MetaDataATTR_OBJ_PTYPE) int
columnCount md.getInt(MetaDataATTR_OBJ_NUM_COL
S) Timestamp objts md.getTimestamp(MetaDataAT
TR_TIMESTAMP) vectorltMetaDatagt cols
md.getVector(MetaDataATTR_LIST_COLUMS) //each
MetaData in the vector is for 1 column cout ltlt
column 1 name ltlt cols0.getString(MetaData
ATTR_NAME)
58
OCCI MetaData Access - Examples

• MetaData of a ResultSet

Statement stmt conn-gtcreateStatement(select
from emp) ResultSet rs rs-gtexecuteQuery() //
each element in the vectorltgt is a
column vectorltMetaDatagt selectcols
rs-gtgetColumnListMetaData() int columnCount
selectcols.size() for (int i 0 i lt
columnCount i) cout ltltcolumn
nameltltselectcolsi.getString(MetaDataATTR_NAME
) cout ltltcolumn typeltltselectcolsi.getInt(Met
aDataATTR_DATA_TYPE)
59
OCCI Scalability features

• Connection pooling
• Stateless Connection pooling(10i)
• Statement caching(10i)
• Thread safety

60
OCCI Connection pooling

• Use a small group of physical connections to
  support many user sessions
• Saves on connection time and server-side
  processes resources
• Oracle dynamically selects one of the free
  connections to execute a statement, and then
  releases the connection to the pool immediately
  after the execution
• Support for proxy connections through the
  connection pool owner
• Suited for middle-tier, multi-threaded
  applications that need to handle many users

61
OCCI Connection pooling Usage

• To create a connection pool -
• ConnectionPool EnvironmentcreateConnectionPool(
  
• const string poolUserName, const string
  poolPassword,
• const string connectString "",
• unsigned int minConn0,unsigned int maxConn1,
• unsigned int incrConn1)
• Maximum connections, minimum connections,
  increment can be reconfigured after the pool has
  been created
• Getting a connection from the pool -
• Connection ConnectionPoolcreateConnection(
• const string userName, const string
  password)
• Releasing a connection to the pool -
• void ConnectionPoolterminateConnection(Connec
  tion conn)
• A Connection will be automatically released if it
  has been idle for the timeout value specified by
  setTimeOut

62
OCCI Connection pooling Other interfaces

• To create proxy connections -
• - Connection ConnectionPoolcreateProxyConnec
  tion(cont string name,
• ConnectionProxyType proxyType
  ConnectionPROXY_DEFAULT)
• - Connection ConnectionPoolcreateProxyConnec
  tion(const string name,
• string roles, int numRoles,
• ConnectionProxyType proxyType
  ConnectionPROXY_DEFAULT)
• Get number of open/busy connections in the pool
• - int ConnectionPoolgetOpenConnections()
• - int ConnectionPoolgetBusyConnections()
• Reconfigure the pool min/max/incr connections
• - void ConnectionPoolsetPoolSize(int
  minConn, int maxConn, int incr)

63
OCCI Connection Pool Example
//Create a connection pool, max connections
10, //min connections 4, increment connections
2 ConnectionPool appPool env-gtcreateConnectio
nPool(app1, app1, , 4, 10,
2) //get a connection from the pool Connection
conn appPool-gtcreateConnection(scott,tiger)
//database access use the Connection
object .. .. //release connection to
pool appPool-gtterminateConnection(conn)
64
OCCI Thread Safety

• Multiple threads in a application improve
  performance and response times
• Multiple threads can make OCCI calls concurrently
• OCCI classes Environment, Connection,
  ConnectionPool are thread-safe.
• Statement, ResultSet are not thread-safe,
  multiple threads should not operate on the same
  object simultaneously
• Either application or OCCI can handle
  serialization for thread-safe objects, by
  specifying mode in createEnvironment -
• THREADED_MUTEXED OCCI manages
  serialization(using mutexes)
• THREADED_UNMUTEXED Application manages
  serialization

65
OCCI New features in 10i

• Advanced Queuing
• Globalization and Unicode
• XA support
• Stateless connection pooling
• Statement caching
• IEEE float and double datatypes

66
OCCI Advanced Queuing

• Database integrated message queuing for
  enterprise applications
• OCCI provides classes and interfaces for
  applications to create, send, receive, and access
  messages
• Access queues of Raw, AnyData, Object types
• Numerous options for messages, enqueue and
  dequeue.
• E.g priority, recipient list, dequeue mode,
  listen etc
• Integrate messaging, object and relational access
  in a single application

67
OCCI AQ Classes
Queue user
Data options
Read Message
Send Message
Wait for messages
68
OCCI AQ Examples

• Enqueue/Dequeue RAW messages

//instantiate a Message object Message
msg(conn) //a Agent identifies a Queue user
consumer/producer vectorltAgentgt
receiveList //construct a Bytes object char
msgstring ABCDE Bytes rawBytes(msgstring,
strlen(msgstring) //set Bytes as message
payload msg.setBytes(rawBytes) //instantiate a
Producer Producer prod(conn) prod.send(msg,
QUEUE1)//enqueue the message //now
dequeue Consumer cons(conn) Message msg2
cons.receive(QUEUE1, RAW, SYS)
69
OCCI AQ Examples

• Enqueue/Dequeue AnyData messages

//AnyData class is for generic data AnyData
any1(conn) any1.setFromString(STRING
MSG)//use setFromXXX methods Message
msg msg.setAnyData(any1)//set AnyData as
payload prod.send(msg, ANYQUEUE)//enqueue any1.
setFromDate(dt)//dt is of type
Date msg.setAnyData(any1)//now set Date as the
payload prod.send(msg, ANYQUEUE)//same
queue //dequeue Message msg2 msg2
cons.receive(ANYQUEUE,ANYDATA,SYS)//dequeue
//get value AnyData msgany msg2.getAnyData() s
tring stringmsg msgany.getAsString()//use
other getAsXXX
70
OCCI AQ Examples

• Enqueue/Dequeue Object Type messages

//construct transient instance of object OrderT
neworder new OrderT()//OrderT is OTT
generated class //set attributes neworder-gtsetOrde
rDate() //set object as Message
payload Message newmessage(conn) newmessage.setOb
ject(neworder) //enqueue prod.send(newmessage,
ORDERSQUEUE) //now dequeue Message recv
cons.receive(ORDERSQUEUE, ORDERTYPE,
SCOTT) //get the object from the
message OrderT processorder (OrderT
)recv.getObject()
71
OCCI Globalization Unicode

• Supports multibyte charactersets(including UTF8)
  and Unicode (UTF16) characterset for application
  development
• Applications can specify client characterset and
  national characterset when the Environment is
  initialized
• Environment EnvironmentcreateEnvironment(str
  ing charset,
• string ncharset)
• Client characterset is for all SQL statements,
  schema elements data for all CHAR types.
  National characterset is for data of all NCHAR
  types
• New datatype UString represents Unicode data in
  UTF16, each character is 2 bytes
• Use existing string interfaces for multibyte data

72
OCCI Unicode Interfaces

• All string interfaces have new equivalent UTF16
  interfaces that take/return Ustring. E.g -
• - StatementsetUString(int col, UString
  value)
• - UString ResultSetgetUString(int col)
• - EnvironmentcreateConnection(UString user,
  UString password,
• UString dbname)
• - Statementexecute(UString sqlquery)
• - getVector( ResultSet rs, unsigned int
  index, vectorltUStringgt vect)
• In Windows platforms, UString is equivalent to
  Standard C wstring datatype
• OTT generates CHAR/VARCHAR attributes as UString
  type member variables for a Unicode application

73
OCCI Globalization support Examples

• Specifying client application charactersets

//OCCIUTF16 is for indicating UTF16
characterset Environment env1
EnvironmentcreateEnvironment(WE8DEC,OCCIUTF16
) Environment env2 EnvironmentcreateEnviron
ment(JA16SJIS,JA16SJIS) Environment env3
EnvironmentcreateEnvironment(ZHT16BIG5,UTF8)
//complete Unicode application - Environment
env4 EnvironmentcreateEnvironment(OCCIUTF16
,OCCIUTF16)

• Binding UTF16 data(UString)

//client national characterset is OCCIUTF16 //for
column types NCHAR/NVARCHAR, call
setDatabaseNCHARParam(col,true) stmt-gtsetDatabaseN
CHARParam(3, true) //the 2 Katakana Unicode
characters for 'Japan' in japanese unsigned short
japanunicodechars 0x65E5,0x672C UString
uJapan(japanunicodechars, 2) stmt-gtsetUString(3,
uJapan)//binding UTF16 string
74
OCCI Globalization Support - Examples

• Using wstring in Windows

//complete Unicode application Environment env
EnvironmentcreateEnvironment(OCCIUTF16,OCCIUT
F16) //wstring datatype is same as UString in
Windows wstring wusername Lscott//L
creates Unicode string wstring wpassword
Ltiger Connection conn env-gtcreateConnection
(wusername, wpassword, L) //create a
Statement with Unicode query //this will call
ConnectioncreateStatement(UString
sql) Statement stmt conn-gtcreateStatement(LSe
lect From ) //bind wstring wstring name
//from input terminal stmt-gtsetUString(1, name)
75
OCCI XA Support

• Oracle XA conforms to X/Open DTP standard for
  transaction monitors(TM)
• Application uses TM for connecting to multiple,
  distributed resources and transaction
  control(2-phase commit)
• OCCI interfaces for getting the underlying Oracle
  Environment Connection in a XA application
• The Environment Connection object can then be
  used as in a typical application for further
  database access

76
OCCI XA Application Architecture
Uses OCCI
77
OCCI XA Support Interfaces

• Get/release Oracle environment
• - Environment EnvironmentgetXAEnvironment(c
  onst string dbname)
• - void EnvironmentreleaseXAEnvironment(Envir
  onment env)
• Get/release Oracle connection
• - Connection EnvironmentgetXAConnection(con
  st string dbname)
• - void EnvironmentreleaseXAConnection(const
  string dbname)
• Check if a error(exception) is in XA or Oracle
• - int SQLExceptiongetXAErrorCode(const
  string dbname)
• dbname is the xa_open string specified by the
  application to the TM to connect to Oracle

78
OCCI XA Support Example
//define the db open string according to Oracle
XA specification string xaopen_str
"oracle_xaACCP/SCOTT/TIGERSESTM50logdir.Sql
Netinst1" //connect to database and other
resources using TM interfaces tx_open() //to
manipulate data need access to Connection Environm
ent xaenv EnvironmentgetXAEnvironment(xaopen_
str) Connection xaconn xaenv-gtgetXAConnection(
xaopen_str) //access database use SQL
etc tx_begin()//TM call Statement stmt
xaconn-gtcreateStatement(Update
Emp..) stmt-gtexecuteUpdate() //for
transaction control use TM calls, not Oracle
calls tx_commit() //release Connection
Environment xaenv-gtreleaseXAConnection(xaconn) En
vironmentreleaseXAEnvironment(xaenv)
79
OCCI Stateless Connection Pooling

• Maintain a pool of open, authenticated user
  connections
• No roundtrip for authentication, connection is
  ready to be used when got from pool
• Connections are stateless, applications should
  not leave open transactions
• Connections can be tagged with a descriptive
  name and then retrieved using tag
• Support for proxy connections through the
  connection pool owner

80
OCCI Stateless Connection Pooling II

• Suited for middle-tier, multi-threaded
  applications that do short database access
• Stateless Connection pool can be
• HOM0GENOUS All connections with same user
• HETEROGENOUS Connections can have
  different user/proxy
• OCCI will not do commit/rollback, users
  responsibility to not leave any state when
  connection is released
• Interfaces similar to ConnectionPool

81
OCCI Stateless Connection Pool Examples

• Homogenous ConnectionPool

StatlessConnectionPool spool Environmentcreat
eStatelessConnectionPool(scott, tiger,
, 10, 4, 2, HOMOGENOUS)//MaxConn10,
MinConn4, IncrConn2 //get a connection from
the pool Connection conn spool-gtgetConnection()
//use Connection as earlier Statement stmt
conn-gtcreateStatement(alter session
set Nls_LanguageFrench) //release
Connection with tag spool-gtreleaseConnection(conn,
FR) //get Connection with tag Connection
conn2 spool-gtgetConnection(FR) //destroy
pool env-gtterminateConnectionPool(spool)
82
OCCI Stateless Connection Pool - Examples

• Heterogenous ConnectionPool

StatlessConnectionPool spool Environmentcreat
eStatelessConnectionPool(scott, tiger,
, 10, 4, 2, HETEROGENOUS)//MaxConn10,
MinConn4, IncrConn2 //get a connection from
the pool with a different user Connection conn
spool-gtgetConnection(acme, acme) //use
Connection as earlier Statement stmt
conn-gtcreateStatement(Select From
Emp) //release Connection without
tag spool-gtreleaseConnection(conn) //get
Connection again Connection conn2
spool-gtgetConnection(acme, acme) //get
Proxy Connection Connection conn3
spool-gtgetProxyConnection(proxyuser1) //destroy
pool env-gtterminateConnectionPool(spool)
83
OCCI Statement Caching

• Cache and reuse frequently executed SQL
  statements
• In the server, cursors are ready to be used
  without the need to parse the statement again
• Client-side resources and data structures are
  effectively reused
• Statements can be tagged with a descriptive
  name and then retrieved from the cache by tag
• Works with connection pooling and stateless
  connection pooling

84
OCCI Statement Caching Usage

• Enable statement caching on a Connection -
• - ConnectionsetStmtCacheSize(unsigned int
  stmtCacheSize)
• On createStatement, the cache will be searched
  for statement. If not found, then a new statement
  is created
• On terminateStatement(with optional tag), the
  statement will be added to cache
• On next createStatement, the statement will be
  got from the cache

85
OCCI Statement Caching Example
//enable statement caching conn-gtsetStmtCacheSize(
10) //get cache size int csize
conn-gtgetStmtCacheSize() //create a
statement Statement stmt conn-gtcreateStatement(
sqlquery) //release the statement to cache with
tag conn-gtterminateStatement(stmt,
tagA) //retrieve statement again with SQL or
tag stmt conn-gtcreateStatement(sqlquery) //Or s
tmt conn-gtcreateStatement(,tagA)
86
OCCI Interoperability with OCI

• OCCI is designed and implemented on top of OCI
• Underlying OCI handles of database access
  classes can be retrieved -
• - OCIEnv EnvironmentgetOCIEnv()
• - OCIServer ConnectiongetOCIServer()
• - OCISvcCtx ConnectiongetOCIServiceContext()
  
• - OCISession ConnectiongetOCISession()
• - OCIStmt StatementgetOCIStatement()
• Handles can be used when migrating and reusing
  existing OCI code
• OCI C code for relational access can be mixed
  in a OCCI application
• OCI objects interfaces cannot be used in OCCI
  application

87
OCCI OCI Interoperability Examples
//get OCI Environment Service Context OCIEnv
ocienv env-gtgetOCIEnv()//env is OCCI
Environment OCISvcCtx ocisvc
conn-gtgetOCISvcCtx()//conn is Connection //use
OCI calls OCIHandleAlloc(ocienv, dpctx,
OCI_HTYPE_DIRPATH_CTX,) //do more OCI
stuff DoDirectPathLoad(ocienv, ocisvc, dpctx,
) //reuse of existing code Statement stmt
conn-gtcreateStatement(Select From
Emp) ResultSet rs stmt-gtexecuteQuery() //di
splay MetaData using existing C function OCIStmt
stmthp stmt-gtgetOCIStmt() DisplayMetaDataInGUI
(stmthp) //do not use OCIObject calls
88
OCCI Performance Tuning

• Relational access
• - Use iterative DML execution to reduce
  roundtrips
• Scalability
• - Use Stateless Connection pooling to get
  authenticated connections
• - Use Connection pooling to use less number of
  physical connections
• Objects access navigation
• - Complex Object Retrieval for prefetching
  related objects
• - Tune the object cache

89
OCCI Complex Object Retrieval(COR)

• Object oriented applications model their data as
  a set of interrelated objects to form graphs
  (using REFs)
• Performance of such models can be increased using
  COR to prefetch a set of linked objects in a
  single network trip
• Prefetch attributes(type and depth) are specified
  on the root RefltTgt. When the root is pinned,
  linked objects are also fetched
• - RefltTgtsetPrefetch(const string typeName,
  unsigned int depth)
• - RefltTgtsetPrefetch(unsigned int depth)

PurchaseOrder Cust REF Customer RelatedOrder
REF PurchaseOrder LineItems LineItemRefArray
Customer
PurchaseOrder
LineItem 1
LineItem 2
90
OCCI COR Example
//OTT class - //class PurchaseOrder //
Date OrderDate // Ref ltCustomergt Customer //
Ref ltPurchaseOrdergt RelatedOrder // vector
lt RefltLineItemgt gt LineItems RefltPurchaseOrdergt
poref //set COR prefetching to get Customer
object also poref.setPrefetch(Customer,
1) Date odate poref-gtgetOrderDate()//this
will fetch pin PO //the linked Customer object
will also now be in the cache RefltCustomergt cust
poref-gtgetCustomer() string cname
cust-gtgetName()//no round-trip //to get all
linked objects at depth 1 customer,
related //purchase order, line items
- poref.setPrefetch(1)
91
OCCI Tuning object cache

• The cache is configured by optimal size and
  maximum size() parameters -
• - Maximum cache size optimal size
  (optimal size maximum size/100)
• - EnvironmentsetCacheOptSize(),
  EnvironmentsetCacheMaxSize()
• For C datatype attributes(string, vectorltTgt),
  the memory comes from C heap
• Size the cache according to application needs
• Consider periodic flushes of objects instead of
  sending all objects at commit
• - ref-gtflush()

92
OCCI In closing

• Natural interface to Oracle object features
• C and object oriented paradigm for better
  productivity and quality
• Simple and easy to use for relational access
• References -
• Oracle C Call Interface Programmers Guide
• Oracle Application Developers Guide
  Object-Relational Features
• OTN Discussion forums otn.oracle.com

93
Demo and Q A