cx-oracle-users

[cx-oracle-users] New features?

[Anthony T. <ant...@gm...>]

All,

I have recently been able to spend a bit more time on cx_Oracle and would
like your feedback on what features would be of the greatest benefit to
you. Besides the ones that are in 5.2.1, the following features have been
committed:

Added support for pickling/unpickling error objects
Added support for getting implicit results (Oracle Database 12.1)
Added support for Transaction Guard (Oracle Database 12.1)
Added support for setting max lifetime session of pool (Oracle Database
12.1)

Any and all feedback appreciated.

Anthony

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

[Chris G. <chr...@to...>]

Hi Anthony -
I'm not an expert Python programmer, but I do find Python and the cx_Oracle
driver extremely useful in my job as a PL/SQL programmer.

One thing which would be extremely useful is if the columns returned by a
query could be referenced by their column names rather than their offset
number.  Is that something that could be done easily?

I also use PHP a bit and I know there it's possible to choose either to
reference the returned columns by name or offset, and it makes the code
much easier to follow when you're using column names.

Thanks
Chris.


On 27 January 2016 at 16:21, Anthony Tuininga <ant...@gm...>
wrote:

> All,
>
> I have recently been able to spend a bit more time on cx_Oracle and would
> like your feedback on what features would be of the greatest benefit to
> you. Besides the ones that are in 5.2.1, the following features have been
> committed:
>
> Added support for pickling/unpickling error objects
> Added support for getting implicit results (Oracle Database 12.1)
> Added support for Transaction Guard (Oracle Database 12.1)
> Added support for setting max lifetime session of pool (Oracle Database
> 12.1)
>
> Any and all feedback appreciated.
>
> Anthony
>
>
> ------------------------------------------------------------------------------
> Site24x7 APM Insight: Get Deep Visibility into Application Performance
> APM + Mobile APM + RUM: Monitor 3 App instances at just $35/Month
> Monitor end-to-end web transactions and take corrective actions now
> Troubleshoot faster and improve end-user experience. Signup Now!
> http://pubads.g.doubleclick.net/gampad/clk?id=267308311&iu=/4140
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>

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

