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

[Florian G. <spi...@gm...>]

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
>>
>>