empty collection from an OUT parameter

[Nobody/Anonymous]

Hi,
I'm trying to execute a procedure which takes an OUT nested table:

    OCI_Coll* coll, *res;
    coll = CollOfIntArray(l, n, type); // returns a collection filled from an int array
    res = OCI_CollCreate(type);
    OCI_Prepare(st, MT("begin test.some_proc(:l, :res); end;"));
    OCI_BindColl(st, MT(":l"), coll); //IN
    OCI_BindColl(st, MT(":res"), res); // OUT
    OCI_Execute(st);
    int size = OCI_CollGetSize(res); // this yields zero

but I always get a zero-length result, though the collection passed as IN argument is seen correctly in PL/SQL code.
Am I doing something wrong?

Thanks.

[Oleg Finkelshteyn]

Sorry, I should have been logged in in the first place.

Stupid me, I should have tried some other way of checking if I'm getting something before complaining.

My SF.net email should be good to go.

[Vincent Rogier]

I'll check this issue and try to reproduce it by tomorrow

what version of ocilib are you using ?

Vincent

[Nobody/Anonymous]

ocilib 3.3.0 compiled against instantclient-basic 11.1.0.7.0 on linux/i686

[Nobody/Anonymous]

being an absolute dbms beginner, I tried the same code in TOra, and it seems to work fine.

[Vincent Rogier]

collections must be initialized to be used in pl/sql...

if you manipulate/assign values to the out param (and if this collection is not initialized with values as it is in your code) directly from within the procedure, it will raise an error even in SQL*PLUS.

If you create a local collection, manipulate it and then assign it to tue out collection, it will be ok.

It's not a OCILIB issue but an PL/SQL misuse...

[Nobody/Anonymous]

Well, I knew the problem was on my end. Though it no more has anything to do with OCILIB itself, I'm still having problems.
Do I understand you correctly that I need to do

declare coll mytype; begin test.some_proc(:l, :i, coll); :res := coll; end

with the same bindings?
mytype is "table of number". OCI reference mentions something about the need for a second BindObject() in the case of collections, but doesn't provide any specific information.

Any pointers would be much appreciated.
Thanks.

[Vincent Rogier]

hi,

I investigated this issue and found out that for an out collection, the size property was not updated afeter the execute call :(

I've fixed it. Your problem might be solved or not (if it's about using an uninitialized collection).

Can you send me a email that i could send you the patch ?

thanks.

vincent