Problem with nested varray in collection

[Victor Mischenko]

Hi Vincent,

I have a problem with nested varray in named data type.
When I try to insert collection of objects which have one of field of varray type, I always get varray empty. Also I noticed strange behavior of collection after call of OCI_ObjectSetColl, it truncated to 1 after next OCI_CollAppend calling. See my example code below.

I use oracle client version 9.2.0.7.0 (windows) and server version 10.* (linux)

Could you help me in this problem?

Thanks.

/*
PL/SQL code

create or replace type rec_trakts_cics_assign
as object
(
cs_route number,
cic number,
ci number
)
/

create or replace type TabTraktsCicsAssign as varray(32) of rec_trakts_cics_assign
/

create or replace type rec_test
as object
(
id number,
cics_assign TabTraktsCicsAssign
)
/

create or replace type TabTest is table of rec_test
/

create table trakt_debug (trakt number, cs_route number, cic number, ci number);

create or replace procedure ProcTest(i_val TabTest)
as
begin
for i in 1..i_val.count
loop
for c in 1..i_val(i).cics_assign.count
loop
insert into trakt_debug (trakt, cs_route, cic, ci)
values (i_val(i).id, i_val(i).cics_assign(c).cs_route, i_val(i).cics_assign(c).cic, i_val(i).cics_assign(c).ci);
end loop;
dbms_output.put_line(i_val(i).cics_assign.count);
end loop;
end;
/

*/

BOOL TestNestedCollection()
{
OCI_Initialize(err_handler, NULL, OCI_ENV_THREADED | OCI_ENV_CONTEXT);

OCI_Connection *cn = OCI_ConnectionCreate("db", "user", "pass", OCI_SESSION_DEFAULT);
OCI_Statement *st = OCI_StatementCreate(cn);

OCI_ServerEnableOutput(cn, 10000, 100, 1024);

// get type informations

OCI_TypeInfo *pTifRec = OCI_TypeInfoGet(cn, "rec_test", OCI_TIF_TYPE);
OCI_TypeInfo *pTifTable = OCI_TypeInfoGet(cn, "TabTest", OCI_TIF_TYPE);

OCI_TypeInfo *pTifRecNested = OCI_TypeInfoGet(cn, "rec_trakts_cics_assign", OCI_TIF_TYPE);
OCI_TypeInfo *pTifTableNested = OCI_TypeInfoGet(cn, "TabTraktsCicsAssign", OCI_TIF_TYPE);

// create local objects

OCI_Object *pObject = OCI_ObjectCreate(cn, pTifRec);
OCI_Elem *pElement = OCI_ElemCreate(pTifTable);
OCI_Coll *pCollection = OCI_CollCreate(pTifTable);

OCI_Object *pObjectNested = OCI_ObjectCreate(cn, pTifRecNested);
OCI_Elem *pElementNested = OCI_ElemCreate(pTifTableNested);
OCI_Coll *pCollectionNested = OCI_CollCreate(pTifTableNested);

// test insert a collection into a nested table column through a PL/SQL stored proc

int iRowCount = 10;
int iMaxSize = OCI_CollGetMax(pCollectionNested);
int iSize = 0;

for (int i = 0; i < iRowCount; i++) 
{
    OCI_ObjectSetInt(pObject, "ID", i + 1);

    for (int j = 0; j < iMaxSize; j++)
    {
        OCI_ObjectSetInt(pObjectNested, "CS_ROUTE", j + 1);
        OCI_ObjectSetInt(pObjectNested, "CIC", j + 2);
        OCI_ObjectSetInt(pObjectNested, "CI", j + 3);

        OCI_ElemSetObject(pElementNested, pObjectNested);
        OCI_CollAppend(pCollectionNested, pElementNested);          
    }

    iSize = OCI_CollGetSize(pCollectionNested);

    OCI_ObjectSetColl(pObject, "CICS_ASSIGN", pCollectionNested);

    OCI_ElemSetObject(pElement, pObject);
    OCI_CollAppend(pCollection, pElement);
}

iSize = OCI_CollGetSize(pCollectionNested);
iSize = OCI_CollGetSize(pCollection);

OCI_Prepare(st, "begin ProcTest(:coll); end;");
OCI_BindColl(st, ":coll", pCollection);
OCI_Execute(st);

while (const dtext *str = OCI_ServerGetOutput(cn))
{
    printf("%s\n", str);
}

// commit modifications

OCI_Commit(cn);

// free allocated objects

OCI_ObjectFree(pObjectNested);
OCI_ElemFree(pElementNested);
OCI_CollFree(pCollectionNested);

OCI_ObjectFree(pObject);
OCI_ElemFree(pElement);
OCI_CollFree(pCollection);

OCI_TypeInfoFree(pTifRec);
OCI_TypeInfoFree(pTifTable);
OCI_TypeInfoFree(pTifRecNested);
OCI_TypeInfoFree(pTifTableNested);

OCI_ConnectionFree(cn);

OCI_Cleanup();

return TRUE;

}

[Vincent Rogier]

Hi vicotr,

I'll have a look this evening :)

[Vincent Rogier]

Hi Victor,

Godd news :)

I found the problem... There is a bug in OCI_CollAssign() that is also internally called by OCI_ObjectSetColl()..

In collection.c, line 198, in the call to OCICollAssign() the 2 OCI collection handles (src and dst) must be swapped...oopps !

should be :

OCICollAssign(OCILib.env, coll->con->err, coll_src->handle, coll->handle)

instead of :

