int main(void)
{
OCI_Connection *cn;
OCI_Statement *st;
int code;
if (!OCI_Initialize(NULL, NULL, OCI_ENV_DEFAULT))
return EXIT_FAILURE;
cn = OCI_ConnectionCreate("db", "usr", "pwd", OCI_SESSION_DEFAULT);
st = OCI_StatementCreate(cn);
OCI_Prepare(st, "delete from test_fetch where code = :code");
OCI_BindInt(st, ":code", &code);
code = 5;
OCI_Execute(st);
code = 12;
OCI_Execute(st);
OCI_Commit(cn);
OCI_Cleanup();
return EXIT_SUCCESS;
}
double get_estoque(requisicao_estoque *req) {
double saldo;
OCI_Statement *stmt = OCI_StatementCreate(oracledb);
check_ociliberror(stmt);
OCI_Prepare(stmt, "select sum(SALD_CTR) from table(saldo_inicial_tipoest(:emp, :est, :prod, :data, 'S', null, 0))");
OCI_BindShort(stmt, ":emp", &req->empresa);
OCI_BindShort(stmt, ":est", &req->controle);
OCI_BindString(stmt, ":prod", req->produto, 0);
OCI_Date *d = OCI_DateCreate(NULL);
OCI_DateSetDate(d, req->data_ano, req->data_mes, req->data_dia);
OCI_BindDate(stmt, ":data", d);
check_ociliberrorb(OCI_Execute(stmt));
OCI_Resultset *rs = OCI_GetResultset(stmt);
check_ociliberror(rs);
if (OCI_FetchNext(rs)) {
saldo = OCI_GetDouble(rs, 1);
}
else {
saldo = 0.0;
}
OCI_FreeStatement(stmt);
return saldo;
}
int main(void)
{
OCI_Connection *cn;
OCI_Statement *st;
int res = 0;
if (!OCI_Initialize(err_handler, NULL, OCI_ENV_DEFAULT))
{
return EXIT_FAILURE;
}
cn = OCI_ConnectionCreate("db", "usr", "pwd", OCI_SESSION_DEFAULT);
st = OCI_StatementCreate(cn);
/* pl/sql call */
OCI_Prepare(st, "begin :res := trunc(sysdate+1)-trunc(sysdate-1); end;");
OCI_BindInt(st, ":res", &res);
OCI_Execute(st);
printf("result : %i\n", res);
OCI_StatementFree(st);
OCI_ConnectionFree(cn);
OCI_Cleanup();
return EXIT_SUCCESS;
}
int main(void)
{
OCI_Connection *cn;
OCI_Statement *st;
OCI_Resultset *rs;
if (!OCI_Initialize(NULL, NULL, OCI_ENV_DEFAULT))
return EXIT_FAILURE;
cn = OCI_ConnectionCreate("db", "usr", "pwd", OCI_SESSION_DEFAULT);
st = OCI_StatementCreate(cn);
OCI_Prepare(st, "update products set code = code+10 returning code into :i");
OCI_RegisterInt(st, ":i");
OCI_Execute(st);
rs = OCI_GetResultset(st);
while (OCI_FetchNext(rs))
printf("%i\n", OCI_GetInt(rs, 1));
printf("count : %i\n", OCI_GetRowCount(rs));
OCI_Commit(cn);
OCI_Cleanup();
return EXIT_SUCCESS;
}
int main(void)
{
OCI_Connection *cn;
OCI_Statement *st;
OCI_Resultset *rs;
char rowid[OCI_SIZE_ROWID + 1] = "";
if (!OCI_Initialize(err_handler, NULL, OCI_ENV_DEFAULT))
{
return EXIT_FAILURE;
}
cn = OCI_ConnectionCreate("db", "usr", "pwd", OCI_SESSION_DEFAULT);
st = OCI_StatementCreate(cn);
OCI_Immediate(cn, "select rowid from products where code = 1", OCI_ARG_TEXT, rowid);
OCI_Prepare(st, "select code, name, rowid from products where rowid = :id");
OCI_BindString(st, ":id", rowid, (unsigned int) strlen(rowid));
OCI_Execute(st);
rs = OCI_GetResultset(st);
OCI_FetchNext(rs);
printf("code [%d], name [%s], rowid [%s]", OCI_GetInt(rs, 1), OCI_GetString(rs, 2), OCI_GetString(rs, 3));
OCI_StatementFree(st);
OCI_ConnectionFree(cn);
OCI_Cleanup();
return EXIT_SUCCESS;
}
int main(void)
{
OCI_Connection *cn;
OCI_Statement *st;
OCI_Error *err;
int tab_int[1000];
char tab_str[1000][21];
int i;
if (!OCI_Initialize(NULL, NULL, OCI_ENV_DEFAULT | OCI_ENV_CONTEXT))
return EXIT_FAILURE;
/* ... create connection and statement ... */
cn = OCI_ConnectionCreate("db", "usr", "pwd", OCI_SESSION_DEFAULT);
st = OCI_StatementCreate(cn);
/* binding */
OCI_Prepare(st, "insert into products values(:i, :s)");
OCI_BindArraySetSize(st, 1000);
OCI_BindArrayOfInts(st, ":i", (int*) tab_int, 0);
OCI_BindArrayOfStrings(st, ":s", (char*) tab_str, 20, 0);
/* filling arrays */
for(i=0;i<1000;i++)
{
tab_int[i] = i+1;
sprintf(tab_str[i],"Name %d",i+1);
}
/* execute */
if (!OCI_Execute(st))
{
printf("Number of DML array errors : %d\n", OCI_GetBatchErrorCount(st));
err = OCI_GetBatchError(st);
while (err)
{
printf("Error at row %d : %s\n", OCI_ErrorGetRow(err), OCI_ErrorGetString(err));
err = OCI_GetBatchError(st);
}
}
printf("row processed : %d\n", OCI_GetAffectedRows(st));
OCI_Commit(cn);
OCI_Cleanup();
return EXIT_SUCCESS;
}
int ociw_query_result(db_wrap * self, char const * sql, size_t len, db_wrap_result ** tgt)
{
CONN_DECL(DB_WRAP_E_BAD_ARG);
if (! sql || !*sql || !len || !tgt) return DB_WRAP_E_BAD_ARG;
OCI_Statement * st = OCI_StatementCreate(ociconn);
if (! st)
{
lerr("Creation of OCI_Statement failed.\n");
return DB_WRAP_E_CHECK_DB_ERROR;
}
db_wrap_result * wres = (db_wrap_result*)malloc(sizeof(db_wrap_result));
if (! wres) return DB_WRAP_E_ALLOC_ERROR;
*wres = ociw_res_empty;
ociw_result_impl * impl = (ociw_result_impl*)malloc(sizeof(ociw_result_impl));
if (! impl)
{
OCI_StatementFree(st);
free(wres);
return DB_WRAP_E_ALLOC_ERROR;
}
*impl = ociw_result_impl_empty;
impl->st = st;
wres->impl.data = impl;
#if 1
if (! OCI_ExecuteStmt(st, sql))
{
wres->api->finalize(wres);
/*
i don't quite know why, but fetching the OCI error state after this
returns a 0 error code and empty error string.
*/
return DB_WRAP_E_CHECK_DB_ERROR;
}
#else
if (!
//OCI_ExecuteStmt(st, sql)
OCI_Prepare(st, sql)
)
{
lerr("Preparation of OCI_Statement failed: [%s]\n",OCI_GetSql(st));
wres->api->finalize(wres);
return DB_WRAP_E_CHECK_DB_ERROR;
}
if (! OCI_Execute(st))
{
lerr("Execution of prepared OCI_Statement failed: [%s]\n",OCI_GetSql(st));
wres->api->finalize(wres);
return DB_WRAP_E_CHECK_DB_ERROR;
}
#endif
impl->result = OCI_GetResultset(st)
/* MIGHT be null - the docs are not really clear here what happens on an empty result set. */
;
*tgt = wres;
return 0;
}
boolean OCI_API OCI_QueueStart
(
OCI_Connection *con,
const otext *queue_name,
boolean enqueue,
boolean dequeue
)
{
OCI_Statement *st = NULL;
OCI_LIB_CALL_ENTER(boolean, FALSE)
OCI_CHECK_PTR(OCI_IPC_CONNECTION, con)
OCI_CHECK_PTR(OCI_IPC_STRING, queue_name)
st = OCI_StatementCreate(con);
if (st)
{
call_status = OCI_Prepare
(
st,
OTEXT("DECLARE ")
OTEXT(" v_enqueue BOOLEAN := FALSE; ")
OTEXT(" v_dequeue BOOLEAN := FALSE; ")
OTEXT("BEGIN ")
OTEXT(" IF (:enqueue = 1) then ")
OTEXT(" v_enqueue := TRUE; ")
OTEXT(" END IF; ")
OTEXT(" IF (:dequeue = 1) then ")
OTEXT(" v_dequeue := TRUE; ")
OTEXT(" END IF; ")
OTEXT(" DBMS_AQADM.START_QUEUE ")
OTEXT(" (")
OTEXT(" queue_name => :queue_name, ")
OTEXT(" enqueue => v_enqueue, ")
OTEXT(" dequeue => v_dequeue ")
OTEXT(" ); ")
OTEXT("END; ")
);
call_status = call_status && OCI_BindString(st, OTEXT(":queue_name"), (otext *) queue_name, 0);
call_status = call_status && OCI_BindInt(st, OTEXT(":enqueue"), &enqueue);
call_status = call_status && OCI_BindInt(st, OTEXT(":dequeue"), &dequeue);
call_status = call_status && OCI_Execute(st);
OCI_StatementFree(st);
}
call_retval = call_status;
OCI_LIB_CALL_EXIT()
}
boolean OCI_API OCI_QueueAlter
(
OCI_Connection *con,
const otext *queue_name,
unsigned int max_retries,
unsigned int retry_delay,
unsigned int retention_time,
const otext *comment
)
{
OCI_Statement *st = NULL;
OCI_LIB_CALL_ENTER(boolean, FALSE)
OCI_CHECK_PTR(OCI_IPC_CONNECTION, con)
OCI_CHECK_PTR(OCI_IPC_STRING, queue_name)
st = OCI_StatementCreate(con);
if (st)
{
call_status = OCI_Prepare
(
st,
OTEXT("BEGIN ")
OTEXT(" DBMS_AQADM.ALTER_QUEUE ")
OTEXT(" (")
OTEXT(" queue_name => :queue_name, ")
OTEXT(" max_retries => :max_retries, ")
OTEXT(" retry_delay => :retry_delay, ")
OTEXT(" retention_time => :retention_time, ")
OTEXT(" comment => :comment ")
OTEXT(" ); ")
OTEXT("END; ")
);
call_status = call_status && OCI_BindString(st, OTEXT(":queue_name"), (otext *) queue_name, 0);
call_status = call_status && OCI_BindUnsignedInt(st, OTEXT(":max_retries"), &max_retries);
call_status = call_status && OCI_BindUnsignedInt(st, OTEXT(":retry_delay"), &retry_delay);
call_status = call_status && OCI_BindUnsignedInt(st, OTEXT(":retention_time"), &retention_time);
call_status = call_status && OCI_BindString(st, OTEXT(":comment"), (otext *) (comment ? comment : OCI_STRING_EMPTY), 0);
call_status = call_status && OCI_Execute(st);
OCI_StatementFree(st);
}
call_retval = call_status;
OCI_LIB_CALL_EXIT()
}
boolean OCI_API OCI_QueueTableAlter
(
OCI_Connection *con,
const otext *queue_table,
const otext *comment,
unsigned int primary_instance,
unsigned int secondary_instance
)
{
OCI_Statement *st = NULL;
OCI_LIB_CALL_ENTER(boolean, FALSE)
OCI_CHECK_PTR(OCI_IPC_CONNECTION, con)
OCI_CHECK_PTR(OCI_IPC_STRING, queue_table)
st = OCI_StatementCreate(con);
if (st)
{
call_status = OCI_Prepare
(
st,
OTEXT("BEGIN ")
OTEXT(" DBMS_AQADM.ALTER_QUEUE_TABLE ")
OTEXT(" (")
OTEXT(" queue_table => :queue_table, ")
OTEXT(" comment => :comment, ")
OTEXT(" primary_instance => :primary_instance, ")
OTEXT(" secondary_instance => :secondary_instance ")
OTEXT(" ); ")
OTEXT("END; ")
);
call_status = call_status && OCI_BindString(st, OTEXT(":queue_table"), (otext *) queue_table, 0);
call_status = call_status && OCI_BindString(st, OTEXT(":comment"), (otext *) (comment ? comment: OCI_STRING_EMPTY), 0);
call_status = call_status && OCI_BindUnsignedInt(st, OTEXT(":primary_instance"), &primary_instance);
call_status = call_status && OCI_BindUnsignedInt(st, OTEXT(":secondary_instance"), &secondary_instance);
call_status = call_status && OCI_Execute(st);
OCI_StatementFree(st);
}
call_retval = call_status;
OCI_LIB_CALL_EXIT()
}
boolean OCI_API OCI_QueueTableDrop
(
OCI_Connection *con,
const otext *queue_table,
boolean force
)
{
OCI_Statement *st = NULL;
OCI_LIB_CALL_ENTER(boolean, FALSE)
OCI_CHECK_PTR(OCI_IPC_CONNECTION, con)
OCI_CHECK_PTR(OCI_IPC_STRING, queue_table)
st = OCI_StatementCreate(con);
if (st)
{
call_status = OCI_Prepare
(
st,
OTEXT("DECLARE ")
OTEXT(" v_force BOOLEAN := FALSE; ")
OTEXT("BEGIN ")
OTEXT(" IF (:force = 1) then ")
OTEXT(" v_force := TRUE; ")
OTEXT(" END IF; ")
OTEXT(" DBMS_AQADM.DROP_QUEUE_TABLE ")
OTEXT(" (")
OTEXT(" queue_table => :queue_table, ")
OTEXT(" force => v_force ")
OTEXT(" ); ")
OTEXT("END; ")
);
call_status = call_status && OCI_BindString(st, OTEXT(":queue_table"), (otext *)queue_table, 0);
call_status = call_status && OCI_BindInt(st, OTEXT(":force"), &force);
call_status = call_status && OCI_Execute(st);
OCI_StatementFree(st);
}
call_retval = call_status;
OCI_LIB_CALL_EXIT()
}
void Statement::prepare(const QString &query)
{
#ifdef DEBUG
QUERY_FOR_DBG = query;
#endif
Q_ASSERT(!prepared);
prepared=true;
Q_ASSERT(ociStmt);
Q_ASSERT(ociQuery==0);
ociQuery=toOciString(query);
if(!OCI_Prepare(ociStmt, ociQuery)){
DbUtil::checkForOciError(this);
}
}
int main(void)
{
OCI_Connection *cn;
OCI_Statement *st;
OCI_Object *racing_car;
OCI_TypeInfo *vehicule_type_inf;
OCI_TypeInfo *car_type_inf;
OCI_TypeInfo *racing_car_type_inf;
char buffer[512];
unsigned int size = sizeof(buffer) - 1;
if (!OCI_Initialize(err_handler, NULL, OCI_ENV_DEFAULT))
{
return EXIT_FAILURE;
}
cn = OCI_ConnectionCreate("db", "usr", "pwd", OCI_SESSION_DEFAULT);
st = OCI_StatementCreate(cn);
racing_car = OCI_ObjectCreate(cn, OCI_TypeInfoGet(cn, "racing_car_type", OCI_TIF_TYPE));
OCI_Prepare(st, "begin :obj := racing_car_type(1,'Formula1', 123456789, 300); end;");
OCI_BindObject(st, "obj", racing_car);
OCI_Execute(st);
OCI_ObjectToText(racing_car, &size, buffer);
printf("Object => %s\n", buffer);
racing_car_type_inf = OCI_ObjectGetTypeInfo(racing_car);
car_type_inf = OCI_TypeInfoGetSuperType(racing_car_type_inf);
vehicule_type_inf = OCI_TypeInfoGetSuperType(car_type_inf);
printf("Is type '%s' final => %d\n", OCI_TypeInfoGetName(racing_car_type_inf), OCI_TypeInfoIsFinalType(racing_car_type_inf));
printf("Is type '%s' final => %d\n", OCI_TypeInfoGetName(car_type_inf), OCI_TypeInfoIsFinalType(car_type_inf));
printf("Is type '%s' final => %d\n", OCI_TypeInfoGetName(vehicule_type_inf), OCI_TypeInfoIsFinalType(vehicule_type_inf));
OCI_ObjectFree(racing_car);
OCI_StatementFree(st);
OCI_ConnectionFree(cn);
OCI_Cleanup();
return EXIT_SUCCESS;
}
int main(void)
{
OCI_Connection *cn;
OCI_Statement *st;
OCI_Resultset *rs;
OCI_Ref *ref;
if (!OCI_Initialize(NULL, NULL, OCI_ENV_DEFAULT))
return EXIT_FAILURE;
cn = OCI_ConnectionCreate("db", "usr", "pwd", OCI_SESSION_DEFAULT);
st = OCI_StatementCreate(cn);
OCI_ExecuteStmt(st, "select ref(e) from table_obj e");
rs = OCI_GetResultset(st);
printf("\n\n=> fetch refs from object table\n\n");
while (OCI_FetchNext(rs))
{
dump_ref(OCI_GetRef(rs, 1));
}
printf("\n\n=> bind a local ref object to a PL/SQL statement\n\n");
ref = OCI_RefCreate(cn, OCI_TypeInfoGet(cn, "ARTICLE_T", OCI_TIF_TYPE));
OCI_Prepare(st, "begin "
" select ref(e) into :r from table_obj e where e.id = 1; "
"end; ");
OCI_BindRef(st, ":r", ref);
OCI_Execute(st);
dump_ref(ref);
OCI_RefFree(ref);
OCI_Cleanup();
return EXIT_SUCCESS;
}
boolean OCI_API OCI_QueueDrop
(
OCI_Connection *con,
const otext *queue_name
)
{
OCI_Statement *st = NULL;
OCI_LIB_CALL_ENTER(boolean, FALSE)
OCI_CHECK_PTR(OCI_IPC_CONNECTION, con)
OCI_CHECK_PTR(OCI_IPC_STRING, queue_name)
st = OCI_StatementCreate(con);
if (st)
{
call_status = OCI_Prepare
(
st,
OTEXT("BEGIN ")
OTEXT(" DBMS_AQADM.DROP_QUEUE ")
OTEXT(" (")
OTEXT(" queue_name => :queue_name ")
OTEXT(" ); ")
OTEXT("END; ")
);
call_status = call_status && OCI_BindString(st, OTEXT(":queue_name"), (otext *) queue_name, 0);
call_status = call_status && OCI_Execute(st);
OCI_StatementFree(st);
}
call_retval = call_status;
OCI_LIB_CALL_EXIT()
}
int main(void)
{
OCI_Connection *cn;
OCI_Statement *st;
int i;
if (!OCI_Initialize(NULL, NULL, OCI_ENV_DEFAULT))
return EXIT_FAILURE;
cn = OCI_ConnectionCreate("db", "usr", "pwd", OCI_SESSION_DEFAULT);
st = OCI_StatementCreate(cn);
OCI_SetBindAllocation(st, OCI_BAM_INTERNAL);
OCI_Prepare(st, "insert into test_array values (:tab)");
OCI_BindArraySetSize(st, NB_ELEMS);
OCI_BindArrayOfDates(st, ":tab", NULL, 0);
{
OCI_Date ** tab = (OCI_Date **) OCI_BindGetData(OCI_GetBind(st, 1));
for (i=0; i < NB_ELEMS; i++)
{
OCI_DateSysDate(tab[i]);
}
OCI_Execute(st);
}
OCI_Commit(cn);
OCI_Cleanup();
return EXIT_SUCCESS;
}
int main(int argc, char **argv)
{
OCI_Connection *cn;
OCI_Statement *st;
OCI_Object *obj_sdo;
OCI_Coll *coll_inf, *coll_ord;
OCI_Elem *elem_inf, *elem_ord;
OCI_TypeInfo *tif_inf, *tif_ord, *tif_sdo;
int i;
/* check command line */
if (argc < 3)
{
fprintf(stderr, "Usage: %s user password\n", argv[0]);
return EXIT_FAILURE;
}
/* init OCILIB */
if (OCI_Initialize(error, NULL, OCI_ENV_DEFAULT))
{
/* connect to oracle */
if (cn = OCI_ConnectionCreate(NULL, argv[1], argv[2], OCI_SESSION_DEFAULT))
{
printf ("\nConnected to Oracle.\n");
/* retreive type info */
tif_sdo = OCI_TypeInfoGet(cn, "MDSYS.SDO_GEOMETRY", OCI_TIF_TYPE);
tif_inf = OCI_TypeInfoGet(cn, "MDSYS.SDO_ELEM_INFO_ARRAY", OCI_TIF_TYPE);
tif_ord = OCI_TypeInfoGet(cn, "MDSYS.SDO_ORDINATE_ARRAY", OCI_TIF_TYPE);
/* create sdo object */
obj_sdo = OCI_ObjectCreate(cn, tif_sdo);
/* create sub arrays */
coll_inf = OCI_CollCreate(tif_inf);
coll_ord = OCI_CollCreate(tif_ord);
/* create sub array element accessors */
elem_inf = OCI_ElemCreate(tif_inf);
elem_ord = OCI_ElemCreate(tif_ord);
/* build ordinates collection with test values */
for (i = 0; i < NB_ELEM; i++)
{
OCI_ElemSetDouble(elem_ord, (double) i);
OCI_CollAppend(coll_ord, elem_ord);
OCI_CollAppend(coll_ord, elem_ord);
}
/* setup information collection attribute 'starting_offset' */
OCI_ElemSetUnsignedInt(elem_inf, 1);
OCI_CollAppend(coll_inf, elem_inf);
/* setup information collection attribute 'element_type' */
OCI_ElemSetUnsignedInt(elem_inf, 1);
OCI_CollAppend(coll_inf, elem_inf);
/* setup information collection attribute 'interpretation' */
OCI_ElemSetUnsignedInt(elem_inf, 1);
OCI_CollAppend(coll_inf, elem_inf);
/* set sdo object member attributes */
OCI_ObjectSetInt(obj_sdo, "SDO_GTYPE", 4);
OCI_ObjectSetNull(obj_sdo, "SDO_SRID");
OCI_ObjectSetNull(obj_sdo, "SDO_POINT");
OCI_ObjectSetColl(obj_sdo, "SDO_ELEM_INFO", coll_inf);
OCI_ObjectSetColl(obj_sdo, "SDO_ORDINATES", coll_ord);
/*create statement object */
st = OCI_StatementCreate(cn);
/* prepare, bind and excute statement then commit*/
OCI_Prepare(st, "INSERT INTO test_insert (gid, geometry) VALUES (1, :sdo)");
OCI_BindObject(st, "sdo", obj_sdo);
OCI_Execute(st);
OCI_Commit(cn);
/* free local objects */
OCI_ObjectFree(obj_sdo);
OCI_CollFree(coll_inf);
OCI_CollFree(coll_ord);
OCI_ElemFree(elem_inf);
OCI_ElemFree(elem_ord);
}
}
/* disconnect from oracle and cleanup OCILIB */
OCI_Cleanup();
printf ("\nDisconnected from Oracle.\n");
return EXIT_SUCCESS;
}
boolean OCI_API OCI_QueueCreate
(
OCI_Connection *con,
const otext *queue_name,
const otext *queue_table,
unsigned int queue_type,
unsigned int max_retries,
unsigned int retry_delay,
unsigned int retention_time,
boolean dependency_tracking,
const otext *comment
)
{
OCI_Statement *st = NULL;
OCI_LIB_CALL_ENTER(boolean, FALSE)
OCI_CHECK_PTR(OCI_IPC_CONNECTION, con)
OCI_CHECK_PTR(OCI_IPC_STRING, queue_name)
OCI_CHECK_PTR(OCI_IPC_STRING, queue_table)
OCI_CHECK_ENUM_VALUE(con, NULL, queue_type, QueueTypeValues, OTEXT("Queue type"))
st = OCI_StatementCreate(con);
if (st)
{
call_status = OCI_Prepare
(
st,
OTEXT("DECLARE ")
OTEXT(" v_dependency_tracking BOOLEAN := FALSE; ")
OTEXT("BEGIN ")
OTEXT(" IF (:dependency_tracking = 1) then ")
OTEXT(" v_dependency_tracking := TRUE; ")
OTEXT(" END IF; ")
OTEXT(" DBMS_AQADM.CREATE_QUEUE ")
OTEXT(" (")
OTEXT(" queue_name => :queue_name, ")
OTEXT(" queue_table => :queue_table, ")
OTEXT(" queue_type => :queue_type, ")
OTEXT(" max_retries => :max_retries, ")
OTEXT(" retry_delay => :retry_delay, ")
OTEXT(" retention_time => :retention_time, ")
OTEXT(" dependency_tracking => v_dependency_tracking, ")
OTEXT(" comment => :comment ")
OTEXT(" ); ")
OTEXT("END; ")
);
call_status = call_status && OCI_BindString(st, OTEXT(":queue_name"), (otext *) queue_name, 0);
call_status = call_status && OCI_BindString(st, OTEXT(":queue_table"), (otext *) queue_table, 0);
call_status = call_status && OCI_BindUnsignedInt(st, OTEXT(":queue_type"), &queue_type);
call_status = call_status && OCI_BindUnsignedInt(st, OTEXT(":max_retries"), &max_retries);
call_status = call_status && OCI_BindUnsignedInt(st, OTEXT(":retry_delay"), &retry_delay);
call_status = call_status && OCI_BindUnsignedInt(st, OTEXT(":retention_time"), &retention_time);
call_status = call_status && OCI_BindInt(st, OTEXT(":dependency_tracking"), &dependency_tracking);
call_status = call_status && OCI_BindString(st, OTEXT(":comment"), (otext *) (comment ? comment : OCI_STRING_EMPTY), 0);
call_status = call_status && OCI_Execute(st);
OCI_StatementFree(st);
}
call_retval = call_status;
OCI_LIB_CALL_EXIT()
}
int main(void)
{
OCI_Connection *cn;
OCI_Statement *st;
OCI_Resultset *rs;
OCI_Coll *coll;
OCI_Iter *iter;
OCI_Elem *elem;
OCI_TypeInfo *type;
OCI_Object *obj;
int i, n;
if (!OCI_Initialize(NULL, NULL, OCI_ENV_DEFAULT))
return EXIT_FAILURE;
cn = OCI_ConnectionCreate("db", "usr", "pwd", OCI_SESSION_DEFAULT);
/* Varray binding -------------------------------------------------------- */
st = OCI_StatementCreate(cn);
/* create the collection */
type = OCI_TypeInfoGet(cn, "Varray_type", OCI_TIF_TYPE);
coll = OCI_CollCreate(type);
/* bind the local collection to a PL/SQL procedure */
OCI_Prepare(st, "begin load_array(:array); end;");
OCI_BindColl(st, ":array", coll);
OCI_Execute(st);
/* the procedure has filled the collection and
we can iterate it using an iterator */
iter = OCI_IterCreate(coll);
elem = OCI_IterGetNext(iter);
while (elem != NULL)
{
printf("value %s\n", OCI_ElemGetString(elem));
elem = OCI_IterGetNext(iter);
}
OCI_IterFree(iter);
OCI_CollFree(coll);
/* Varray SQL fetch ------------------------------------------------------- */
/* query on a table with varray column */
OCI_ExecuteStmt(st, "SELECT * from table_article");
rs = OCI_GetResultset(st);
while (OCI_FetchNext(rs))
{
/* iterate the collection using an iterator */
coll = OCI_GetColl(rs, 2);
iter = OCI_IterCreate(coll);
elem = OCI_IterGetNext(iter);
printf("article #%d\n", OCI_GetInt(rs, 1));
while (elem != NULL)
{
obj = OCI_ElemGetObject(elem);
printf(".... code %d, name%s \n", OCI_ObjectGetInt(obj, "ID"),
OCI_ObjectGetString(obj, "NAME"));
elem = OCI_IterGetNext(iter);
}
OCI_IterFree(iter);
}
/* Nested table fetch ------------------------------------------------------- */
/* query on a table with nested table column */
OCI_ExecuteStmt(st, "SELECT * from table_sales");
rs = OCI_GetResultset(st);
while (OCI_FetchNext(rs))
{
coll = OCI_GetColl(rs, 2);
printf("Sale #%d\n", OCI_GetInt(rs, 1));
/* iterate the collection by accessing element by index */
n = OCI_CollGetSize(coll);
for(i = 1; i <= n; i++)
{
elem = OCI_CollGetAt(coll, i);
obj = OCI_ElemGetObject(elem);
printf(".... employee %s, amount %s \n", OCI_ObjectGetString(obj, "EMP"),
OCI_ObjectGetString(obj, "AMOUNT"));
}
}
OCI_Cleanup();
return EXIT_SUCCESS;
}
boolean OCI_API OCI_QueueTableCreate
(
OCI_Connection *con,
const otext *queue_table,
const otext *queue_payload_type,
const otext *storage_clause,
const otext *sort_list,
boolean multiple_consumers,
unsigned int message_grouping,
const otext *comment,
unsigned int primary_instance,
unsigned int secondary_instance,
const otext *compatible
)
{
OCI_Statement *st = NULL;
OCI_LIB_CALL_ENTER(boolean, FALSE)
OCI_CHECK_PTR(OCI_IPC_CONNECTION, con)
OCI_CHECK_PTR(OCI_IPC_STRING, queue_table)
OCI_CHECK_PTR(OCI_IPC_STRING, queue_payload_type)
OCI_CHECK_ENUM_VALUE(con, NULL, message_grouping, GroupingModeValues, OTEXT("Grouping mode"))
st = OCI_StatementCreate(con);
if (st)
{
call_status = OCI_Prepare
(
st,
OTEXT("DECLARE ")
OTEXT(" v_multiple_consumers BOOLEAN := FALSE; ")
OTEXT("BEGIN ")
OTEXT(" IF (:multiple_consumers = 1) then ")
OTEXT(" v_multiple_consumers := TRUE; ")
OTEXT(" END IF; ")
OTEXT(" DBMS_AQADM.CREATE_QUEUE_TABLE ")
OTEXT(" (")
OTEXT(" queue_table => :queue_table, ")
OTEXT(" queue_payload_type => :queue_payload_type, ")
OTEXT(" storage_clause => :storage_clause, ")
OTEXT(" sort_list => :sort_list, ")
OTEXT(" multiple_consumers => v_multiple_consumers, ")
OTEXT(" message_grouping => :message_grouping, ")
OTEXT(" comment => :comment, ")
OTEXT(" primary_instance => :primary_instance, ")
OTEXT(" secondary_instance => :secondary_instance, ")
OTEXT(" compatible => :compatible")
OTEXT(" ); ")
OTEXT("END; ")
);
call_status = call_status && OCI_BindString(st, OTEXT(":queue_table"), (otext *) queue_table, 0);
call_status = call_status && OCI_BindString(st, OTEXT(":queue_payload_type"), (otext *) queue_payload_type, 0);
call_status = call_status && OCI_BindString(st, OTEXT(":storage_clause"), (otext *) (storage_clause ? storage_clause : OCI_STRING_EMPTY), 0);
call_status = call_status && OCI_BindString(st, OTEXT(":sort_list"), (otext *) (sort_list ? sort_list : OCI_STRING_EMPTY), 0);
call_status = call_status && OCI_BindInt(st, OTEXT(":multiple_consumers"), &multiple_consumers);
call_status = call_status && OCI_BindUnsignedInt(st, OTEXT(":message_grouping"), &message_grouping);
call_status = call_status && OCI_BindString(st, OTEXT(":comment"), (otext *)( comment ? comment : OCI_STRING_EMPTY), 0);
call_status = call_status && OCI_BindUnsignedInt(st, OTEXT(":primary_instance"), &primary_instance);
call_status = call_status && OCI_BindUnsignedInt(st, OTEXT(":secondary_instance"), &secondary_instance);
call_status = call_status && OCI_BindString(st, OTEXT(":compatible"), (otext *) (compatible ? compatible : OCI_STRING_EMPTY), 0);
call_status = call_status && OCI_Execute(st);
OCI_StatementFree(st);
}
call_retval = call_status;
OCI_LIB_CALL_EXIT()
}