[Amaury F. d'A. <ama...@gm...>]

Hi Chris,

2016-01-27 18:02 GMT+01:00 Chris Gould <chr...@to...>:

> Hi Anthony -
> I'm not an expert Python programmer, but I do find Python and the
> cx_Oracle driver extremely useful in my job as a PL/SQL programmer.
>
> One thing which would be extremely useful is if the columns returned by a
> query could be referenced by their column names rather than their offset
> number.  Is that something that could be done easily?
>
> I also use PHP a bit and I know there it's possible to choose either to
> reference the returned columns by name or offset, and it makes the code
> much easier to follow when you're using column names.
>

Someone asked this several years ago already, here is a solution with a
custom cursor.rowfactory:
http://sourceforge.net/p/cx-oracle/mailman/message/27145597/


-- 
Amaury Forgeot d'Arc

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

[Mark H. <ma...@gm...>]

On 1/27/16 9:21 AM, Amaury Forgeot d'Arc wrote:
> Hi Chris,
>
> 2016-01-27 18:02 GMT+01:00 Chris Gould <chr...@to...
> <mailto:chr...@to...>>:
>
>     Hi Anthony -
>     I'm not an expert Python programmer, but I do find Python and the
>     cx_Oracle driver extremely useful in my job as a PL/SQL programmer.
>
>     One thing which would be extremely useful is if the columns returned
>     by a query could be referenced by their column names rather than
>     their offset number.  Is that something that could be done easily?
>
>     I also use PHP a bit and I know there it's possible to choose either
>     to reference the returned columns by name or offset, and it makes
>     the code much easier to follow when you're using column names.
>
>
> Someone asked this several years ago already, here is a solution with a
> custom cursor.rowfactory:
> http://sourceforge.net/p/cx-oracle/mailman/message/27145597/

I added this to stackoverflow to hopefully make it a bit more searchable...

http://stackoverflow.com/questions/35045879/cx-oracle-how-can-i-receive-each-row-as-a-dictionary


>
>
> --
> Amaury Forgeot d'Arc
>
>
> ------------------------------------------------------------------------------
> Site24x7 APM Insight: Get Deep Visibility into Application Performance
> APM + Mobile APM + RUM: Monitor 3 App instances at just $35/Month
> Monitor end-to-end web transactions and take corrective actions now
> Troubleshoot faster and improve end-user experience. Signup Now!
> http://pubads.g.doubleclick.net/gampad/clk?id=267308311&iu=/4140
>
>
>
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

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

[Chris G. <chr...@to...>]

Thanks, yes I think I saw that before. But it'd still be nicer if it were
built in to the driver (as it is in PHP)


On 27 January 2016 at 17:21, Amaury Forgeot d'Arc <ama...@gm...>
wrote:

> Hi Chris,
>
> 2016-01-27 18:02 GMT+01:00 Chris Gould <chr...@to...>:
>
>> Hi Anthony -
>> I'm not an expert Python programmer, but I do find Python and the
>> cx_Oracle driver extremely useful in my job as a PL/SQL programmer.
>>
>> One thing which would be extremely useful is if the columns returned by a
>> query could be referenced by their column names rather than their offset
>> number.  Is that something that could be done easily?
>>
>> I also use PHP a bit and I know there it's possible to choose either to
>> reference the returned columns by name or offset, and it makes the code
>> much easier to follow when you're using column names.
>>
>
> Someone asked this several years ago already, here is a solution with a
> custom cursor.rowfactory:
> http://sourceforge.net/p/cx-oracle/mailman/message/27145597/
>
>
> --
> Amaury Forgeot d'Arc
>

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

[Anthony T. <ant...@gm...>]

Thanks, Chris. I'll look into that.

On Wed, Jan 27, 2016 at 10:35 AM, Chris Gould <chr...@to...>
wrote:

> Thanks, yes I think I saw that before. But it'd still be nicer if it were
> built in to the driver (as it is in PHP)
>
>
> On 27 January 2016 at 17:21, Amaury Forgeot d'Arc <ama...@gm...>
> wrote:
>
>> Hi Chris,
>>
>> 2016-01-27 18:02 GMT+01:00 Chris Gould <chr...@to...>:
>>
>>> Hi Anthony -
>>> I'm not an expert Python programmer, but I do find Python and the
>>> cx_Oracle driver extremely useful in my job as a PL/SQL programmer.
>>>
>>> One thing which would be extremely useful is if the columns returned by
>>> a query could be referenced by their column names rather than their offset
>>> number.  Is that something that could be done easily?
>>>
>>> I also use PHP a bit and I know there it's possible to choose either to
>>> reference the returned columns by name or offset, and it makes the code
>>> much easier to follow when you're using column names.
>>>
>>
>> Someone asked this several years ago already, here is a solution with a
>> custom cursor.rowfactory:
>> http://sourceforge.net/p/cx-oracle/mailman/message/27145597/
>>
>>
>> --
>> Amaury Forgeot d'Arc
>>
>
>
>
> ------------------------------------------------------------------------------
> Site24x7 APM Insight: Get Deep Visibility into Application Performance
> APM + Mobile APM + RUM: Monitor 3 App instances at just $35/Month
> Monitor end-to-end web transactions and take corrective actions now
> Troubleshoot faster and improve end-user experience. Signup Now!
> http://pubads.g.doubleclick.net/gampad/clk?id=267308311&iu=/4140
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>

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

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

On 27 Jan 2016, at 17:21, Anthony Tuininga wrote:

> All,
>
> I have recently been able to spend a bit more time on cx_Oracle and 
> would
> like your feedback on what features would be of the greatest benefit 
> to
> you. Besides the ones that are in 5.2.1, the following features have 
> been
> committed:
>
> Added support for pickling/unpickling error objects
> Added support for getting implicit results (Oracle Database 12.1)
> Added support for Transaction Guard (Oracle Database 12.1)
> Added support for setting max lifetime session of pool (Oracle 
> Database
> 12.1)
>
> Any and all feedback appreciated.
>
> Anthony

We have many procedures which have a parameter with type INTEGERS, which 
is defined as:

    create or replace type integers as table of integer;

I'd like to by able to such a procedure through cx_Oracle.

Servus,
    Walter

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

[Anthony T. <ant...@gm...>]

Thanks, Walter. This one has been requested many times over the years so I
strongly suspect this one will "make the cut" so to speak. :-)

On Thu, Jan 28, 2016 at 2:52 AM, Walter Dörwald <wa...@li...>
wrote:

> On 27 Jan 2016, at 17:21, Anthony Tuininga wrote:
>
> > All,
> >
> > I have recently been able to spend a bit more time on cx_Oracle and
> > would
> > like your feedback on what features would be of the greatest benefit
> > to
> > you. Besides the ones that are in 5.2.1, the following features have
> > been
> > committed:
> >
> > Added support for pickling/unpickling error objects
> > Added support for getting implicit results (Oracle Database 12.1)
> > Added support for Transaction Guard (Oracle Database 12.1)
> > Added support for setting max lifetime session of pool (Oracle
> > Database
> > 12.1)
> >
> > Any and all feedback appreciated.
> >
> > Anthony
>
> We have many procedures which have a parameter with type INTEGERS, which
> is defined as:
>
>     create or replace type integers as table of integer;
>
> I'd like to by able to such a procedure through cx_Oracle.
>
> Servus,
>     Walter
>
>
> ------------------------------------------------------------------------------
> Site24x7 APM Insight: Get Deep Visibility into Application Performance
> APM + Mobile APM + RUM: Monitor 3 App instances at just $35/Month
> Monitor end-to-end web transactions and take corrective actions now
> Troubleshoot faster and improve end-user experience. Signup Now!
> http://pubads.g.doubleclick.net/gampad/clk?id=267308311&iu=/4140
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

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

[Anthony T. <ant...@gm...>]

On Thu, Jan 28, 2016 at 2:52 AM, Walter Dörwald <wa...@li...>
wrote:

> On 27 Jan 2016, at 17:21, Anthony Tuininga wrote:
>
> > All,
> >
> > I have recently been able to spend a bit more time on cx_Oracle and
> > would
> > like your feedback on what features would be of the greatest benefit
> > to
> > you. Besides the ones that are in 5.2.1, the following features have
> > been
> > committed:
> >
> > Added support for pickling/unpickling error objects
> > Added support for getting implicit results (Oracle Database 12.1)
> > Added support for Transaction Guard (Oracle Database 12.1)
> > Added support for setting max lifetime session of pool (Oracle
> > Database
> > 12.1)
> >
> > Any and all feedback appreciated.
> >
> > Anthony
>
> We have many procedures which have a parameter with type INTEGERS, which
> is defined as:
>
>     create or replace type integers as table of integer;
>
> I'd like to by able to such a procedure through cx_Oracle.
>

This can now be done with the code in the source repository today.

typeObj = connection.gettype("integers")
obj = typeObj.newobject()
obj.extend([1, 2, 3, 4])
cursor = connection.cursor()
cursor.callproc("someprocedure", (obj,))

Anthony

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

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

On 24 Feb 2016, at 0:30, Anthony Tuininga wrote:

> On Thu, Jan 28, 2016 at 2:52 AM, Walter Dörwald 
> <wa...@li...>
> wrote:
>
>> On 27 Jan 2016, at 17:21, Anthony Tuininga wrote:
>>
>>> All,
>>>
>>> I have recently been able to spend a bit more time on cx_Oracle and
>>> would
>>> like your feedback on what features would be of the greatest benefit
>>> to
>>> you. Besides the ones that are in 5.2.1, the following features have
>>> been
>>> committed:
>>>
>>> Added support for pickling/unpickling error objects
>>> Added support for getting implicit results (Oracle Database 12.1)
>>> Added support for Transaction Guard (Oracle Database 12.1)
>>> Added support for setting max lifetime session of pool (Oracle
>>> Database
>>> 12.1)
>>>
>>> Any and all feedback appreciated.
>>>
>>> Anthony
>>
>> We have many procedures which have a parameter with type INTEGERS, 
>> which
>> is defined as:
>>
>>  create or replace type integers as table of integer;
>>
>> I'd like to by able to such a procedure through cx_Oracle.
>>
>
> This can now be done with the code in the source repository today.
>
> typeObj = connection.gettype("integers")
> obj = typeObj.newobject()
> obj.extend([1, 2, 3, 4])
> cursor = connection.cursor()
> cursor.callproc("someprocedure", (obj,))

It doesn't seem to work for me. I get the following:

$ python
Python 3.5.1 (default, Jan 22 2016, 11:57:23)
[GCC 4.2.1 Compatible Apple LLVM 7.0.0 (clang-700.1.76)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>> import cx_Oracle
>> db = cx_Oracle.connect("user/pwd@db")
>> integers = db.gettype("INTEGERS")
>> i = integers.newobject()
>> i.extend([1,2,3,4])
Traceback (most recent call last):
   File "<stdin>", line 1, in <module>
cx_Oracle.NotSupportedError: Object_ConvertFromPython(): unhandled data 
type 3
>> ^D

integers is defined as:

    create or replace type INTEGERS as table of integer;

I installed cx_Oracle via

$ hg clone https://bitbucket.org/anthony_tuininga/cx_oracle
$ cd cx_oracle
$ pip install .

BTW, it would be great if the type object could be called (just like a 
Python class) to create an instance, i.e. use:

	i = integers([1, 2, 3, 4])

instead of

     i = integers.newobject()
     i.extend([1,2,3,4])

Servus,
    Walter

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

[Anthony T. <ant...@gm...>]

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.

Anthony

On Wed, Feb 24, 2016 at 7:07 AM, Walter Dörwald <wa...@li...>
wrote:

> On 24 Feb 2016, at 0:30, Anthony Tuininga wrote:
>
> > On Thu, Jan 28, 2016 at 2:52 AM, Walter Dörwald
> > <wa...@li...>
> > wrote:
> >
> >> On 27 Jan 2016, at 17:21, Anthony Tuininga wrote:
> >>
> >>> All,
> >>>
> >>> I have recently been able to spend a bit more time on cx_Oracle and
> >>> would
> >>> like your feedback on what features would be of the greatest benefit
> >>> to
> >>> you. Besides the ones that are in 5.2.1, the following features have
> >>> been
> >>> committed:
> >>>
> >>> Added support for pickling/unpickling error objects
> >>> Added support for getting implicit results (Oracle Database 12.1)
> >>> Added support for Transaction Guard (Oracle Database 12.1)
> >>> Added support for setting max lifetime session of pool (Oracle
> >>> Database
> >>> 12.1)
> >>>
> >>> Any and all feedback appreciated.
> >>>
> >>> Anthony
> >>
> >> We have many procedures which have a parameter with type INTEGERS,
> >> which
> >> is defined as:
> >>
> >>  create or replace type integers as table of integer;
> >>
> >> I'd like to by able to such a procedure through cx_Oracle.
> >>
> >
> > This can now be done with the code in the source repository today.
> >
> > typeObj = connection.gettype("integers")
> > obj = typeObj.newobject()
> > obj.extend([1, 2, 3, 4])
> > cursor = connection.cursor()
> > cursor.callproc("someprocedure", (obj,))
>
> It doesn't seem to work for me. I get the following:
>
> $ python
> Python 3.5.1 (default, Jan 22 2016, 11:57:23)
> [GCC 4.2.1 Compatible Apple LLVM 7.0.0 (clang-700.1.76)] on darwin
> Type "help", "copyright", "credits" or "license" for more information.
> >> import cx_Oracle
> >> db = cx_Oracle.connect("user/pwd@db")
> >> integers = db.gettype("INTEGERS")
> >> i = integers.newobject()
> >> i.extend([1,2,3,4])
> Traceback (most recent call last):
>    File "<stdin>", line 1, in <module>
> cx_Oracle.NotSupportedError: Object_ConvertFromPython(): unhandled data
> type 3
> >> ^D
>
> integers is defined as:
>
>     create or replace type INTEGERS as table of integer;
>
> I installed cx_Oracle via
>
> $ hg clone https://bitbucket.org/anthony_tuininga/cx_oracle
> $ cd cx_oracle
> $ pip install .
>
> BTW, it would be great if the type object could be called (just like a
> Python class) to create an instance, i.e. use:
>
>         i = integers([1, 2, 3, 4])
>
> instead of
>
>      i = integers.newobject()
>      i.extend([1,2,3,4])
>
> Servus,
>     Walter
>
>
> ------------------------------------------------------------------------------
> Site24x7 APM Insight: Get Deep Visibility into Application Performance
> APM + Mobile APM + RUM: Monitor 3 App instances at just $35/Month
> Monitor end-to-end web transactions and take corrective actions now
> Troubleshoot faster and improve end-user experience. Signup Now!
> http://pubads.g.doubleclick.net/gampad/clk?id=272487151&iu=/4140
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

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

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

[Anthony T. <ant...@gm...>]

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'll look into adding the custom repr as well. I agree that would be
useful. :-)

Anthony

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
>

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

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

[Anthony T. <ant...@gm...>]

On Mon, Mar 7, 2016 at 8:30 AM, Walter Dörwald <wa...@li...>
wrote:

> On 7 Mar 2016, at 16:03, Anthony Tuininga wrote:
> >> 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>
>

I can buy that. :-) I have made that change.

Anthony