OCICollAssign(OCILib.env, coll->con->err, coll->handle, coll_src->handle)

But there is a bug as well in your code... You"re appending elements to the collection pCollectionNested at every outer loop.. So the first iteration appends 32 elements, the second outer iteration tries to append 32 more to the 32 existing and this generates an error because it has a max size of 32 elements !

So, you need to reset the collection before the inner loop (using 'j' counter) with the following :

OCI_CollTrim(pCollectionNested, iMaxSize);

Btw, I've just noticed that that was no public function to clear (or reset) a collection (because OCI does not provide such a function).. That's stupid. I added in v3.3.0 the following call :

OCI_EXPORT boolean OCI_API OCI_CollClear( OCI_Coll *coll);

With the OCILIB fix and the user application fix, your sample code works like a charm on my config...

Vincent

[Victor Mischenko]

Hi Vincent,

Great thanks.

I know that I need to clean the collection, but while the bug is present, I get an Oracle error after first loop iteration when trying to clean collection.
Yesterday I found another bug and few issues.
First about bug. See my code example:

/
create or replace type tab_of_number is table of number
/
create or replace procedure test_proc(i_val tab_of_number)
as
begin
for i in 1..i_val.count
loop
dbms_output.put_line(to_char(i_val(i)));
end loop;
end;
/
/
BOOL TestNumberCollection()
{
OCI_Initialize(err_handler, NULL, OCI_ENV_THREADED | OCI_ENV_CONTEXT);

OCI_Connection *cn = OCI_ConnectionCreate("db", "user", "pwd", OCI_SESSION_DEFAULT);
OCI_Statement *st = OCI_StatementCreate(cn);

OCI_ServerEnableOutput(cn, 10000, 100, 1024);

// get type information

OCI_TypeInfo *pTifTable = OCI_TypeInfoGet(cn, "tab_of_number", OCI_TIF_TYPE);

// create local objects

OCI_Elem *pElement = OCI_ElemCreate(pTifTable);
OCI_Coll *pCollection = OCI_CollCreate(pTifTable);

const int iSize = 10;

for (int i = 0; i < iSize; i++)
{
    OCI_ElemSetInt(pElement, i + 1);
    OCI_CollAppend(pCollection, pElement);
}

OCI_Prepare(st, "begin  test_proc(:coll); end;");
OCI_BindColl(st, ":coll", pCollection);
OCI_Execute(st);

while (const dtext *str = OCI_ServerGetOutput(cn))
{
    afxDump << str << "\n";
    printf("%s\n", str);
}

// commit modifications

OCI_Commit(cn);

// free allocated objects

OCI_ElemFree(pElement);
OCI_CollFree(pCollection);
OCI_TypeInfoFree(pTifTable);

OCI_ConnectionFree(cn);

OCI_Cleanup();

return TRUE;

}

OCI_ElemSetInt(pElement, i + 1) always return false.
I watch to call stack and found in OCI_NumberSet (...) (number.c line 92)
OCI_CHECK(data == NULL, FALSE) check fails because elem->handle is null in OCI_ElemSetNumber(...) caller function.

And about issues:

1. In OCI_CollSetAt you don't check bounds and indexing starts with 0 because you call OCICollAssignElem with "index" argument.
2. In OCI_CollGetAt you check bounds, but call OCICollGetElem with "index-1" argument, so indexing starts with 1.
3. When I call OCI_CollTrim(pCollection, OCI_CollGetSize(pCollection)) and collection contains zero elements I got an error, because you do this check OCI_CHECK_BOUND(coll->con, nb_elem, 1, coll->size, FALSE) I think in this case must be no errors.

I fix this issues, in my local copy of sources.
I added bounds check in OCI_CollSetAt, changed index handling to starts with 0 everywhere, changed check in OCI_CollTrim (simply changed OCI_CHECK_BOUND to return true) to allow call OCI_CollTrim on empty collections.
It will be nice if you make the same changes and update them in SVN.

Thanks.
Victor

[Vincent Rogier]

Hi victor,

about the issues, i'll fix it today but all indexes in ocilib start by 1. So i'll fix OCI_CollSetAt() to follow this rule

About the bug, i'll correct it as well today.

I'll update the svn with current v3.3.0 this evening (between 22:00 pm and 23:00 pm, paris time)

btw, i added you in the ocilib credits thanks file

Vincent

[Victor Mischenko]

Well, I will keep in mind that the indexing starts with 1.

Great tanks.

[Vincent Rogier]

Hello,

The current development version v3.3.0 is now available on the OCILIB SVN repository :)

This version include the fixes you need.

Vincent

[Victor Mischenko]

Hi Vincent,

Thanks a lot

[Victor Mischenko]

Hi Vincent,

The latest version of the library has compiling errors in new macros.

Here is correct ones :)

#define OCI_SetNullAtPos(stmt, index, position) \
OCI_BindSetNullAtPos(OCI_GetBind(stmt, index), position)

#define OCI_SetNullAtPos2(stmt, name, position) \
OCI_BindSetNullAtPos(OCI_GetBind2(stmt, name), position)

Victor

[Vincent Rogier]

Hi Victor,

Thanks a lot !

I 've justed updated the svn :)

Vincent

[Victor Mischenko]

Hi,

You didn't fix typo :)

postion instead position

#define OCI_SetNullAtPos(stmt, index, position) \
OCI_BindSetNullAtPos(OCI_GetBind(stmt, index), postion)

Victor

[Vincent Rogier]

Fixed.

vincent