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() }