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

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

On 7 Mar 2016, at 16:03, Anthony Tuininga wrote:

> Hi Walter,
>
> Yes, that was indeed an issue. Apparently data with a type of
> OCI_TYPECODE_INTEGER is actually stored as OCINumber, not as a native
> integer. So as long as you remain in Python all is well. But as soon 
> as you
> try to use it within PL/SQL you get strange results as you noted 
> above.
> This has been corrected. Thanks!

I tried it again with the new version, and now it works. Thanks!

And it works in views too:

create or replace view foo as
   select
     cast(multiset(select 1 from dual union all
                   select 2 from dual union all
                   select 3 from dual union all
                   select 4 from dual
     ) as integers) as ids
   from
	dual;

  >> from ll import orasql
  >> db = orasql.connect('user/pwd@db')
  >> c = db.cursor()
  >> c.execute("select * from foo")
  >> <ll.orasql.Cursor statement='select * from foo' at 0x11084a048>
  >> r = c.fetchone()
  >> r
  >> <ll.orasql.Record ids=<cx_Oracle.Object WALTER.INTEGERS> at 
0x1149d8240>
  >> r.ids
  >> <cx_Oracle.Object WALTER.INTEGERS>
  >> r.ids.aslist()
  >> [1, 2, 3, 4]

> I'll look into adding the custom repr as well. I agree that would be
> useful. :-)

<cx_Oracle.Object WALTER.INTEGERS> definitely is an improvement. However 
the object address should remain part of the object repr so that 
different objects are distinguishable, i.e. the repr should probably be:

    <cx_Oracle.Object WALTER.INTEGERS at 0x12345678>

> Anthony

Servus,
    Walter

> On Mon, Mar 7, 2016 at 3:06 AM, Walter Dörwald 
> <wa...@li...>
> wrote:
>
>> 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
>>
>>
>> ------------------------------------------------------------------------------
>> Transform Data into Opportunity.
>> Accelerate data analysis in your applications with
>> Intel Data Analytics Acceleration Library.
>> Click to learn more.
>> http://makebettercode.com/inteldaal-eval
>> _______________________________________________
>> cx-oracle-users mailing list
>> cx-...@li...
>> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>>
> ------------------------------------------------------------------------------
> Transform Data into Opportunity.
> Accelerate data analysis in your applications with
> Intel Data Analytics Acceleration Library.
> Click to learn more.
> http://makebettercode.com/inteldaal-eval_______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users