Sample C Code Using an OCI Session Pool
Use an OCI session pool to connect to the primary database and True Cache from a C application.
Warning: Do not use this code in a production environment.
This example mirrors the flow from Sample Java Code Using the JDBC Thin Driver:
- Create a session pool from the primary database service.
- Get a default session and verify that it is connected to the primary database.
- Get a read-only session and verify that it is connected to True Cache.
- Get a read-prefer session and verify that it uses True Cache when available; otherwise, it uses the primary database.
Before you compile and run the example, ensure that you have Oracle Client or Oracle AI Database 23.26.2 or later, a primary database service with an associated True Cache service, and a database user that can connect to both services.
Set ORACLE_HOME to the Oracle home, compile the program, and run it with the primary database connect string, user name, and password.
export ORACLE_HOME=/path/to/oracle/home
cc -O2 -Wall -Wextra -I$ORACLE_HOME/rdbms/public \
-o minimal_truecache_oci minimal_truecache_oci.c \
-L$ORACLE_HOME/lib -Wl,-rpath,$ORACLE_HOME/lib -lclntsh
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
./minimal_truecache_oci primary_host:1521/primary_service username passwordThe application passes only the primary database connect string. OCI selects the True Cache service when the application checks out a pooled session with OCI_SESSGET_READ_ONLY or OCI_SESSGET_READ_PREFER.
#include <oci.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
static void print_oci_error(OCIError *errhp, const char *where) {
text errbuf[1024];
sb4 errcode = 0;
memset(errbuf, 0, sizeof(errbuf));
OCIErrorGet((dvoid *)errhp, 1, NULL, &errcode, errbuf, sizeof(errbuf), OCI_HTYPE_ERROR);
fprintf(stderr, "%s: ORA-%d: %s\n", where, (int)errcode, errbuf);
}
static int set_auth_attr(OCIError *errhp, OCIAuthInfo *authhp, ub4 attr, const char *value, const char *label) {
sword rc = OCIAttrSet((dvoid *)authhp, OCI_HTYPE_AUTHINFO,
(dvoid *)value, (ub4)strlen(value),
attr, errhp);
if (rc != OCI_SUCCESS && rc != OCI_SUCCESS_WITH_INFO) {
print_oci_error(errhp, label);
return -1;
}
return 0;
}
static OCIAuthInfo *make_auth(OCIEnv *envhp, OCIError *errhp, const char *user, const char *password) {
OCIAuthInfo *authhp = NULL;
sword rc = OCIHandleAlloc((dvoid *)envhp, (dvoid **)&authhp, OCI_HTYPE_AUTHINFO, 0, NULL);
if (rc != OCI_SUCCESS) {
print_oci_error(errhp, "OCIHandleAlloc(OCI_HTYPE_AUTHINFO)");
return NULL;
}
if (set_auth_attr(errhp, authhp, OCI_ATTR_USERNAME, user, "OCIAttrSet(OCI_ATTR_USERNAME)") != 0 ||
set_auth_attr(errhp, authhp, OCI_ATTR_PASSWORD, password, "OCIAttrSet(OCI_ATTR_PASSWORD)") != 0) {
OCIHandleFree((dvoid *)authhp, OCI_HTYPE_AUTHINFO);
return NULL;
}
return authhp;
}
/*
* Check out one pooled session, run a lightweight verification query, print
* the routed instance/service, and release the session back to the pool.
*/
static int verify_connection(OCIEnv *envhp,
OCIError *errhp,
OCISPool *poolhp,
OraText *pool_name,
ub4 pool_name_len,
const char *user,
const char *password,
ub4 sessget_mode,
const char *label) {
OCIAuthInfo *authhp = NULL;
OCISvcCtx *svchp = NULL;
OCIStmt *stmthp = NULL;
OCIDefine *def1 = NULL;
OCIDefine *def2 = NULL;
OCIDefine *def3 = NULL;
OCIDefine *def4 = NULL;
char instance_name[128];
char server_host[256];
char service_name[256];
char db_unique_name[128];
sb2 ind1 = 0, ind2 = 0, ind3 = 0, ind4 = 0;
sword rc;
(void)poolhp;
memset(instance_name, 0, sizeof(instance_name));
memset(server_host, 0, sizeof(server_host));
memset(service_name, 0, sizeof(service_name));
memset(db_unique_name, 0, sizeof(db_unique_name));
/*
* Pass an OCIAuthInfo handle to OCISessionGet(). This is important for the
* True Cache checkout path; using NULL can crash or fail, depending on the
* client and server version and pool state.
*/
authhp = make_auth(envhp, errhp, user, password);
if (!authhp) return -1;
/*
* Use the pool name returned by OCISessionPoolCreate(), not the original
* connect string.
*
* sessget_mode controls routing:
* OCI_DEFAULT -> primary database
* OCI_SESSGET_READ_ONLY -> strict True Cache checkout
* OCI_SESSGET_READ_PREFER -> prefer True Cache, then primary database
*/
rc = OCISessionGet(envhp, errhp, &svchp, authhp,
pool_name, pool_name_len,
NULL, 0, NULL, NULL, NULL,
OCI_SESSGET_SPOOL | sessget_mode);
OCIHandleFree((dvoid *)authhp, OCI_HTYPE_AUTHINFO);
authhp = NULL;
if (rc != OCI_SUCCESS && rc != OCI_SUCCESS_WITH_INFO) {
print_oci_error(errhp, "OCISessionGet");
return -1;
}
rc = OCIHandleAlloc((dvoid *)envhp, (dvoid **)&stmthp, OCI_HTYPE_STMT, 0, NULL);
if (rc != OCI_SUCCESS) {
print_oci_error(errhp, "OCIHandleAlloc(OCI_HTYPE_STMT)");
OCISessionRelease(svchp, errhp, NULL, 0, OCI_DEFAULT);
return -1;
}
/*
* SYS_CONTEXT works for application users and does not require access to
* dynamic performance views such as V$DATABASE or V$INSTANCE.
*/
const char *sql =
"select sys_context('USERENV','INSTANCE_NAME'), "
" sys_context('USERENV','SERVER_HOST'), "
" sys_context('USERENV','SERVICE_NAME'), "
" sys_context('USERENV','DB_UNIQUE_NAME') "
"from dual";
rc = OCIStmtPrepare(stmthp, errhp, (text *)sql, (ub4)strlen(sql), OCI_NTV_SYNTAX, OCI_DEFAULT);
if (rc != OCI_SUCCESS && rc != OCI_SUCCESS_WITH_INFO) {
print_oci_error(errhp, "OCIStmtPrepare");
OCIHandleFree((dvoid *)stmthp, OCI_HTYPE_STMT);
OCISessionRelease(svchp, errhp, NULL, 0, OCI_DEFAULT);
return -1;
}
OCIDefineByPos(stmthp, &def1, errhp, 1, instance_name, sizeof(instance_name), SQLT_STR, &ind1, NULL, NULL, OCI_DEFAULT);
OCIDefineByPos(stmthp, &def2, errhp, 2, server_host, sizeof(server_host), SQLT_STR, &ind2, NULL, NULL, OCI_DEFAULT);
OCIDefineByPos(stmthp, &def3, errhp, 3, service_name, sizeof(service_name), SQLT_STR, &ind3, NULL, NULL, OCI_DEFAULT);
OCIDefineByPos(stmthp, &def4, errhp, 4, db_unique_name, sizeof(db_unique_name), SQLT_STR, &ind4, NULL, NULL, OCI_DEFAULT);
rc = OCIStmtExecute(svchp, stmthp, errhp, 0, 0, NULL, NULL, OCI_DEFAULT);
if (rc != OCI_SUCCESS && rc != OCI_SUCCESS_WITH_INFO) {
print_oci_error(errhp, "OCIStmtExecute");
OCIHandleFree((dvoid *)stmthp, OCI_HTYPE_STMT);
OCISessionRelease(svchp, errhp, NULL, 0, OCI_DEFAULT);
return -1;
}
rc = OCIStmtFetch2(stmthp, errhp, 1, OCI_FETCH_NEXT, 0, OCI_DEFAULT);
if (rc != OCI_SUCCESS && rc != OCI_SUCCESS_WITH_INFO) {
print_oci_error(errhp, "OCIStmtFetch2");
OCIHandleFree((dvoid *)stmthp, OCI_HTYPE_STMT);
OCISessionRelease(svchp, errhp, NULL, 0, OCI_DEFAULT);
return -1;
}
printf("%s\n", label);
printf(" instance_name : %s\n", instance_name);
printf(" server_host : %s\n", server_host);
printf(" service_name : %s\n", service_name);
printf(" db_unique_name: %s\n", db_unique_name);
OCIHandleFree((dvoid *)stmthp, OCI_HTYPE_STMT);
OCISessionRelease(svchp, errhp, NULL, 0, OCI_DEFAULT);
return 0;
}
int main(int argc, char **argv) {
OCIEnv *envhp = NULL;
OCIError *errhp = NULL;
OCISPool *poolhp = NULL;
OraText *pool_name = NULL;
ub4 pool_name_len = 0;
sword rc;
if (argc != 4) {
fprintf(stderr, "Usage: %s primary_host:1521/primary_service username password\n", argv[0]);
return 2;
}
const char *primary_connstr = argv[1];
const char *user = argv[2];
const char *password = argv[3];
rc = OCIEnvCreate(&envhp, OCI_THREADED, NULL, NULL, NULL, NULL, 0, NULL);
if (rc != OCI_SUCCESS) {
fprintf(stderr, "OCIEnvCreate failed\n");
return 1;
}
OCIHandleAlloc((dvoid *)envhp, (dvoid **)&errhp, OCI_HTYPE_ERROR, 0, NULL);
OCIHandleAlloc((dvoid *)envhp, (dvoid **)&poolhp, OCI_HTYPE_SPOOL, 0, NULL);
printf("Creating OCI True Cache session pool from primary service:\n %s\n", primary_connstr);
/*
* OCI_SPC_TRUECACHE enables multiple-service-pool mode for True Cache.
* Use the primary database service as the connect string. OCI discovers
* the True Cache target for read-only and read-prefer checkouts from the
* service configuration.
*/
rc = OCISessionPoolCreate(envhp, errhp, poolhp,
&pool_name, &pool_name_len,
(OraText *)primary_connstr, (ub4)strlen(primary_connstr),
1, 2, 1,
(OraText *)user, (ub4)strlen(user),
(OraText *)password, (ub4)strlen(password),
OCI_SPC_TRUECACHE);
if (rc != OCI_SUCCESS && rc != OCI_SUCCESS_WITH_INFO) {
print_oci_error(errhp, "OCISessionPoolCreate(OCI_SPC_TRUECACHE)");
OCIHandleFree((dvoid *)poolhp, OCI_HTYPE_SPOOL);
OCIHandleFree((dvoid *)errhp, OCI_HTYPE_ERROR);
OCIHandleFree((dvoid *)envhp, OCI_HTYPE_ENV);
return 1;
}
verify_connection(envhp, errhp, poolhp, pool_name, pool_name_len, user, password,
OCI_DEFAULT, "Default session, expected primary:");
verify_connection(envhp, errhp, poolhp, pool_name, pool_name_len, user, password,
OCI_SESSGET_READ_ONLY, "Read-only session, expected True Cache:");
verify_connection(envhp, errhp, poolhp, pool_name, pool_name_len, user, password,
OCI_SESSGET_READ_PREFER, "Read-prefer session, expected True Cache if available:");
OCISessionPoolDestroy(poolhp, errhp, OCI_DEFAULT);
OCIHandleFree((dvoid *)poolhp, OCI_HTYPE_SPOOL);
OCIHandleFree((dvoid *)errhp, OCI_HTYPE_ERROR);
OCIHandleFree((dvoid *)envhp, OCI_HTYPE_ENV);
return 0;
}