How to activate Oracle Client Caching at the
Client Side for OCI Applications
previous article (OCI
) 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
for the Statement preparation
instead of OCIStmtPrepare
function must go in pair with OCIStmtRelease
release of the Statement handle, and never with
. Use of OCIHandleFree
Statement prepared with OCIStmtPrepare2
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
after Statement use, Client Caching
will never happen on next calls. Of course if statement
caching is deactivated OCIStmtRelease
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
all support Client Caching.
, 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"
, you can just set last parameter to "OCI_DEFAULT"
For fetching you can use either OCIStmtFetch
, its irrelevant to client
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
* 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.
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
We see bellow the above steps in short.
Connect -> StmtHandleAlloc -> Prepare
-> Fetch -> StmtHandleFree
Now the standard OCI
functions equivalent of the above steps - many of which
we already referred - are as follow.
OCIHandleAlloc -> OCIStmtPrepare
-> OCIStmtExecute ->
For Client caching
however you have to use different functions for
Preparing and Releasing the statement as we explained
OCILogon -> OCIHandleAlloc ->
-> OCIStmtExecute -> OCIStmtFetch ->
You may refer to the Oracle
Documentation for complete detail on the use of the
functions, all their parameters and possible values.