OCI_Iter * OCI_API OCI_IterCreate
(
OCI_Coll *coll
)
{
OCI_Iter *iter = NULL;
OCI_CALL_ENTER(OCI_Iter*, iter);
OCI_CALL_CHECK_PTR(OCI_IPC_COLLECTION, coll)
OCI_CALL_CONTEXT_SET_FROM_CONN(coll->con)
/* allocate iterator structure */
OCI_ALLOCATE_DATA(OCI_IPC_ITERATOR, iter, 1)
if (OCI_STATUS)
{
iter->coll = coll;
iter->eoc = FALSE;
iter->boc = TRUE;
iter->dirty = TRUE;
/* create iterator */
OCI_EXEC(OCIIterCreate(iter->coll->con->env, iter->coll->con->err, coll->handle, &iter->handle))
/* create data element */
if (OCI_STATUS)
{
iter->elem = OCI_ElemInit(coll->con, iter->elem, NULL, (OCIInd *)NULL, coll->typinf);
OCI_STATUS = (NULL != iter->elem);
}
}
/* check for success */
if (OCI_STATUS)
{
OCI_RETVAL = iter;
}
else if (iter)
{
OCI_IterFree(iter);
}
OCI_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;
}
