Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







Activate Oracle Client Caching for OCI Applications

Oracle Database Tips by Donald BurlesonAugust 9, 2013

By Georgios Kalofyris


Also see these notes on OCI client cache.


How to activate Oracle Client Caching at the Client Side for OCI Applications

On previous article (OCI client cache) it was presented how to activate the Client Caching feature on the Oracle Server. However for an OCI API to indeed cache results as Client, some specific OCI implementation rules have to apply.

In short for the already accustomed OCI programmers, the Client Caching feature requires use of the OCIStmtPrepare2 for the Statement preparation instead of OCIStmtPrepare. OCIStmtPrepare2 function must go in pair with OCIStmtRelease for release of the Statement handle, and never with OCIHandleFree. Use of OCIHandleFree for a Statement prepared with OCIStmtPrepare2 triggers a fatal error.

That is because OCIHandleFree deallocates completely the relevant Handle from memory while OCIStmtPrepare2 in order to be able to cache the Statement, requires to release the Statement to the cache - in fact if you dont call OCIStmtRelease after Statement use, Client Caching will never happen on next calls. Of course if statement caching is deactivated OCIStmtRelease will deallocate the Statement just like OCIHandleFree.

Mind also that during connecting, you have to enable the correct attribute for Statement Caching. This is done differently for the 3 connecting functions OCILogon,OCILogon2,OCISessionGet - yet all support Client Caching.

When using OCILogon, you need a supplementary post-call to function OCIAttrSet to enable the attribute "OCI_ATR_STMTCACHESIZE"*.

When using OCILogon2, there is already 1 extra last parameter "mode" for that. You need to set "mode" to "OCI_LOGON2_STMTCACHE"

When using OCISessionGet, you can just set last parameter to "OCI_DEFAULT"

For fetching you can use either OCIStmtFetch or OCIStmtFetch2, its irrelevant to client caching.

If you want a full source code working implementation example, you can search for the C source file "cdemoqc.c" in Oracle Examples which you can download from oracle website.
To analyse a complete code implementation is beyond the scope of this short informative article.

* Client caching is actually a "Client Side" "Statement Caching". So an evolution/extension of the "Statement Caching" feature - that existed in Oracle for many years - to the client side .

Additional info & brief introduction for the inexperienced to OCI programming

We will in brief refer the common OCI procedure for connecting to the DBMS, with which most OCI programmers are already familiar.

First we have to create a connection to the DBMS of course. Second we have to allocate in the system memory a handle for the Statement that we intend to use. Then we may prepare the Statement for execution. Now we can execute the Statement and Fetch the results. The final step is to free from the memory the Statement Handle we allocated.
We see bellow the above steps in short.

Connect -> StmtHandleAlloc -> Prepare
-> Execute -> Fetch -> StmtHandleFree

Now the standard OCI functions equivalent of the above steps - many of which we already referred - are as follow.

OCILogon -> OCIHandleAlloc -> OCIStmtPrepare
-> OCIStmtExecute -> OCIStmtFetch-> OCIHandleFree

For Client caching however you have to use different functions for Preparing and Releasing the statement as we explained already

OCILogon -> OCIHandleAlloc -> OCIStmtPrepare2
-> OCIStmtExecute -> OCIStmtFetch -> OCIStmtRelease

You may refer to the Oracle Documentation for complete detail on the use of the functions, all their parameters and possible values.

Statement Functions -
Handle and Descriptor Functions -
OCI Handle Types -
OCI Relational Functions (Logon) -

Oracle® Call Interface Programmer's Guide 11g Release 1 (11.1) B28395-10 March 2013

pdf page 138 - online link :
"OCIStmtPrepare2(), is an enhanced version of OCIStmtPrepare() introduced to support statement caching"

pdf page 291 - online link :
"The OCI calls that implement statement caching are: OCIStmtPrepare2(), OCIStmtRelease()"

pdf page 292 - online link :
" A call to the OCIStmtPrepare2() must be followed with OCIStmtRelease() after the user is done with the statement handle. If statement caching is used, this will release the statement to the cache. If statement caching is not used, the statement will be deallocated. Do not call OCIHandleFree() to free the memory. "

Chapter 10. More OCI Advanced Topics : Client Caching

Chapter 9. OCI Programming Advanced Topics : Statement Caching in OCI
"Rules for Statement Caching in OCI : Use the function OCIStmtPrepare2() instead of OCIStmtPrepare(). If you are using OCIStmtPrepare(), you are strongly urged not to use a statement handle across different service contexts. Doing so raises an error if the statement has been obtained by OCIStmtPrepare2(). Migration of a statement handle to a new service context actually closes the cursor associated with the old session and therefore no sharing is achieved. Client-side sharing is also not obtained, because OCI frees all buffers associated with the old session when the statement handle is migrated. "

Oracle Training from Don Burleson 

The best on site "Oracle training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!

Oracle training



Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster