#ifdef __GNUC__
# define _int64 long long
#endif
#ifdef __cplusplus
extern "C" {
#endif
#include <oci.h>
struct oci_connection {
/* The environment handle defines a context in which all OCI functions are
invoked. Each environment handle contains a memory cache, which allows for
fast memory access. All memory allocation under the environment handle is
done from this cache. Access to the cache is serialized if multiple
threads try to allocate memory under the same environment handle. When
multiple threads share a single environment handle, they may block on
access to the cache. The environment handle is passed as the parent
parameter to the OCIHandleAlloc() call to allocate all other handle types.
Bind and define handles are allocated implicitly.
*/
OCIEnv* env;
/* The error handle is passed as a parameter to most OCI calls. The error
handle maintains information about errors that occur during an OCI
operation. If an error occurs in a call, the error handle can be passed to
OCIErrorGet() to obtain additional information about the error that
occurred. Allocating the error handle is one of the first steps in an OCI
application because most OCI calls require an error handle as one of its
parameters. */
OCIError* err;
OCIServer* srv;
OCISvcCtx* svc;
OCISession* ses;
};
struct oci_description {
text col_name[31];
ub2 col_type;
};
void checkerr(OCIError* err, sword status, char errbuf[512]);
void parse_connect_string (
char* connect_str, /* in */
text username[31], /* out */
text password[31], /* out */
text dbname [31] /* out */
);
int oci_describe(
OCIStmt* sh,
/* char* errmsg,*/
struct oci_connection* conn,
struct oci_description* descr,
int pos);
int oci_connect(
text username[30],
text password[30],
text dbname [30],
struct oci_connection* connection,
char errmsg [512]
);
int oci_parse_and_execute(
char* stmt,
char errmsg[512],
struct oci_connection* conn
);
int oci_execute0(
OCIStmt* sh,
char* errmsg,
struct oci_connection* conn);
OCIStmt* oci_parse(
char* stmt,
char errmsg[512],
struct oci_connection* conn );
int oci_execute(
OCIStmt* sh,
char* errmsg,
struct oci_connection* conn,
int nof_binds,
...);
int oci_fetch(
OCIStmt* sh,
char* errmsg,
struct oci_connection* conn);
int oci_define(
OCIStmt* sh,
char* errmsg,
struct oci_connection* conn,
int nof_defines,
...);
int oci_commit(struct oci_connection* conn, char* errmsg);
#ifdef __cplusplus
}
#endif
#include <string.h>
#include <stdio.h>
#include "tq84_oci.h"
static int oci_env_created = 0;
int oci_connect(
text username[31],
text password[31],
text dbname [31],
struct oci_connection* conn,
char errmsg[512]
) {
sword r;
if (!oci_env_created) {
r=OCIEnvCreate( &conn->env,
OCI_DEFAULT |
OCI_THREADED
//OCI_OBJECT
//OCI_EVENTS
//OCI_NO_UCB
//OCI_ENV_NO_MUTEX
//OCI_NEW_LENGTH_SEMANTICS
, 0, 0, 0, 0, 0, 0);
if (r != OCI_SUCCESS) {
strcpy((char*) errmsg, "Couldn't create environment (OCIEnvCreate");
goto clean_up;
}
oci_env_created = 1;
}
OCIHandleAlloc(conn->env, (dvoid**)&conn->err, OCI_HTYPE_ERROR, 0, 0);
OCIHandleAlloc(conn->env, (dvoid**)&conn->srv, OCI_HTYPE_SERVER, 0, 0);
OCIHandleAlloc(conn->env, (dvoid**)&conn->svc, OCI_HTYPE_SVCCTX, 0, 0);
OCIHandleAlloc(conn->env, (dvoid**)&conn->ses, OCI_HTYPE_SESSION, 0, 0);
// r = OCILogon(conn->env, conn->err, &conn->svc,
// username, strlen((const char*) username),
// password, strlen((const char*) password),
// dbname , strlen((const char*) dbname ));
r=OCIServerAttach(conn->srv, conn->err, (text*) dbname, strlen((const char*)dbname), (ub4) OCI_DEFAULT);
if (r != OCI_SUCCESS) {
checkerr(conn->err, r, errmsg);
goto clean_up;
}
OCIAttrSet(conn->svc, OCI_HTYPE_SVCCTX, conn->srv, 0, OCI_ATTR_SERVER, conn->err);
OCIAttrSet(conn->ses, OCI_HTYPE_SESSION, username, strlen((const char*)username), OCI_ATTR_USERNAME, conn->err);
OCIAttrSet(conn->ses, OCI_HTYPE_SESSION, password, strlen((const char*)password), OCI_ATTR_PASSWORD, conn->err);
if ( (r=OCIAttrSet((dvoid *) conn->svc, (ub4) OCI_HTYPE_SVCCTX, (dvoid *) conn->ses, (ub4) 0, (ub4) OCI_ATTR_SESSION, conn->err)) ) {
checkerr(conn->err, r,errmsg);
goto clean_up;
}
r=OCISessionBegin (conn->svc, conn->err, conn->ses, OCI_CRED_RDBMS, OCI_DEFAULT);
if (r != OCI_SUCCESS) {
checkerr(conn->err, r,errmsg);
goto clean_up;
}
return 1;
clean_up:
//strcpy(errmsg,(const char*) errbuf);
return 0;
}
void checkerr(OCIError* err, sword status, char errbuf[512]) {
sb4 errcode;
switch (status) {
case OCI_SUCCESS:
strcpy(errbuf, "No error");
break;
case OCI_SUCCESS_WITH_INFO:
strcpy(errbuf, "OCI_SUCCESS_WITH_INFO");
break;
case OCI_NEED_DATA:
strcpy(errbuf, "OCI_NEED_DATA");
break;
case OCI_NO_DATA:
strcpy(errbuf, "OCI_NO_DATA");
break;
case OCI_ERROR:
OCIErrorGet (err, (ub4) 1, (text*) NULL, &errcode, (text*) errbuf, (ub4) 512 , (ub4) OCI_HTYPE_ERROR);
break;
case OCI_INVALID_HANDLE:
strcpy(errbuf, "OCI_INVALID_HANDLE");
break;
case OCI_STILL_EXECUTING:
strcpy(errbuf, "OCI_STILL_EXECUTING");
break;
case OCI_CONTINUE:
strcpy(errbuf, "OCI_CONTINUE");
break;
default:
strcpy(errbuf, "Unknown error");
break;
}
}
void parse_connect_string(
char* connect_str, /* in */
text username[31], /* out */
text password[31], /* out */
text dbname [31] /* out */
) {
username[0] = 0;
password[0] = 0;
dbname [0] = 0;
text* to=username;
while (*connect_str) {
if (*connect_str == '/') {
*to=0;
to=password;
connect_str++;
continue;
}
if (*connect_str == '@') {
*to=0;
to=dbname;
connect_str++;
continue;
}
*to=*connect_str;
to++;
connect_str++;
}
*to=0;
}
int oci_parse_and_execute(
char* stmt,
char errmsg[512],
struct oci_connection* conn
) {
OCIStmt *sh;
sword r;
if ((r=OCIHandleAlloc((dvoid *) conn->env, (dvoid **) &sh, (ub4) OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0))) {
checkerr(conn->err, r, errmsg);
return 0;
}
if ((r=OCIStmtPrepare(sh, conn->err, (text*) stmt, (ub4) strlen(stmt), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT))) {
checkerr(conn->err, r, errmsg);
OCIHandleFree((dvoid *) sh, (ub4) OCI_HTYPE_STMT);
return 0;
}
if ((r=OCIStmtExecute(conn->svc, sh, conn->err, (ub4) 1, (ub4) 0, (CONST OCISnapshot *) 0, (OCISnapshot *) 0, (ub4) OCI_DEFAULT))) {
checkerr(conn->err, r, errmsg);
OCIHandleFree((dvoid *) sh, (ub4) OCI_HTYPE_STMT);
return 0;
}
OCIHandleFree((dvoid *) sh, (ub4) OCI_HTYPE_STMT);
return 1;
}
OCIStmt* oci_parse(
char* stmt,
char errmsg[512],
struct oci_connection* conn ) {
OCIStmt* sh;
sword r;
if ( (r=OCIHandleAlloc((dvoid*) conn->env, (dvoid **) &sh, (ub4) OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0)) ) {
checkerr(conn->err, r, errmsg);
return 0;
}
if ((r=OCIStmtPrepare(sh, conn->err, (text*)(void*)stmt, (ub4) strlen((char *) stmt), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT))) {
checkerr(conn->err, r, errmsg);
OCIHandleFree((dvoid*) sh, (ub4) OCI_HTYPE_STMT);
return 0;
}
return sh;
}
int oci_define(
OCIStmt* sh,
char* errmsg,
struct oci_connection* conn,
int nof_defines,
...) {
OCIDefine *defnp = (OCIDefine *) 0;
sword r;
va_list defines;
dvoid* value_ptr;
int sz;
int pos;
ub2 dt_type;
va_start(defines, nof_defines);
for (pos=0;pos<nof_defines; pos++) {
dt_type = va_arg(defines, int);
switch (dt_type) {
case SQLT_INT:
// printf("SQLT_INT\n");
value_ptr=va_arg(defines, dvoid*);
sz = sizeof(int);
break;
case SQLT_FLT:
// printf("SQLT_FLT\n");
value_ptr = va_arg(defines, dvoid*);
sz = sizeof(double);
break;
case SQLT_CHR:
case SQLT_STR:
// printf("SQLT_STR\n");
value_ptr = va_arg(defines, dvoid*);
sz = va_arg(defines, int); // or should this be long?
break;
case SQLT_ODT:
// printf("SQLT_ODT\n");
value_ptr = va_arg(defines, dvoid*);
sz = sizeof(OCIDate);
break;
default:
// "THIS MUST NOT HAPPEN";
strcpy(errmsg, "adp_oci_error 0001, datatype not yet implemented");
return 0;
}
if ( (r=OCIDefineByPos(
sh,
&defnp,
conn->err,
pos+1,
value_ptr,
sz,
dt_type,
(dvoid *) 0, // indp
(ub2 *) 0, // rlenp
(ub2 *) 0, // rcodep
OCI_DEFAULT) )) {
checkerr(conn->err, r, errmsg);
return 0;
}
}
return 1;
}
int oci_execute(
OCIStmt* sh,
char* errmsg,
struct oci_connection* conn,
int nof_binds,
...) {
OCIBind *bndhp = (OCIBind*) 0;
sword r;
int sz;
int pos;
va_list binds;
ub2 dt_type;
dvoid *value_ptr;
int i;
double d;
va_start(binds, nof_binds);
for (pos=0;pos<nof_binds;pos++) {
dt_type = va_arg(binds, int);
switch (dt_type) {
case SQLT_INT:
i = va_arg(binds, int);
// printf("SQLT_INT: %d\n", i);
sz = sizeof(int);
value_ptr= &i;
break;
case SQLT_FLT:
d = va_arg(binds, double);
// printf("SQLT_FLT: %f\n", d);
sz = sizeof(double);
value_ptr = &d;
break;
case SQLT_CHR:
value_ptr = va_arg(binds, char*);
// printf("SQLT_CHR: %s\n", (char*) value_ptr);
sz = strlen( (char*) value_ptr);
break;
case SQLT_ODT:
// printf("SQLT_ODT\n");
value_ptr = va_arg(binds, OCIDate*);
sz = sizeof(OCIDate);
break;
}
if ((r=OCIBindByPos(sh, &bndhp, conn->err,
(ub4) pos+1, // position
value_ptr, // value pointer
sz, // size
dt_type,
(dvoid *) 0, // indp
(ub2 *) 0, // alenp
(ub2 *) 0, // rcodep
(ub4 ) 0, // maxarr_len
(ub4 *) 0, // curelp
(ub4) OCI_DEFAULT))) {
checkerr(conn->err, r, errmsg);
return 0;
}
}
if ((r=OCIStmtExecute(
conn->svc,
sh,
conn->err,
//-------
(ub4) 1, // iters
// (ub4) 0, // iters
//-------
(ub4) 0, (CONST OCISnapshot *) 0, (OCISnapshot *) 0, (ub4) OCI_DEFAULT))) {
checkerr(conn->err, r, errmsg);
OCIHandleFree((dvoid*) sh, (ub4) OCI_HTYPE_STMT);
return 0;
}
return 1;
}
/*
Todo: Getting the number of columns in the query :
OCIAttrGet(stmhp, OCI_HTYPE_STMT, &numcols, 0, OCI_ATTR_PARAM_COUNT, errh);
*/
int oci_execute0(
OCIStmt* sh,
char* errmsg,
struct oci_connection* conn) {
sword r;
if ((r=OCIStmtExecute(
conn->svc,
sh,
conn->err,
(ub4) 0, // iters
(ub4) 0, (CONST OCISnapshot *) 0, (OCISnapshot *) 0, (ub4) OCI_DEFAULT))) {
checkerr(conn->err, r, errmsg);
OCIHandleFree((dvoid*) sh, (ub4) OCI_HTYPE_STMT);
return 0;
}
return 1;
}
int oci_fetch(
OCIStmt* sh,
char* errmsg,
struct oci_connection* conn) {
sb4 r;
r = OCIStmtFetch(
sh,
conn->err,
(ub4) 1,
(ub4) OCI_FETCH_NEXT,
(ub4) OCI_DEFAULT);
if (r == OCI_SUCCESS) {
return 1;
}
else if (r == OCI_SUCCESS_WITH_INFO) {
return 1;
}
else {
if (r != OCI_NO_DATA) {
checkerr(conn->err, r, errmsg);
return 0;
}
return 0;
}
}
// Metalink note 125057.1
int oci_describe(
OCIStmt* sh,
/* char* errmsg, */
struct oci_connection* conn,
struct oci_description* descr,
int pos) {
text* colName;
OCIParam* param = 0;
ub4 len;
sb4 status;
status = OCIParamGet(sh, OCI_HTYPE_STMT, conn->err, (void**)¶m, (ub4) pos);
if (status == OCI_SUCCESS) {
len = sizeof(sb2);
OCIAttrGet((dvoid*)param, (ub4) OCI_DTYPE_PARAM, (dvoid*) &(descr->col_type), &len, (ub4) OCI_ATTR_DATA_TYPE, conn->err);
len = sizeof(sb4);
OCIAttrGet((dvoid*)param, (ub4) OCI_DTYPE_PARAM, (dvoid**)&colName , &len, (ub4) OCI_ATTR_NAME , conn->err);
// printf("len: %d\n",len);
// descr->col_name[len]=0;
memcpy(descr->col_name, colName, len);
descr->col_name[len]=0;
return 1;
}
else {
return 0;
}
}
int oci_commit(struct oci_connection* conn, char* errmsg) {
sb4 r;
if ((r=OCITransCommit(conn->svc, conn->err, 0))) {
checkerr(conn->err, r, errmsg);
return 0;
}
return 1;
}
#include <stdio.h>
#include <stdlib.h>
#include "tq84_oci.h"
int main(int argc, char* argv[]) {
char err_msg [512];
struct oci_connection conn;
if (argc < 2) {
printf("usage %s username/password[@dbname]\n", argv[0]);
exit (-1);
}
text username[31];
text password[31];
text dbname [31];
parse_connect_string(argv[1],username, password, dbname);
if (!oci_connect(username, password, dbname, &conn, err_msg)) {
printf("Error with oci_connect: %s\n",err_msg);
goto clean_up;
}
if (!oci_parse_and_execute("create table oci_test_table ( "
" number_ number, "
" number_5_3 number(5,3), "
" varchar2_20 varchar2(20), "
// " blob_ blob, "
// " clob_ clob, "
" date_ date) ",
err_msg, &conn) ) {
printf(err_msg);
goto clean_up;
}
printf("A table named oci_test_table should now be created\n");
clean_up:
OCITerminate(OCI_DEFAULT);
return 0;
}
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include "tq84_oci.h"
int main(int argc, char* argv[]) {
char err_msg [512];
struct oci_connection conn;
int i;
double d;
char* c;
OCIStmt* sh;
OCIDate aug_28th_1970_22h23m44s;
OCIDate feb_01th_2003_04h05m06s;
if (argc < 2) {
printf("usage %s username/password[@dbname]\n", argv[0]);
exit (-1);
}
text username[30];
text password[30];
text dbname [30];
parse_connect_string(argv[1], username, password, dbname);
if (!oci_connect(username, password, dbname, &conn, err_msg)) {
printf(err_msg);
goto clean_up;
}
if (! (sh=oci_parse("insert into oci_test_table (number_, number_5_3, varchar2_20, date_) values (:1, :2, :3, :4)", err_msg, &conn))) {
printf("could not oci_parse: %s\n", err_msg);
goto clean_up;
}
OCIDateSetTime(&aug_28th_1970_22h23m44s, 22, 23, 44);
OCIDateSetDate(&aug_28th_1970_22h23m44s, 1970, 8, 28);
if (!oci_execute(sh, err_msg, &conn, 4,
SQLT_INT, 1 ,
SQLT_FLT, 4.8,
SQLT_CHR, "first",
SQLT_ODT, &aug_28th_1970_22h23m44s
)) {
printf("could not oci_execute (1): %s\n", err_msg);
goto clean_up;
}
i = 10;
d = 24.44;
c = (char*) malloc(21);
strcpy(c, "second");
OCIDateSetTime(&feb_01th_2003_04h05m06s, 4, 5, 6);
OCIDateSetDate(&feb_01th_2003_04h05m06s, 2003, 2, 1);
if (!oci_execute(sh, err_msg, &conn, 4,
SQLT_INT, i,
SQLT_FLT, d,
SQLT_CHR, c,
SQLT_ODT, &feb_01th_2003_04h05m06s)) {
printf("could not oci_execute (2): %s\n", err_msg);
goto clean_up;
}
free(c);
oci_parse_and_execute("commit",err_msg,&conn);
clean_up:
OCITerminate(OCI_DEFAULT);
return 0;
}
#include <stdio.h>
#include <stdlib.h>
#include "tq84_oci.h"
int main(int argc, char* argv[]) {
char err_msg [512];
struct oci_connection conn;
OCIStmt* sh;
int i_out;
double d_out;
char* c_out;
OCIDate dt_out;
int day, month, year, hour, minute, second;
c_out = (char*) malloc(21);
if (argc < 2) {
printf("usage %s username/password[@dbname]\n", argv[0]);
exit (-1);
}
text username[31];
text password[31];
text dbname [31];
parse_connect_string(argv[1],username, password, dbname);
if (! oci_connect(username, password, dbname,&conn, err_msg)) {
printf(err_msg);
goto clean_up;
}
if (! (sh=oci_parse("select number_, number_5_3, varchar2_20, date_ from oci_test_table where "
"number_ > :1 or number_5_3 = :2 or varchar2_20 < :3", err_msg, &conn))) {
printf(err_msg);
goto clean_up;
}
if (! oci_define(sh, err_msg, &conn, 4, SQLT_INT, &i_out, SQLT_FLT, &d_out, SQLT_CHR, c_out, 20, SQLT_ODT, &dt_out)) {
printf(err_msg);
goto clean_up;
}
if (! oci_execute(sh, err_msg, &conn, 3, SQLT_INT, 5, SQLT_FLT, 4.8, SQLT_CHR, "zzz")) {
printf(err_msg);
goto clean_up;
}
while (oci_fetch(sh, err_msg,&conn)) {
OCIDateGetDate(&dt_out, &year, &month , &day );
OCIDateGetTime(&dt_out, &hour, &minute, &second);
printf (" %d %f %s %02d.%02d.%04d %02d:%02d:%02d\n", i_out, d_out, c_out, day, month, year, hour, minute, second);
}
clean_up:
free (c_out);
OCITerminate(OCI_DEFAULT);
printf("\n");
return 0;
}
select * statement. #include <stdio.h>
#include <stdlib.h>
#include "tq84_oci.h"
int main(int argc, char* argv[]) {
char err_msg [512];
// sword r;
struct oci_connection conn;
OCIStmt* sh;
int pos;
struct oci_description descr;
// int numCol;
if (argc < 2) {
printf("usage %s username/password[@dbname]\n", argv[0]);
exit (-1);
}
text username[30];
text password[30];
text dbname [30];
parse_connect_string(argv[1],username, password, dbname);
if (! oci_connect(username, password, dbname, &conn, err_msg)) {
printf("Error with oci_connect: %s\n",err_msg);
goto clean_up;
}
if (! (sh=oci_parse("select * from oci_test_table", err_msg, &conn))) {
printf(err_msg);
goto clean_up;
}
if (! oci_execute0(sh, err_msg, &conn)) {
printf("after x\n");
printf(err_msg);
goto clean_up;
}
// OCIAttrGet(sh, OCI_HTYPE_STMT, &numCol, 0, OCI_ATTR_PARAM_COUNT, conn.err);
// printf("Nof: %d\n", numCol);
pos = 1;
while (oci_describe(sh, /*err_msg,*/ &conn, &descr, pos)) {
printf("%-30s %d:\n", descr.col_name, descr.col_type) ;
pos++;
}
clean_up:
OCITerminate(OCI_DEFAULT);
return 0;
}
dbms_pipe) and prints them to the console: #include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include "tq84_oci.h"
int main(int argc, char* argv[]) {
char err_msg [512];
sword r;
struct oci_connection conn;
OCIStmt* sh;
if (argc < 3) {
printf("usage %s username/password[@dbname] msg_name\n", argv[0]);
exit (-1);
}
text username[31];
text password[31];
text dbname [31];
int s;
char item [100];
strcpy(item,
" "
" "
" "
" "
" "
" "
" "
" "
" ");
char stmt [] =
" declare "
" s integer; "
" item varchar2(200); "
" begin "
" item := ''; "
" s := sys.dbms_pipe.receive_message(:1); "
" if s = 0 then "
" dbms_pipe.unpack_message(item); "
" end if; "
" :2 := s; "
" :3 := item; "
" end; ";
parse_connect_string(argv[1], username, password, dbname);
if (!oci_connect(username, password, dbname, &conn, err_msg)) {
printf(err_msg);
goto clean_up;
}
if (! (sh=oci_parse(stmt, err_msg, &conn ))) {
printf(err_msg);
goto clean_up;
}
printf("Waiting for messages\n");
for (;;) {
if (! (r=oci_execute(sh, err_msg, &conn,
3, // 3 Bind variables used:
// ---------------------
SQLT_CHR, argv[2], // :1 pipe_name
SQLT_INT, &s, // :2 return value of dbms_pipe.receive_message (s)
SQLT_CHR, item) // :3 the message received (item)
)) {
printf(err_msg);
break;
}
if (s != 0) {
printf("Abnormal pipe status: %d %s\n\r", s, item);
}
else {
printf("%s\n", item);
}
}
clean_up:;
return 0;
}
--
-- Use this script to write the text 'hello world'
-- into a message named 'queue_name'.
-- Then use
-- .\read_pipe.exe user/pw queue_name
--
declare
status number;
begin
dbms_pipe.pack_message('hello world');
status := dbms_pipe.send_message('queue_name');
end;
/
gcc -I$ORACLE_HOME/rdbms/demo -I$ORACLE_HOME/rdbms/public src.c -o executable -L$ORACLE_HOME/lib -lclntsh
gcc -I%ORACLE_HOME%/oci/include src.c %ORACLE_HOME%/bin/oci.dll -o executable.exe
CFLAGS=-Wall -Wextra -I. -I.. -I$(ORACLE_HOME)/oci/include CC=gcc all: ../tq84_oci.o 01_create_table.exe 02_insert_table.exe 03_select_table.exe 04_describe_table.exe read_pipe.exe %.exe: %.o ../tq84_oci.o gcc $(CFLAGS) -I.. $^ -o $@ $(ORACLE_HOME)/bin/oci.dll ../tq84_oci.o: ../tq84_oci.c gcc $(CFLAGS) $^ -c $@