Oracle Corporation · NYSE:ORCL
Oracle — Technical Presentation
93 slides from Oracle's technical presentation
Slide 1: Oracle C++ Call Interface (OCCI)
ORACLE C++ CALL INTERFACE(OCCI) Shankar Iyer, Oracle India. Oracle C++ Call Interface(OCCI) 1
Slide 2: OCCI – Today's Agenda
OCCI – Today’s Agenda Session I Session III - Introduction - Scalability Features - SQL/PLSQL Execution - 10i features - Data classes Session II Session IV - Object Features - Interoperability with OCI - MetaData access - OCCI Performance Tuning - Demo application -Q&A Oracle C++ Call Interface(OCCI
Slide 3: Introduction
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 Oracle C++ Call Interface(OCCI) 3
Slide 4: Benefits
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 Ora
Slide 5: Features
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 ac
Slide 6: Building an application
OCCI – Building an application Application source files OCCI API header files (use OCCI API and OTT generated C++ classes) class headers OTT generated C++ C++ compiler class implementations OCCI header files •occi.h •occiCommon.h OCCI library •occiControl.h Linker (static or dynamic) •occiData.h •oc
Slide 7: Application Initialization
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 co
Slide 8: Initialize Environment – Examples
OCCI – Initialize Environment – Examples Creating default Environment //include 1 header file for all OCCI classes/interfaces #include <occi.h> //create Environment Environment *env = Environment::createEnvironment(); //use the Environment instance to create connections, //database access … //term
Slide 9: Control classes
OCCI – Control classes Environment Create Create Create ConnectionPool Get Connection Get StatelessConnection Pool Get Create MetaData Statement Get Execute ResultSet Oracle C++ Call Interface(OCCI) 9
Slide 10: Connecting to database
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 *Environment::createConnection( const string &userName, const string &password, const string &connectString) U
Slide 11: Create Connection - Example
OCCI – Create Connection - Example //First need Environment Environment *env = Environment::createEnvironment(); Connection *conn=env->createConnection(“scott”,”tiger”,””); //3rd parameter is db name/TNS alias ..//database access – use the Connection object .. .. //logoff and terminate connection en
Slide 12: Executing SQL/PLSQL
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 val
Slide 13: Executing SQL – Usage
OCCI – Executing SQL – Usage Create a Statement object with Connection::createStatement() Specify SQL command(DDL/DML/query) as argument to :- Connection::createStatement(string &sql); Statement::setSQL(string &sql); Statement::execute(string &sql); - can be used for any SQL, returns status Stat
Slide 14: Executing SQL – Examples
OCCI – Executing SQL – Examples Simple DML Insert //createStatement() on Connection class gives a Statement //instance Statement *stmt = conn->createStatement(“ insert into Dept(Deptno,Dname, Loc) values (1, ‘ACCOUNTS’, ‘ZONE1’ ”); //executeUpdate for all INSERT/UPDATE/DELETE stmt->executeUpdate()
Slide 15: Executing SELECT – Examples
OCCI – Executing SELECT – Examples Executing Select queries and fetching results Statement *stmt = conn->createStatement(“ select Empno, Ename, Sal from Emp where Hiredate >= :1”); //automatically converted to Date stmt->setString(1, “01-JAN-1987”); //executeQuery returns a ResultSet ResultSet *rs
Slide 16: DML on multiple rows using Iterations
OCCI – DML on multiple rows using Iterations DML(INSERT/UPDATE/DELETE) of multiple rows in single roundtrip Statement *stmt = conn->createStatement(“insert into emp (empno, ename) values (:1, :2)”); //specify max iterations stmt->setMaxIterations(10);//number of rows //specify maximum data size fo
Slide 17: SQL Execution – Streaming
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->createStatement(“Select LongCol…”); ResultSet *rs =
Slide 18: Executing PL/SQL
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 Statement::registerOutParam, sizes of OUT parameters by Statement
Slide 19: PLSQL – Examples
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->createStatement(“ begin :1 := CalculateBonus( :2, :
Slide 20: SQL/PLSQL – Data buffer interface
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 : Statement::executeArrayUpdate(in
Slide 21: SQL/PLSQL – Array fetch example
OCCI – SQL/PLSQL – Array fetch example char enames[10][20];//10 elements of length 20 chars each ub2 elens[10];//to receive the length of each element Statement *stmt = conn->createStatement("Select Ename from EMP"); ResultSet *rs = stmt->executeQuery(); //ResultSet::setDataBuffer(colIndex,buffer,ty
Slide 22: SQL/PLSQL – Array insert example
OCCI – SQL/PLSQL – Array insert example int empnos[5]; empnos[0] = 801; empnos[1] = 802; stmt = conn->createStatement("insert into emp (empno) values (:1)"); //Statement::setDataBuffer(colIndex,buffer,type,elemsize, //lengths,ind,rc) //OCCIINT for int datatype stmt->setDataBuffer(1, empnos, OCCIINT,
Slide 23: Error Handling
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+
Slide 24: Error Handling – Example
OCCI – Error Handling – Example Handling Oracle and C++ STL errors separately try { ResultSet *rs = stmt->executeQuery(); while (rs->next()) ………. } catch (SQLException &oraex) //Oracle/OCCI errors { int errno = oraex->getErrorCode();//returns the ORA number string errmsg = oraex->getMessage(); //m
Slide 25: Data classes
OCCI – Data classes Classes for using Oracle data types Oracle data type OCCI class NUMBER/REAL/INT etc Number DATE Date INTERVAL YEAR TO MONTH IntervalYM INTERVAL DAY TO SECOND IntervalDS TIMESTAMP WITH TZ/LTZ Timestamp RAW Bytes Easy to use with comprehensive functionality Used with Statemen
Slide 26: Data classes - Number
OCCI – Data classes - Number // inserting a number Number bignum; bignum.fromText(env, “43613923333.233”, “99999999999999.999”); stmt->setNumber(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
Slide 27: Data classes – Date
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->setDate(1, edate);//stmt is Statement // fetching and displaying a Date Date odate = rs->getDate(1); //rs is ResultSet Cout << odate.toText(“DD-MON-YYYY”
Slide 28: Large Object(LOB) support
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 :- - Statement::setBfile(int column, Bfile &value); -
Slide 29: LOB support – Examples
OCCI – LOB support – Examples Reading a BLOB Statement *stmt = conn->createStatement(“select resume from emp”); ResultSet *rs = stmt->executeQuery(); rs->next(); //fetch 1 row //Blob ResultSet::getBlob(int column) Blob resume = rs->getBlob(1); char buffer[100]; int bytesRead, offset = 1; while ((b
Slide 30: LOB support - Examples
OCCI – LOB support - Examples Inserting/updating a CLOB Statement *stmt = conn->createStatement(“ insert into documents values (:1) ”); //create empty Clob Clob doccontents(conn); doccontents.setEmpty(); //1st insert will create LOB locator in database stmt->setClob(1, doccontents); stmt->executeU
Slide 31: Objects
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 Oracle’s object features, OCCI and C++ Cli
Slide 32: Objects – Oracle basics
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; --
Slide 33: Objects – Oracle basics
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 Produ
Slide 34: Objects – Access methods
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 -
Slide 35: Object Type Translator(OTT)
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 inp
Slide 36: OTT – Data type mappings
OCCI – OTT – Data type mappings Oracle data type of attribute C++/OCCI type of class member All numeric types : Number NUMBER,INT,REAL etc VARCHAR/CHAR string(C++) BFILE Bfile BLOB Blob CLOB Clob DATE Date INTERVAL YEAR TO MONTH IntervalYM INTERVAL DAY TO SECOND IntervalDS TIMESTAMP WITH TZ/LTZ Time
Slide 37: OTT – Example generated class
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 oracle::occi::PObject { private: oracle::occi::Number EMPNO; string NAME; oracle::occi::Date HIREDATE
Slide 38: Using OTT
OCCI – Using OTT #input type specification file (emp.typ) $ cat emp.typ TYPE employee As EmployeeT $ #Running OTT $ ott attraccess=private code=cpp cppfile=empo.cpp hfile=emph.h intype=emp.typ mapfile=empm.cpp userid=scott/tiger #generates emph.h, empo.cpp, empm.h, empm.cpp #OTT command-line options
Slide 39: OTT – Example code fragments
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 oracle::occi::PObject { private: Ref< DepartmentT > DEPT; 2.//Database type :- create type employe
Slide 40: OTT – Example code fragments
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 oracle::occi::PObject { private: vector<
Slide 41: Navigational Access
OCCI – Navigational Access Retrieve objects and navigate relationships using references A reference is represented in OCCI by Ref<T> class type, where T is the class generated by OTT for the object type - Ref <AddressT> addrref; Fetch initial REFs using SQL - Statement *stmt = conn->createStat
Slide 42: Object cache
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
Slide 43: Pinning/unpinning of objects
OCCI – Pinning/unpinning of objects A object is ‘pinned’ in the object cache when a reference (Ref<T>) to the object is dereferenced (-> operator) The pin count of a object is incremented when additional Ref<T>’s point to the same object Application can access/modify(get/set attributes) a obje
Slide 44: Object cache – Example
OCCI – Object cache – Example Ref<EmployeeT> empref = rs->getRef(1) //fetch and pin object string ename = empref->getName() //pass to another function by value Client Object Cache //pin count increases by 1 Oracle PrintEmployee(empref); Database 012 //pin count decreases by 1 on return function //na
Slide 45: Modifying/Deleting objects – Example
OCCI – Modifying/Deleting objects – Example //fetch initial REF using SQL Statement *stmt = conn->createStatement(“Select Ref(a) From EmpTab a”); ResultSet *rs = stmt->executeQuery(); rs->next(); //fetch the Ref Ref<EmployeeT> empref = rs->getRef(1); //to modify a object, change attributes using set
Slide 46: Creating new persistent objects
OCCI – Creating new persistent objects OTT generates a overloaded new operator in each class :- void *operator new(size_t size, const oracle::occi::Connection * sess, const string& table); Use the new operator to create persistent objects in a database table :- EmployeeT *newemp = new (conn, “SC
Slide 47: Creating persistent object - Example
OCCI – Creating persistent object - Example Connection *conn = env->createConnection(“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->setEmpno(1000); newemp->setEmpname
Slide 48: Objects – Associative Access
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 Obje
Slide 49: Associative Access – Examples
OCCI – Associative Access – Examples Insert into a object table Statement *stmt = conn->createStatement(“ insert into DeptTab values (:1) ”); DepartmentT *dept = new DepartmentT();//create transient instance //set values into dept members dept->setDeptNo(1); dept->setDeptName(“HR”); stmt->setObject
Slide 50: Associative Access – Examples
OCCI – Associative Access – Examples Selecting a object value Statement *stmt = conn->createStatement(“select value(a) from DeptTab a”); //or “select dept from Region” ResultSet *rs = stmt->executeQuery(); rs->next();//fetch DepartmentT *dept = rs->getObject(1); //access dept members cout << dept->
Slide 51: Collections
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
Slide 52: Collections – Relational access
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, unsi
Slide 53: Collections – Example
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->createStatement (“ insert into Customer values (:1, :2) “); stmt->setString (1, “JOHN W”); //ad
Slide 54: Collections – Example
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->createStatement (“ select * from customer“); ResultSet *rs = stmt->executeQuery(); while (rs->next()) { cout
Slide 55: Collections – Objects support
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 vector<T> type members create type PhoneNumbersType as varray(10) of varchar2(20); create type Customer as objec
Slide 56: MetaData access
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 Connection::ge
Slide 57: MetaData access – Examples
OCCI – MetaData access – Examples MetaData of a schema object //Connection::getMetaData(string &object, ParamType ptype) //ptype = PTYPE_TABLE/PTYPE_VIEW etc or PTYPE_UNK MetaData md = conn->getMetaData(“EMP”, PTYPE_UNK); //ATTR_OBJ_PTYPE returns PTYPE_TABLE/PTYPE_VIEW … int objectType = md.getInt(
Slide 58: MetaData Access - Examples
OCCI – MetaData Access - Examples MetaData of a ResultSet Statement *stmt = conn->createStatement(“select * from emp”); ResultSet *rs = rs->executeQuery(); //each element in the vector<> is a column vector<MetaData> selectcols = rs->getColumnListMetaData(); int columnCount = selectcols.size(); for
Slide 59: Scalability features
OCCI – Scalability features Connection pooling Stateless Connection pooling(10i) Statement caching(10i) Thread safety Oracle C++ Call Interface(OCCI) 59
Slide 60: Connection pooling
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 immediate
Slide 61: Connection pooling – Usage
OCCI – Connection pooling – Usage To create a connection pool :- ConnectionPool* Environment::createConnectionPool( const string &poolUserName, const string &poolPassword, const string &connectString ="", unsigned int minConn=0,unsigned int maxConn=1, unsigned int incrConn=1) Maximum connections
Slide 62: Connection pooling – Other interfaces
OCCI – Connection pooling – Other interfaces To create proxy connections :- - Connection* ConnectionPool::createProxyConnection(cont string &name, Connection::ProxyType proxyType = Connection::PROXY_DEFAULT); - Connection* ConnectionPool::createProxyConnection(const string &name, string roles[], i
Slide 63: Connection Pool – Example
OCCI – Connection Pool – Example //Create a connection pool, max connections = 10, //min connections = 4, increment connections = 2 ConnectionPool *appPool = env->createConnectionPool(“app1”, ”app1”, “”, 4, 10, 2); //get a connection from the pool Connection *conn = appPool->createConnection(“scott”
Slide 64: Thread Safety
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 oper
Slide 65: New features in 10i
OCCI – New features in 10i Advanced Queuing Globalization and Unicode XA support Stateless connection pooling Statement caching IEEE float and double datatypes Oracle C++ Call Interface(OCCI) 65
Slide 66: Advanced Queuing
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 : pr
Slide 67: AQ Classes
OCCI – AQ Classes Queue user Data + options Read Message Send Message Wait for messages Oracle C++ Call Interface(OCCI) 67
Slide 68: AQ – Examples
OCCI – AQ – Examples Enqueue/Dequeue RAW messages //instantiate a Message object Message msg(conn); //a Agent identifies a Queue user – consumer/producer vector<Agent> receiveList; //construct a Bytes object char msgstring[] = “ABCDE”; Bytes rawBytes(msgstring, strlen(msgstring); //set Bytes as mes
Slide 69: AQ – Examples
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 typ
Slide 70: AQ – Examples
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->setOrderDate(…); … //set object as Message payload Message newmessage(conn); newmessage.setObject(neworder);
Slide 71: Globalization & Unicode
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 *Environment::createEnvironment(stri
Slide 72: Unicode Interfaces
OCCI – Unicode Interfaces All string interfaces have new equivalent UTF16 interfaces that take/return Ustring. E.g :- - Statement::setUString(int col, UString &value) - UString ResultSet::getUString(int col) - Environment::createConnection(UString &user, UString &password, UString &dbname) - State
Slide 73: Globalization support – Examples
OCCI – Globalization support – Examples Specifying client application charactersets //OCCIUTF16 is for indicating UTF16 characterset Environment *env1 = Environment::createEnvironment(“WE8DEC”,”OCCIUTF16”); Environment *env2 = Environment::createEnvironment(“JA16SJIS”,”JA16SJIS”); Environment *env3
Slide 74: Globalization Support - Examples
OCCI – Globalization Support - Examples Using wstring in Windows //complete Unicode application Environment *env = Environment::createEnvironment(“OCCIUTF16”,”OCCIUTF16”); //wstring datatype is same as UString in Windows wstring wusername = L”scott”;//’L’ creates Unicode string wstring wpassword =
Slide 75: XA Support
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 applicatio
Slide 76: XA Application Architecture
OCCI – XA Application Architecture Uses OCCI Oracle C++ Call Interface(OCCI) 76
Slide 77: XA Support – Interfaces
OCCI – XA Support – Interfaces Get/release Oracle environment - Environment *Environment::getXAEnvironment(const string &dbname) - void Environment::releaseXAEnvironment(Environment *env) Get/release Oracle connection - Connection *Environment::getXAConnection(const string &dbname) - void Enviro
Slide 78: XA Support – Example
OCCI – XA Support – Example //define the db open string according to Oracle XA specification string xaopen_str = "oracle_xa+ACC=P/SCOTT/TIGER+SESTM=50+logdir=.+SqlNet=inst1"; //connect to database and other ‘resources’ using TM interfaces tx_open(); //to manipulate data need access to Connection Env
Slide 79: Stateless Connection Pooling
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 descr
Slide 80: Stateless Connection Pooling – II
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, use
Slide 81: Stateless Connection Pool – Examples
OCCI – Stateless Connection Pool – Examples Homogenous ConnectionPool StatlessConnectionPool *spool = Environment::createStatelessConnectionPool(“scott”, “tiger”, “”, 10, 4, 2, HOMOGENOUS);//MaxConn=10, MinConn=4, IncrConn=2 //get a connection from the pool Connection *conn = spool->getConnection()
Slide 82: Stateless Connection Pool - Examples
OCCI – Stateless Connection Pool - Examples Heterogenous ConnectionPool StatlessConnectionPool *spool = Environment::createStatelessConnectionPool(“scott”, “tiger”, “”, 10, 4, 2, HETEROGENOUS);//MaxConn=10, MinConn=4, IncrConn=2 //get a connection from the pool with a different user Connection *con
Slide 83: Statement Caching
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 r
Slide 84: Statement Caching – Usage
OCCI – Statement Caching – Usage Enable statement caching on a Connection :- - Connection::setStmtCacheSize(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 stat
Slide 85: Statement Caching – Example
OCCI – Statement Caching – Example //enable statement caching conn->setStmtCacheSize(10); //get cache size int csize = conn->getStmtCacheSize(); //create a statement Statement *stmt = conn->createStatement(sqlquery); //release the statement to cache with tag conn->terminateStatement(stmt, “tagA”); /
Slide 86: Interoperability with OCI
OCCI – Interoperability with OCI OCCI is designed and implemented on top of OCI Underlying OCI ‘handles’ of database access classes can be retrieved :- - OCIEnv *Environment::getOCIEnv() - OCIServer *Connection::getOCIServer() - OCISvcCtx *Connection::getOCIServiceContext() - OCISession *Connect
Slide 87: OCI Interoperability – Examples
OCCI – OCI Interoperability – Examples //get OCI Environment & Service Context OCIEnv *ocienv = env->getOCIEnv();//env is OCCI Environment OCISvcCtx *ocisvc = conn->getOCISvcCtx();//conn is Connection //use OCI calls OCIHandleAlloc(ocienv, &dpctx, OCI_HTYPE_DIRPATH_CTX,…); //do more OCI stuff DoDire
Slide 88: Performance Tuning
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 Retrie
Slide 89: Complex Object Retrieval(COR)
OCCI – Complex Object Retrieval(COR) Object oriented applications model their data as a set of interrelated objects to form graphs (using REFs) PurchaseOrder Cust REF Customer; RelatedOrder REF PurchaseOrder; LineItems LineItemRefArray Customer PurchaseOrder LineItem 1 LineItem 2 Performance of
Slide 90: COR – Example
OCCI – COR – Example //OTT class :- //class PurchaseOrder : { // Date OrderDate; // Ref <Customer> Customer; // Ref <PurchaseOrder> RelatedOrder; // vector < Ref<LineItem> > LineItems; } Ref<PurchaseOrder> poref; //set COR prefetching to get Customer object also poref.setPrefetch(“Customer”, 1); Dat
Slide 91: Tuning object cache
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) - Environment::setCacheOptSize(), Environment::setCacheMaxSize() For C++ datatype attributes(string, vector<T>), the memory
Slide 92: In closing…
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 Programmer’s Guide Oracle Application Developer’s Guide – Object-Relational
Slide 93: Demo and Q & A
Demo and Q&A Oracle C++ Call Interface(OCCI) 93