Re: [Orclib-users] Question about calling procedures in packages with custom types

[vincent r. <vin...@gm...>]

Hi,

I've realized that for nested table, OCILIB does not provide support for
the DELETE method (that delete an item at a given index).
I've just added (OCI_CollDeleteAt() ) it and it will be part of 4.0.
release.
Will be committed into SVN within few days.


On Wed, Nov 13, 2013 at 9:48 PM, vincent rogier <vin...@gm...>wrote:

> Hi,
>
> Here is an example on how creating a collection, filling it with entries,
> iterating over entries and cleaning the collection :
>
> int main (int argc, char **argv)
>> {
>>     OCI_Connection *cn;
>>     OCI_Statement *st;
>>     OCI_Resultset *rs;
>>     OCI_TypeInfo *col_type;
>>     OCI_TypeInfo *obj_type;
>>     OCI_Coll * coll;
>>     OCI_Object *obj;
>>     OCI_Elem * elem;
>>     OCI_Iter *iter;
>>     int i;
>>
>>     OCI_Initialize(err_handler, NULL, OCI_ENV_DEFAULT );
>>
>>     cn = OCI_ConnectionCreate("db12c", "usr", "pwd", OCI_SESSION_DEFAULT);
>>     col_type = OCI_TypeInfoGet ( cn, "test_obj_coll", OCI_TIF_TYPE ) ;
>>     obj_type = OCI_TypeInfoGet ( cn, "test_obj", OCI_TIF_TYPE ) ;
>>
>>     coll = OCI_CollCreate( col_type ) ;
>>     obj  = OCI_ObjectCreate( cn, obj_type ) ;
>>     elem = OCI_ElemCreate ( col_type ) ;
>>
>>     for (i = 1; i <= 5000; i++)
>>     {
>>         dtext buf[50];
>>
>>         mtsprintf(buf, 50, "val1 - %04d", i);
>>         OCI_ObjectSetString ( obj, "val1", buf ) ;
>>
>>         mtsprintf(buf, 50, "val1 - %04d", i);
>>         OCI_ObjectSetString ( obj, "val2", buf ) ;
>>
>>
>>         OCI_ElemSetObject ( elem, obj ) ;
>>         OCI_CollAppend ( coll, elem ) ;
>>     }
>>
>>     OCI_ElemFree( elem );
>>     OCI_ObjectFree (obj ) ;
>>
>>
>>     iter = OCI_IterCreate ( coll ) ;
>>     elem = OCI_IterGetNext ( iter ) ;
>>
>>     while (elem)
>>     {
>>         obj = OCI_ElemGetObject( elem ) ;
>>
>>         printf("val1 = %s, val2 = %s\n", OCI_ObjectGetString(obj,
>> "val1"), OCI_ObjectGetString(obj, "val1"));
>>
>>
>>         elem = OCI_IterGetNext ( iter ) ;
>>     }
>>
>>     OCI_CollClear(coll);
>>
>>
>>     OCI_IterFree ( iter ) ;
>>     OCI_CollFree ( coll ) ;
>>     OCI_ConnectionFree ( cn ) ;
>>
>>     OCI_Cleanup () ;
>> }
>>
>
>
>
> On Tue, Nov 12, 2013 at 10:54 PM, vincent rogier <vin...@gm...>wrote:
>
>> I'll have a look tomorrow and let you know.
>> Le 12 nov. 2013 18:49, "Florian Grißmer" <spi...@gm...> a écrit :
>>
>> Hi Vincent
>>>
>>> Using this destroy-loop doesn't make any diffence
>>>
>>>   While ( elem != Null ) {
>>>
>>>     OCI_ObjectFree ( OCI_ElemGetObject( elem ) ) ;
>>>
>>>     elem = OCI_IterGetNext ( iter ) ;
>>>   }
>>>
>>> I still get the same error
>>>
>>>
>>> Only call OCI_xxxFree() çon object returned by OCI_xxxCreate()
>>>>
>>>
>>> Yes Thats why I thought i coul call the Function OCI_ElemFree on my
>>> elem, which I created with OCI_ElemCreate. Is it impossible to destroy
>>> the objects using only the collection-Object?
>>>
>>> Thx for your help
>>>
>>>
>>>
>>>>
>>>> Regards,
>>>>
>>>> Vincent
>>>>
>>>>
>>>> On Tue, Nov 12, 2013 at 4:12 PM, Florian Grißmer <spi...@gm...>wrote:
>>>>
>>>>> Hi Vincent
>>>>>
>>>>> Thx for you answer, I was afraid of that, I liked the package-scheme,
>>>>> It's a pity I can't use it this way.
>>>>>
>>>>> I have another question. I created and destroyed a list (collection of
>>>>> an object-type with two string-fields) with 5000 Elements as Objects and it
>>>>> still gives me this error at the end: *OCI Error 0, Found 5000
>>>>> unfreed OCI Object handles *after the OCI_Cleanup call. What am I
>>>>> doing wrong, I create and destroy all Elements
>>>>>
>>>>> I call OCI_ElemFree ( elem ), but because of this check:
>>>>>
>>>>>     if ((obj)->hstate == OCI_OBJECT_FETCHED_CLEAN)
>>>>>         return (ret);
>>>>>
>>>>> This Elements object ist not destroyed. Is this an effect of my test,
>>>>> that Elements in Lists, if they are not used in a request can't be
>>>>> destroyed and in a productive environment, where I would always call a
>>>>> PL/SQL procedure with this list, this would never happen? Or sth different?
>>>>>
>>>>> Thx for your help
>>>>>
>>>>>
>>>>>   OCI_Initialize ( err_handler, Null, OCI_ENV_DEFAULT) ) ;
>>>>>
>>>>>   con = OCI_ConnectionCreate ( db, user, pwd ) ;
>>>>>
>>>>>   list_typinf = OCI_TypeInfoGet ( con, name, OCI_TIF_TYPE ) ;
>>>>>
>>>>>   column = OCI_TypeInfoGetColumn ( list_typinf, 1 ) ;
>>>>>
>>>>>   col_typinf = OCI_ColumnGetTypeInfo ( column ) ;
>>>>>
>>>>>   coll = OCI_CollCreate ( list_typinf ) ;
>>>>>
>>>>>   obj = OCI_ObjectCreate ( con, col_typinf ) ;
>>>>>
>>>>>   OCI_ObjectSetString ( obj, field_1, field_1_value ) ;
>>>>>
>>>>>   OCI_ObjectSetString ( obj, field_2, field_2_value ) ;
>>>>>
>>>>>   For (int i = 1; i <= 5000; i++) {
>>>>>
>>>>>     elem = OCI_ElemCreate ( list_typinf ) ;
>>>>>
>>>>>     OCI_ElemSetObject ( elem, obj ) ;
>>>>>
>>>>>     OCI_CollAppend ( coll, elem ) ;
>>>>>   }
>>>>>
>>>>>   OCI_ObjectFree ( obj ) ;
>>>>>
>>>>>   iter = OCI_IterCreate ( coll ) ;
>>>>>
>>>>>   elem = OCI_IterGetNext ( iter ) ;
>>>>>
>>>>>   While ( elem != Null ) {
>>>>>
>>>>>     OCI_ElemFree ( elem ) ;
>>>>>
>>>>>     elem = OCI_IterGetNext ( iter ) ;
>>>>>   }
>>>>>
>>>>>   OCI_IterFree ( iter ) ;
>>>>>
>>>>>   OCI_CollFree ( coll ) ;
>>>>>
>>>>>   OCI_ConnectionFree ( con ) ;
>>>>>
>>>>>   OCI_Cleanup () ;
>>>>>
>>>>>
>>>>>
>>>>> On Mon, Nov 11, 2013 at 3:53 PM, vincent rogier <
>>>>> vin...@gm...> wrote:
>>>>>
>>>>>> Hello,
>>>>>>
>>>>>> PL/SQL records and tables of records cannot be used outside PL/SQL.
>>>>>> In order to use collections in PL/SQL,SQL and host programs, you must use
>>>>>> SQL collections (varrays or nested tables).
>>>>>> Thus you need to create an object type and a nested table of objects.
>>>>>> Change your record definition to an object type definition.
>>>>>> You can have a look at the ocilib demo source files for definition
>>>>>> and usage :)
>>>>>>
>>>>>> Regards,
>>>>>>
>>>>>> Vincent
>>>>>> Le 11 nov. 2013 14:13, "Florian Grißmer" <spi...@gm...> a
>>>>>> écrit :
>>>>>>
>>>>>>>  Hi
>>>>>>>
>>>>>>> I have a question about an OCI_Collection Argument of a procedure.
>>>>>>> Suppose I have a procedure defined in a package like this:
>>>>>>>
>>>>>>> CREATE OR REPLACE
>>>>>>> PACKAGE TEST_PACK AS
>>>>>>>
>>>>>>>   TYPE test_rcd IS RECORD ( testnum Number(4,0)
>>>>>>>                           , test_varchar Varchar(14) );
>>>>>>>
>>>>>>>   TYPE list_of_rcds IS TABLE OF test_rcd;
>>>>>>>
>>>>>>>   Procedure test_prc1 ( rcd IN Out list_of_rcds ) ;
>>>>>>>
>>>>>>>
>>>>>>> END TEST_PACK;
>>>>>>>
>>>>>>> Ho do I call this procedure with a binded argument? To build the
>>>>>>> "rcd"-OCI_Collection Variable i have to have an OCI_TypeInf, which I cant
>>>>>>> get, because the type is inside a package. OCI_TypeInfoGet() uses
>>>>>>> "Describe" and this can't access types inside packages.
>>>>>>>
>>>>>>> Thx in advance.
>>>>>>>
>>>>>>>
>>>>>>> ------------------------------------------------------------------------------
>>>>>>> November Webinars for C, C++, Fortran Developers
>>>>>>> Accelerate application performance with scalable programming models.
>>>>>>> Explore
>>>>>>> techniques for threading, error checking, porting, and tuning. Get
>>>>>>> the most
>>>>>>> from the latest Intel processors and coprocessors. See abstracts and
>>>>>>> register
>>>>>>>
>>>>>>> http://pubads.g.doubleclick.net/gampad/clk?id=60136231&iu=/4140/ostg.clktrk
>>>>>>> _______________________________________________
>>>>>>> Orclib-users mailing list
>>>>>>> Orc...@li...
>>>>>>> https://lists.sourceforge.net/lists/listinfo/orclib-users
>>>>>>>
>>>>>>>
>>>>>
>>>>
>>>>
>>>> --
>>>> Vincent Rogier
>>>>
>>>
>>>
>
>
> --
> Vincent Rogier
>



-- 
Vincent Rogier