Re: [cx-oracle-users] New features?

[Walter D. <wa...@li...>]

On 6 Mar 2016, at 3:23, Anthony Tuininga wrote:

> Hi Walter,
>
> Apologies for the lengthy delay in responding to you!
>
> The reason for the failure was that integer was not supported (it is 
> now,
> though). If you had used number or varchar2 instead all would have 
> worked
> as expected!
>
> I also added support for the following, as suggested (thanks for the
> suggestion!)
>
> typeObj = connection.gettype("INTEGERS")
> obj = typeObj.newobject([1, 2, 3, 4])
> obj2 = typeObj([1, 2, 3, 4])
>
> Let me know if you have any other difficulties or suggestions! Thanks.

Now I can create INTEGERS objects. However there still seems to be a 
problem. I tried the following:

   import cx_Oracle

   db = cx_Oracle.connect("user/pwd@db")
   c = db.cursor()

   c.execute("create type integers as table of integer")

   c.execute("create table debug (dbg_text varchar2(4000), dbg_time 
timestamp)")

   c.execute("""
     create or replace procedure debug_insert(p_dbg_text varchar2)
     as
     pragma autonomous_transaction;
     begin
       insert into debug (dbg_text, dbg_time) values (p_dbg_text, 
systimestamp);
       commit;
     end;
   """)

   c.execute("""
     create or replace function SEARCH
     (
       p_ids in integers,
       p_searchvalue in integer
     )
     return integer
     as
     begin
       debug_insert('searching for ' || p_searchvalue);
       for i in 1..p_ids.count loop
         debug_insert('testing ' || p_ids(i));
         if p_ids(i) = p_searchvalue then
           debug_insert('found');
           return 1;
         end if;
       end loop;
       debug_insert('not found');
       return 0;
     end;
   """)

   integers = db.gettype("INTEGERS")

   print(c.callfunc("SEARCH", int, [integers([1,2,3,4]), 2]))

The output of this script is 0. However I would have expected it to be 
1.

Looking into the DEBUG table gives me the following:

    searching for 2

    testing -~

    testing

    testing 
-0.00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000E+125

    testing 
-0.00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000E+125

    not found

So either I'm doing something stupid, or passing an INTEGERS object 
still has problems.

BTW, it would be great if custom objects had a repr that at least 
displayed the underlying Oracle type, not just:

    <cx_Oracle.Object object at 0x108b8f450>

i.e. something like

    <cx_Oracle.Object object type='INTEGERS' at 0x108b8f450>

> Anthony
> [...]

Servus,
    Walter