How to activate Oracle Client Caching at the
Client Side for OCI ApplicationsOn
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.
