[oracle@lampeye demo]$ cat $ORACLE_HOME/rdbms/demo/cdemoqc.c
/* Copyright (c) 2007, Oracle. All rights reserved. */
/*
NAME
cdemoqc - Basic OCI Query Cache(result cache) functionality
DESCRIPTION
This program demonstrate the working of Query cache.
To use the query cache feature (and hence this program), database
should have been brought up with client side result cache enabled. To bring
the database with result cache enabled, add the following lines in
initialization parameter file,
client_result_cache_size=<cache size in bytes>
client_result_cache_lag=<timeout value for cache in ms>
compatible=11.0.0.0.0
and bring up the database.
*/
# include <oci.h>
# include <stdio.h>
# include <string.h>
# include <stdlib.h>
# include <time.h>
#include <unistd.h>
#define EMPLOYEE 0
/* Function prototypes */
static void query_tab();
static void dummy_query();
static void dbLogon();
static void dbLogoff();
static void Checkerr(OCIError *errhp, sword status, text *msg);
int main();
static sword status = 0;
static OCISvcCtx *svchp;
static OCIError *errhp;
static OCIEnv *envhp;
/* Queries with result cache hints */
static text *cache_query=
(text *)"SELECT /*+ result_cache */ empno, ename, sal FROM qctable";
//(text *)"SELECT empno, ename, sal FROM qctable";
static text *dummy= (text *)"SELECT COUNT(*) FROM DUAL";
/* - main -------------------------------------------------------------------*/
int main ()
{
long t;
time(&t);
printf ("Start -- %s",ctime(&t));
printf ("Query cache is enabled by using result_cache hints\n\n");
/* Logging on to multiple sessions */
dbLogon ();
printf ("Employee: Execute will fetch rows from server\n");
query_tab(EMPLOYEE);
printf ("\n\nEmployee: Execute will fetch rows from local cache, 4 times\n");
query_tab(EMPLOYEE);
query_tab(EMPLOYEE);
query_tab(EMPLOYEE);
query_tab(EMPLOYEE);
printf ("\n\nTotal query 5 times\nSleeping...30sec..\n");
sleep(30);
time(&t);
dummy_query(EMPLOYEE);
printf("dummyクエリ実行済み、１０秒待機中。\n");
printf("この間にclient_result_cache_stats$ビューを他端末からチェック！\n\n%s",
ctime(&t));
sleep(10);
dbLogoff ();
return 0;
}
static void dummy_query ()
{
OCIStmt *stmthp = (OCIStmt *)0;
OCIDefine *def1hp = (OCIDefine *)0;
ub4 ctr;
sb4 ctrSz = sizeof(ctr);
ub2 datelen=0;
ub4 prefetch = 0;
Checkerr (errhp,
OCIStmtPrepare2 ((OCISvcCtx *)svchp,(OCIStmt **)&stmthp,
(OCIError *)errhp, (text *)dummy, (ub4)strlen((char *)dummy),
(oratext *)NULL, (ub4) 0, (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT),
(oratext *)"OCIStmtPrepare");
/* Setting the prefetch count = 0 */
Checkerr(errhp,
OCIAttrSet((OCIStmt *) stmthp, OCI_HTYPE_STMT, (dvoid *)&prefetch,
sizeof(prefetch), OCI_ATTR_PREFETCH_ROWS, (OCIError *)errhp),
(oratext *) "OCIAttrSet-prefetch");
Checkerr (errhp,
OCIDefineByPos ((OCIStmt *)stmthp, (OCIDefine **)&def1hp,
(OCIError *)errhp, (ub4)1, (dvoid *)&(ctr), (sb4)ctrSz, (ub2)SQLT_INT,
(dvoid *)0, (ub2 *)0, (ub2 *)0, (ub4)OCI_DEFAULT),
(oratext *)"OCIDefByPos");
if ((status = OCIStmtExecute ((OCISvcCtx *)svchp, (OCIStmt *)stmthp,
(OCIError *)errhp, (ub4)0, (ub4)0, (OCISnapshot *)0,
(OCISnapshot *)0, (ub4)OCI_DEFAULT)) != OCI_SUCCESS )
{
printf ("OCIStmtExecute for Query Dual - Fail\n" );
Checkerr (errhp, status,(oratext *)"Stmt Execute");
}
else
{
status = OCIStmtFetch2((OCIStmt *)stmthp, (OCIError *)errhp, (ub4)1,
(ub2)OCI_FETCH_NEXT, (sb4)0, (ub4)OCI_DEFAULT);
if (status == OCI_ERROR || status == OCI_INVALID_HANDLE)
{
Checkerr(errhp, status, (oratext *)"OCIStmtFetch2");
return;
}
else if (status != OCI_NO_DATA)
{
printf("Dual selected.\n");
}
}
Checkerr (errhp,
OCIStmtRelease ((OCIStmt *)stmthp, (OCIError *)errhp,(dvoid *)NULL, 0,
OCI_DEFAULT), (oratext *)"StmtRelease");
}
/* - Logon to the DB in two sessions ----------------------------------------*/
static void dbLogon ()
{
ub4 cachesize=10;
OraText *connStr = (text *)"";
OraText *username = (text *)"ocitest";
OraText *password = (text *)"ocitest";
OCIAuthInfo *authhp = (OCIAuthInfo *)0;
OCIEnvCreate ((OCIEnv **)&envhp, (ub4)OCI_DEFAULT, (dvoid *)0,
(dvoid * (*)(dvoid *, size_t))0,
(dvoid * (*)(dvoid *, dvoid *, size_t))0,
(void (*)(dvoid *, dvoid *))0, (size_t)0, (dvoid **)0);
OCIHandleAlloc ((dvoid *) envhp, (dvoid **) &errhp, OCI_HTYPE_ERROR,
(size_t) 0, (dvoid **) 0);
OCIHandleAlloc ((dvoid *)envhp, (dvoid **)&authhp,
(ub4)OCI_HTYPE_AUTHINFO, (size_t)0, (dvoid **)0);
OCIAttrSet ((dvoid *)authhp, (ub4)OCI_HTYPE_AUTHINFO, (dvoid *)username,
(ub4)strlen((char *)username), (ub4)OCI_ATTR_USERNAME,
(OCIError *)errhp);
OCIAttrSet ((dvoid *)authhp, (ub4)OCI_HTYPE_AUTHINFO, (dvoid *)password,
(ub4)strlen((char *)password), (ub4)OCI_ATTR_PASSWORD,
(OCIError *)errhp);
Checkerr (errhp,
OCISessionGet ((OCIEnv *)envhp, (OCIError *)errhp,
(OCISvcCtx **)&svchp, (OCIAuthInfo *)authhp, (OraText *)connStr,
(ub4)strlen((char *)connStr), (OraText *)NULL, (ub4)0, (OraText **)0,
(ub4 *)0, (boolean *)0,(ub4)OCI_DEFAULT),
(oratext *)"OCISessionGet");
printf ("Connected to Employee Session\n");
OCIAttrSet((dvoid *)svchp, OCI_HTYPE_SVCCTX, (dvoid *)&cachesize,
(ub4)0,OCI_ATTR_STMTCACHESIZE,errhp);
}
/* - Execute SQL query and prints the data ----------------------------------*/
static void query_tab ()
{
OCIStmt *stmthp = (OCIStmt *)0;
OCIDefine *def1hp = (OCIDefine *)0;
OCIDefine *def2hp = (OCIDefine *)0;
OCIDefine *def3hp = (OCIDefine *)0;
ub4 empno;
text ename[100];
ub4 sal;
sb4 empnoSz = sizeof (empno);
sb4 enameSz = sizeof (ename);
sb4 salSz = sizeof (sal);
ub2 datelen=0;
ub4 prefetch = 0;
Checkerr (errhp,
OCIStmtPrepare2 ((OCISvcCtx *)svchp,(OCIStmt **)&stmthp,
(OCIError *)errhp, (text *)cache_query, (ub4)strlen((char *)cache_query),
(oratext *)NULL, (ub4) 0, (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT),
(oratext *)"OCIStmtPrepare");
/* Setting the prefetch count = 0 */
Checkerr(errhp,
OCIAttrSet((OCIStmt *) stmthp, OCI_HTYPE_STMT, (dvoid *)&prefetch,
sizeof(prefetch), OCI_ATTR_PREFETCH_ROWS, (OCIError *)errhp),
(oratext *) "OCIAttrSet-prefetch");
Checkerr (errhp,
OCIDefineByPos ((OCIStmt *)stmthp, (OCIDefine **)&def1hp,
(OCIError *)errhp, (ub4)1, (dvoid *)&(empno), (sb4)empnoSz, (ub2)SQLT_INT,
(dvoid *)0, (ub2 *)0, (ub2 *)0, (ub4)OCI_DEFAULT),
(oratext *)"OCIDefByPos");
Checkerr (errhp,
OCIDefineByPos ((OCIStmt *)stmthp, (OCIDefine **)&def2hp,
(OCIError *)errhp, (ub4)2, (dvoid *)&(ename), (sb4)enameSz, (ub2)SQLT_STR,
(dvoid *)0, (ub2 *)0, (ub2 *)0, (ub4)OCI_DEFAULT),
(oratext *)"OCIDefByPos2");
Checkerr (errhp,
OCIDefineByPos ((OCIStmt *)stmthp, (OCIDefine **)&def3hp,
(OCIError *)errhp, (ub4)3, (dvoid *)&(sal), (sb4)salSz, (ub2)SQLT_INT,
(dvoid *)0, (ub2 *)0, (ub2 *)0, (ub4)OCI_DEFAULT),
(oratext *)"OCIDefByPos3");
if ((status = OCIStmtExecute ((OCISvcCtx *)svchp, (OCIStmt *)stmthp,
(OCIError *)errhp, (ub4)0, (ub4)0, (OCISnapshot *)0,
(OCISnapshot *)0, (ub4)OCI_DEFAULT)) != OCI_SUCCESS )
{
printf ("OCIStmtExecute for SELECT - Fail\n" );
Checkerr (errhp, status,(oratext *)"Stmt Execute");
}
else
{
do
{
status = OCIStmtFetch2((OCIStmt *)stmthp, (OCIError *)errhp, (ub4)1,
(ub2)OCI_FETCH_NEXT, (sb4)0, (ub4)OCI_DEFAULT);
if (status == OCI_ERROR || status == OCI_INVALID_HANDLE)
{
Checkerr(errhp, status, (oratext *)"OCIStmtFetch2");
break;
}
else if (status != OCI_NO_DATA)
{
printf("\nEMPNO is %d, ENAME is %s, SAL is %d\n", empno, ename, sal);
}
}while(status != OCI_NO_DATA);
}
Checkerr (errhp,
OCIStmtRelease ((OCIStmt *)stmthp, (OCIError *)errhp,(dvoid *)NULL, 0,
OCI_DEFAULT), (oratext *)"StmtRelease");
}
/* - Session Logoff --------------------------------------------------------*/
static void dbLogoff ()
{
printf ("\nLogging off the connected session.\n");
Checkerr (errhp,
OCISessionRelease(svchp, errhp, 0,0, OCI_DEFAULT),
(oratext *) "Session-Release");
}
/* - Error checking routing -------------------------------------------------*/
void Checkerr(OCIError *errhp, sword status, text *msg)
{
text msgbuf[512];
sb4 errcode = 0;
memset((void *) msgbuf, (int)'\0', (size_t)512);
if(status!=OCI_SUCCESS)
{
printf("error msg: %s\n",msg);
}
switch (status)
{
case OCI_SUCCESS: break;
case OCI_SUCCESS_WITH_INFO:
printf("status = OCI_SUCCESS_WITH_INFO\n");
OCIErrorGet((dvoid *) errhp, (ub4) 1, (text *) NULL, &errcode,
msgbuf, (ub4) sizeof(msgbuf), (ub4) OCI_HTYPE_ERROR);
printf("ERROR CODE = %d\n", errcode);
printf("%.*s\n", 512, msgbuf);
if (errcode == 436 || errcode == 437 || errcode == 438 || errcode == 439)
exit(1);
break;
case OCI_NEED_DATA:
printf("status = OCI_NEED_DATA\n");
break;
case OCI_NO_DATA:
printf("status = OCI_NO_DATA\n");
break;
case OCI_ERROR:
printf("status = OCI_ERROR\n");
OCIErrorGet((dvoid *) errhp, (ub4) 1, (text *) NULL, &errcode,
msgbuf, (ub4) sizeof(msgbuf), (ub4) OCI_HTYPE_ERROR);
printf("ERROR CODE = %d\n", errcode);
printf("%.*s\n", 512, msgbuf);
if (errcode == 436 || errcode == 437 || errcode == 438 || errcode == 439)
exit(1);
break;
case OCI_INVALID_HANDLE:
OCIErrorGet((dvoid *) errhp, (ub4) 1, (text *) NULL, &errcode,
msgbuf, (ub4) sizeof(msgbuf), (ub4) OCI_HTYPE_ERROR);
printf("ERROR CODE = %d\n", errcode);
printf("%.*s\n", 512, msgbuf);
printf("status = OCI_INVALID_HANDLE\n");
break;
case OCI_STILL_EXECUTING:
printf("status = OCI_STILL_EXECUTE\n");
break;
case OCI_CONTINUE:
printf("status = OCI_CONTINUE\n");
break;
default:
break;
}
return;
}
|
最近のコメント