cx-oracle-users — Primary discussion list for users (and developers) of cx_Oracle

Re: [cx-oracle-users] Trying to copy the value of a SDO_GEOMETRY column from one table to another

[Jani T. <re...@gm...>]

Hi,

Unfortunately cx_Oracle doesn't support writing user types to database, 
only reading.

There are few options to overcome your problem:

Make output as varchar/clob like wkt. This is relatively slow.

Another option is to use nice approach described here: 
http://www.tolon.co.uk/2012/09/geometry-objects-across-dblink/


On 07.02.2016 16:13, Michael Huber wrote:
> Hi,
>
> I'm querying from a table with a SDO_GEOMETRY column and want to store 
> the content of this column to another table. If I do so, I'm receiving 
> the following exception:
>
> cx_Oracle.NotSupportedError: Variable_TypeByValue(): unhandled data 
> type cx_Oracle.OBJECT
>
> Please find a basic example below:
>
> Python 3.5.1 (default, Jan 20 2016, 15:13:56)
> [GCC 4.8.5 20150623 (Red Hat 4.8.5-4)] on linux
> Type "help", "copyright", "credits" or "license" for more information.
> >>> import cx_Oracle
> >>> con = cx_Oracle.connect('user1/pass1@hostname:1521/TESTDB')
> >>> cur = con.cursor()
> >>>
> >>> cur.execute("SELECT * FROM USER1.SOURCE_TABLE WHERE id = 1")
> <cx_Oracle.Cursor on <cx_Oracle.Connection to user1@hostname:1521/TESTDB>>
> >>> source_data = cur.fetchall()
> >>>
> >>> print(source_data)
> [(1, 'testvalue', <cx_Oracle.OBJECT object at 0x7f39e2561c70>)]
> >>>
> >>> cur.execute("INSERT INTO USER1.TARGET_TABLE VALUES (:1, :2, :3 )", 
> [source_data[0][0], source_data[0][1], source_data[0][2] ])
> Traceback (most recent call last):
>   File "<stdin>", line 1, in <module>
> cx_Oracle.NotSupportedError: Variable_TypeByValue(): unhandled data 
> type cx_Oracle.OBJECT
> >>>
>
> Is it not supported to write the content of a SDO_GEOMETRY column back 
> to an Oracle database? Thanks in advance!
>
> Best,
> Micheal
>
>
> ------------------------------------------------------------------------------
> 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

-- 
Jani Tiainen

[cx-oracle-users] Trying to copy the value of a SDO_GEOMETRY column from one table to another

[Michael H. <mhu...@gm...>]

Hi,

I'm querying from a table with a SDO_GEOMETRY column and want to store the
content of this column to another table. If I do so, I'm receiving the
following exception:

cx_Oracle.NotSupportedError: Variable_TypeByValue(): unhandled data type
cx_Oracle.OBJECT

Please find a basic example below:

Python 3.5.1 (default, Jan 20 2016, 15:13:56)
[GCC 4.8.5 20150623 (Red Hat 4.8.5-4)] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import cx_Oracle
>>> con = cx_Oracle.connect('user1/pass1@hostname:1521/TESTDB')
>>> cur = con.cursor()
>>>
>>> cur.execute("SELECT * FROM USER1.SOURCE_TABLE WHERE id = 1")
<cx_Oracle.Cursor on <cx_Oracle.Connection to user1@hostname:1521/TESTDB>>
>>> source_data = cur.fetchall()
>>>
>>> print(source_data)
[(1, 'testvalue', <cx_Oracle.OBJECT object at 0x7f39e2561c70>)]
>>>
>>> cur.execute("INSERT INTO USER1.TARGET_TABLE VALUES (:1, :2, :3 )",
[source_data[0][0], source_data[0][1], source_data[0][2] ])
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
cx_Oracle.NotSupportedError: Variable_TypeByValue(): unhandled data type
cx_Oracle.OBJECT
>>>

Is it not supported to write the content of a SDO_GEOMETRY column back to
an Oracle database? Thanks in advance!

Best,
Micheal

Re: [cx-oracle-users] CX_ORACLE vs JAVA OCI performance

[Shai B. <sh...@pl...>]

Hi Kurt and Anthony,

When I first saw this, I thought it might be related to an issue I've touched 
on in the past -- the translation to numbers through strings when there is not 
enough info to know in advance that a C integral type would suffice. In 
particular, this fits in with the report of math operations causing slow-downs, 
as Oracle tends not to report limits on precision of calculated columns.

However, the report is still odd -- NUMBER (*,8), NUMBER(*,18) and 
NUMBER(*,38) all have precision 38, they only differ on scale. The report would 
make perfect sense to me if the types were actually NUMBER (8) [fits within 32-
bit int, although cx-Oracle treats it the same as...] NUMBER (18) [fits within 
64-bit int] and NUMBER(38) [requires a Python Long or Decimal].

Shai.

On Saturday 06 February 2016 04:31:38 Anthony Tuininga wrote:
> Hi Kurt,
> 
> That is quite surprising but I'd love to get to the bottom of why that
> behavior is the way it is!  Please do send me the DDL and the Python code
> you used to produce those results. If you have the Java code as well, that
> would be helpful -- so I can replicate the situation on my own machine.
> Thanks!
> 
> Anthony
> 
> On Fri, Feb 5, 2016 at 4:49 PM, kurtosis kurtosis.org
> <kur...@ku...
> 
> > wrote:
> > 
> > Hi Anthony,
> > 
> > First, let me thank you for the effort you have put into cx_Oracle.  It’s
> > a great product and very useful.
> > 
> > I have been doing some performance testing lately and   I’ve noticed some
> > interesting differences in the way cx_Oracle performs versus other
> > platforms (Java,  C#)  and wanted to let you know about them.
> > 
> > First the good news – cx_Oracle is very fast in certain situations, more
> > than twice as fast than Java OCI.  In other situations, it performs much
> > worse.
> > 
> > To do my testing, I created a table of numbers in different
> > configurations.   Each table was 500 columns with 10,000 rows.  I created
> > tables with different types:  NUMBER(*,8), NUMBER(*,18), NUMBER(*,38),
> > and BINARY_DOUBLE.
> > 
> > For NUMBER (*,8), NUMBER(*,18), and NUMBER(*,38) performance is great.  I
> > did 250 select * from the tables, below is the average time in seconds it
> > takes to get results back:
> > 
> > *TABLE*
> > 
> > *PYTHON*
> > 
> > *JAVA*
> > 
> > *tbl_n8*
> > 
> > 1.0513
> > 
> > 2.5052
> > 
> > *tbl_n18*
> > 
> > 1.1196
> > 
> > 2.7348
> > 
> > *tbl_bd*
> > 
> > 0.8423
> > 
> > 1.8668
> > 
> > However when we go to NUMBER(*,38)  performance is much worse:
> > 
> > *TABLE*
> > 
> > *PYTHON*
> > 
> > *JAVA*
> > 
> > *tbl_n38*
> > 
> > 8.5739
> > 
> > 2.8539
> > 
> > I then tested some views in various configurations.  Notably I found that
> > any time one does a math operation in a sql statement cx_Oracle slows
> > down.   Using TRUNC(col, 18) to downcast precision, same result.
> > 
> > *VIEW*
> > 
> > *PYTHON*
> > 
> > *JAVA*
> > 
> > *v_t18*
> > 
> > 9.2087
> > 
> > 3.3632
> > 
> > *v_f18*
> > 
> > 9.8412
> > 
> > 3.3678
> > 
> > I may be doing something wrong, but from my perspective, I’m just using
> > the library in the usual way.   If you are interested, I’m happy to send
> > along the ddl and py code I used to produce these results.
> > 
> > Thanks,
> > 
> > --Kurt
> > 
> > 
> > -------------------------------------------------------------------------
> > ----- 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] CX_ORACLE vs JAVA OCI performance

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

Hi Kurt,

That is quite surprising but I'd love to get to the bottom of why that
behavior is the way it is!  Please do send me the DDL and the Python code
you used to produce those results. If you have the Java code as well, that
would be helpful -- so I can replicate the situation on my own machine.
Thanks!

Anthony

On Fri, Feb 5, 2016 at 4:49 PM, kurtosis kurtosis.org <kur...@ku...
> wrote:

> Hi Anthony,
>
> First, let me thank you for the effort you have put into cx_Oracle.  It’s
> a great product and very useful.
>
> I have been doing some performance testing lately and   I’ve noticed some
> interesting differences in the way cx_Oracle performs versus other
> platforms (Java,  C#)  and wanted to let you know about them.
>
> First the good news – cx_Oracle is very fast in certain situations, more
> than twice as fast than Java OCI.  In other situations, it performs much
> worse.
>
> To do my testing, I created a table of numbers in different
> configurations.   Each table was 500 columns with 10,000 rows.  I created
> tables with different types:  NUMBER(*,8), NUMBER(*,18), NUMBER(*,38), and
> BINARY_DOUBLE.
>
> For NUMBER (*,8), NUMBER(*,18), and NUMBER(*,38) performance is great.  I
> did 250 select * from the tables, below is the average time in seconds it
> takes to get results back:
>
> *TABLE*
>
> *PYTHON*
>
> *JAVA*
>
> *tbl_n8*
>
> 1.0513
>
> 2.5052
>
> *tbl_n18*
>
> 1.1196
>
> 2.7348
>
> *tbl_bd*
>
> 0.8423
>
> 1.8668
>
> However when we go to NUMBER(*,38)  performance is much worse:
>
> *TABLE*
>
> *PYTHON*
>
> *JAVA*
>
> *tbl_n38*
>
> 8.5739
>
> 2.8539
>
> I then tested some views in various configurations.  Notably I found that
> any time one does a math operation in a sql statement cx_Oracle slows
> down.   Using TRUNC(col, 18) to downcast precision, same result.
>
> *VIEW*
>
> *PYTHON*
>
> *JAVA*
>
> *v_t18*
>
> 9.2087
>
> 3.3632
>
> *v_f18*
>
> 9.8412
>
> 3.3678
>
> I may be doing something wrong, but from my perspective, I’m just using
> the library in the usual way.   If you are interested, I’m happy to send
> along the ddl and py code I used to produce these results.
>
> Thanks,
>
> --Kurt
>
>
> ------------------------------------------------------------------------------
> 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
>
>

[cx-oracle-users] CX_ORACLE vs JAVA OCI performance

[kurtosis kurtosis.o. <kur...@ku...>]

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">

<html xmlns="http://www.w3.org/1999/xhtml"><head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"/>
 </head><body><p>Hi Anthony,</p><p>First, let me thank you for the effort you have put into cx_Oracle.&#160; It&#8217;s a great product and very useful.</p><p>I have been doing some performance testing lately and&#160;&#160; I&#8217;ve noticed some interesting differences in the way cx_Oracle performs versus other platforms (Java,&#160; C#) &#160;and wanted to let you know about them.</p><p>First the good news &#8211; cx_Oracle is very fast in certain situations, more than twice as fast than Java OCI.&#160; In other situations, it performs much worse.</p><p>To do my testing, I created a table of numbers in different configurations.&#160;&#160; Each table was 500 columns with 10,000 rows.&#160; I created tables with different types:&#160; NUMBER(*,8), NUMBER(*,18), NUMBER(*,38), and BINARY_DOUBLE.</p><p>For NUMBER (*,8), NUMBER(*,18), and NUMBER(*,38) performance is great.&#160; I did 250 select * from the tables, below is the average time in seconds it takes to get results back:</p><table style="line-height: 1em; margin: 0.5em auto;" border="0" cellspacing="0" cellpadding="0" class="mce-item-table"><tbody><tr><td style="border-bottom-width: 1px; border-bottom-style: solid; border-bottom-color: #aaaaaa; padding: 0.4em 1em; border-top-width: 1px; border-top-style: solid; border-top-color: #555555;" width="56"><p><strong>TABLE</strong></p></td><td style="border-bottom-width: 1px; border-bottom-style: solid; border-bottom-color: #aaaaaa; padding: 0.4em 1em; border-top-width: 1px; border-top-style: solid; border-top-color: #555555;" width="73"><p><strong>PYTHON</strong></p></td><td style="border-bottom-width: 1px; border-bottom-style: solid; border-bottom-color: #aaaaaa; padding: 0.4em 1em; border-top-width: 1px; border-top-style: solid; border-top-color: #555555;" width="97"><p><strong>JAVA</strong></p></td></tr><tr><td style="border-bottom-width: 1px; border-bottom-style: solid; border-bottom-color: #aaaaaa; padding: 0.4em 1em;" width="56"><p><strong>tbl_n8</strong></p></td><td style="border-bottom-width: 1px; border-bottom-style: solid; border-bottom-color: #aaaaaa; padding: 0.4em 1em;" width="73"><p>1.0513</p></td><td style="border-bottom-width: 1px; border-bottom-style: solid; border-bottom-color: #aaaaaa; padding: 0.4em 1em;" width="97"><p>2.5052</p></td></tr><tr><td style="border-bottom-width: 1px; border-bottom-style: solid; border-bottom-color: #aaaaaa; padding: 0.4em 1em;" width="56"><p><strong>tbl_n18</strong></p></td><td style="border-bottom-width: 1px; border-bottom-style: solid; border-bottom-color: #aaaaaa; padding: 0.4em 1em;" width="73"><p>1.1196</p></td><td style="border-bottom-width: 1px; border-bottom-style: solid; border-bottom-color: #aaaaaa; padding: 0.4em 1em;" width="97"><p>2.7348</p></td></tr><tr><td style="border-bottom-width: 1px; border-bottom-style: solid; border-bottom-color: #555555; padding: 0.4em 1em;" width="56"><p><strong>tbl_bd</strong></p></td><td style="border-bottom-width: 1px; border-bottom-style: solid; border-bottom-color: #555555; padding: 0.4em 1em;" width="73"><p>0.8423</p></td><td style="border-bottom-width: 1px; border-bottom-style: solid; border-bottom-color: #555555; padding: 0.4em 1em;" width="97"><p>1.8668</p></td></tr></tbody></table><p>However when we go to NUMBER(*,38)&#160; performance is much worse:</p><table style="line-height: 1em; margin: 0.5em auto;" border="0" cellspacing="0" cellpadding="0" class="mce-item-table"><tbody><tr><td style="border-bottom-width: 1px; border-bottom-style: solid; border-bottom-color: #aaaaaa; padding: 0.4em 1em; border-top-width: 1px; border-top-style: solid; border-top-color: #555555;" width="73"><p><strong>TABLE</strong></p></td><td style="border-bottom-width: 1px; border-bottom-style: solid; border-bottom-color: #aaaaaa; padding: 0.4em 1em; border-top-width: 1px; border-top-style: solid; border-top-color: #555555;" width="62"><p><strong>PYTHON</strong></p></td><td style="border-bottom-width: 1px; border-bottom-style: solid; border-bottom-color: #aaaaaa; padding: 0.4em 1em; border-top-width: 1px; border-top-style: solid; border-top-color: #555555;" width="97"><p><strong>JAVA</strong></p></td></tr><tr><td style="border-bottom-width: 1px; border-bottom-style: solid; border-bottom-color: #555555; padding: 0.4em 1em;" width="73"><p><strong>tbl_n38</strong></p></td><td style="border-bottom-width: 1px; border-bottom-style: solid; border-bottom-color: #555555; padding: 0.4em 1em;" width="62"><p>8.5739</p></td><td style="border-bottom-width: 1px; border-bottom-style: solid; border-bottom-color: #555555; padding: 0.4em 1em;" width="97"><p>2.8539</p></td></tr></tbody></table><p>I then tested some views in various configurations.&#160; Notably I found that any time one does a math operation in a sql statement cx_Oracle slows down.&#160;&#160; Using TRUNC(col, 18) to downcast precision, same result.</p><table style="line-height: 1em; margin: 0.5em auto;" border="0" cellspacing="0" cellpadding="0" class="mce-item-table"><tbody><tr><td style="border-bottom-width: 1px; border-bottom-style: solid; border-bottom-color: #aaaaaa; padding: 0.4em 1em; border-top-width: 1px; border-top-style: solid; border-top-color: #555555;" width="61"><p><strong>VIEW</strong></p></td><td style="border-bottom-width: 1px; border-bottom-style: solid; border-bottom-color: #aaaaaa; padding: 0.4em 1em; border-top-width: 1px; border-top-style: solid; border-top-color: #555555;" width="68"><p><strong>PYTHON</strong></p></td><td style="border-bottom-width: 1px; border-bottom-style: solid; border-bottom-color: #aaaaaa; padding: 0.4em 1em; border-top-width: 1px; border-top-style: solid; border-top-color: #555555;" width="97"><p><strong>JAVA</strong></p></td></tr><tr><td style="border-bottom-width: 1px; border-bottom-style: solid; border-bottom-color: #aaaaaa; padding: 0.4em 1em;" width="61"><p><strong>v_t18</strong></p></td><td style="border-bottom-width: 1px; border-bottom-style: solid; border-bottom-color: #aaaaaa; padding: 0.4em 1em;" width="68"><p>9.2087</p></td><td style="border-bottom-width: 1px; border-bottom-style: solid; border-bottom-color: #aaaaaa; padding: 0.4em 1em;" width="97"><p>3.3632</p></td></tr><tr><td style="border-bottom-width: 1px; border-bottom-style: solid; border-bottom-color: #555555; padding: 0.4em 1em;" width="61"><p><strong>v_f18</strong></p></td><td style="border-bottom-width: 1px; border-bottom-style: solid; border-bottom-color: #555555; padding: 0.4em 1em;" width="68"><p>9.8412</p></td><td style="border-bottom-width: 1px; border-bottom-style: solid; border-bottom-color: #555555; padding: 0.4em 1em;" width="97"><p>3.3678</p></td></tr></tbody></table><p>I may be&#160;doing something wrong, but from my perspective, I&#8217;m just using the library in the usual way.&#160;&#160; If you are interested, I&#8217;m happy to send along the ddl and py code I used to produce these results.&#160;&#160;</p><p>Thanks,</p><p>--Kurt</p></body></html>

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?

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

Thanks Jochen. Transaction Guard support is already implemented (but not
yet released). App continuity is not available through the OCI currently so
it will have to wait until that is in place, first, before cx_Oracle can
consider it.

On Thu, Jan 28, 2016 at 5:07 AM, Jochen Halwachs <jo...@ha...>
wrote:

> Thanks
>
> Transaction Guard/AC.
>
> ng, Jochen
>
> Von meinem Samsung Gerät gesendet.
>
>
> -------- Ursprüngliche Nachricht --------
> Von: Walter Dörwald <wa...@li...>
> Datum: 28.01.2016 10:52 (GMT+01:00)
> An: cx-...@li...
> Betreff: Re: [cx-oracle-users] New features?
>
> 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
>
>
> ------------------------------------------------------------------------------
> 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...>]

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

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?

[Jochen H. <jo...@ha...>]

    
Thanks
Transaction Guard/AC.
ng, Jochen
Von meinem Samsung Gerät gesendet.

-------- Ursprüngliche Nachricht --------
Von: Walter Dörwald <wa...@li...> 
Datum: 28.01.2016  10:52  (GMT+01:00) 
An: cx-...@li... 
Betreff: Re: [cx-oracle-users] New features? 

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?

[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] ExportObjects: is "Unable to locate LOB segment” fatal?

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

Hi Mark,

If you use the -t option you will get traceback that should help you figure
things out. It is trying to read from either dba_segments or user_segments
(depending on if you used the --use-dba-views option) and is unable to
locate the row. Considering it is referencing TLC.DEFINITION and you are
connecting as FOO I'd guess you want to use --use-dba-views.

And yes, that error is indeed fatal. :-(

Anthony

On Wed, Jan 27, 2016 at 4:04 PM, Mark Harrison <mha...@le...>
wrote:

> I’m getting this error on one of our dev databases.
>
> 1.  What is the cause of this?  How can I find the problem table
> definition?
>
> 2. Is this fatal to ExportObjects, I.e. will processing continue?
>
> $ ExportObjects --default-tablespace --default-storage —schema=foo/bar
> Describing synonyms...
> Describing sequences...
> Describing tables...
> 14:48:19.170 Unable to locate LOB segment TLC.DEFINITION
>
>
> ------------------------------
> DISCLAIMER: The information transmitted is intended only for the person or
> entity to which it is addressed and may contain confidential and/or
> privileged material. Any review, re-transmission, dissemination or other
> use of, or taking of any action in reliance upon this information by
> persons or entities other than the intended recipient is prohibited. If you
> received this in error, please contact the sender and destroy any copies of
> this document and any attachments.
>
>
> ------------------------------------------------------------------------------
> 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
>
>

[cx-oracle-users] ExportObjects: is "Unable to locate LOB segment” fatal?

[Mark H. <mha...@le...>]

I’m getting this error on one of our dev databases.

1.  What is the cause of this?  How can I find the problem table definition?

2. Is this fatal to ExportObjects, I.e. will processing continue?

$ ExportObjects --default-tablespace --default-storage —schema=foo/bar
Describing synonyms...
Describing sequences...
Describing tables...
14:48:19.170 Unable to locate LOB segment TLC.DEFINITION


________________________________
DISCLAIMER: The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, re-transmission, dissemination or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and destroy any copies of this document and any attachments.

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?

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

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

[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] Error while trying to import cx_Oracle

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

Hi Saurabh,

One possibility is that your Oracle client doesn't match the installation
of cx_Oracle that you selected. You need to make sure that you are using
the 64-bit Oracle 11.2 instant client or equivalent Oracle Database
installation.

Are you able to use SQL*Plus to connect to the database?

Anthony

On Tue, Jan 19, 2016 at 12:09 PM, Saurabh Kumar <sa...@le...>
wrote:

> This is the error message :
>
>
>
> ---------------------------------------------------------------------------
>
> ImportError                               Traceback (most recent call last)
>
> <ipython-input-3-5ff1bac0ae2a> in <module>()
>
> ----> 1 import cx_Oracle;
>
>
>
> ImportError: DLL load failed: The specified module could not be found.
>
>
>
> I am trying to import cx_Oracle into my Python 2.7 environment from within
> Anaconda’s iPython Notebook. I am on a Windows 7 machine. The import keeps
> failing despite installing the msi cx_Oracle-5.1.2-11g.win-amd64-py2.7 . Do
> you know what is the possible fix to this problem ?
>
>
>
> All I want to do is be able to connect to an Oracle database and be able
> to execute SQL script via Python. I am attempting to automate a SQL code
> block run that could help me generate csv files.
>
>
>
>
>
> ------------------------------
> DISCLAIMER: The information transmitted is intended only for the person or
> entity to which it is addressed and may contain confidential and/or
> privileged material. Any review, re-transmission, dissemination or other
> use of, or taking of any action in reliance upon this information by
> persons or entities other than the intended recipient is prohibited. If you
> received this in error, please contact the sender and destroy any copies of
> this document and any attachments.
>
>
> ------------------------------------------------------------------------------
> 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
>
>

[cx-oracle-users] Error while trying to import cx_Oracle

[Saurabh K. <sa...@le...>]

This is the error message :

---------------------------------------------------------------------------
ImportError                               Traceback (most recent call last)
<ipython-input-3-5ff1bac0ae2a> in <module>()
----> 1 import cx_Oracle;

ImportError: DLL load failed: The specified module could not be found.

I am trying to import cx_Oracle into my Python 2.7 environment from within Anaconda's iPython Notebook. I am on a Windows 7 machine. The import keeps failing despite installing the msi cx_Oracle-5.1.2-11g.win-amd64-py2.7 . Do you know what is the possible fix to this problem ?

All I want to do is be able to connect to an Oracle database and be able to execute SQL script via Python. I am attempting to automate a SQL code block run that could help me generate csv files.



________________________________
DISCLAIMER: The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, re-transmission, dissemination or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and destroy any copies of this document and any attachments.

Re: [cx-oracle-users] cx_Oracle 5.2.1

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

On 19 Jan 2016, at 15:03, Anthony Tuininga wrote:

> Hi Walter,
>
> Sorry about that! I forgot to upload the source. That has now been
> corrected. :-)

Now "pip install --upgrade cx_Oracle" worked.

Thanks for the quick fix (and thanks for cx_Oracle :-)).

> Anthony
>
> [...]

Servus,
   Walter
 

Re: [cx-oracle-users] cx_Oracle 5.2.1

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

Hi Walter,

Sorry about that! I forgot to upload the source. That has now been
corrected. :-)

Anthony

On Tue, Jan 19, 2016 at 3:54 AM, Walter Dörwald <wa...@li...>
wrote:

> On 19 Jan 2016, at 6:24, Anthony Tuininga wrote:
>
> > What is cx_Oracle?
> >
> > cx_Oracle is a Python extension module that enables access to Oracle
> > for
> > Python 2.x and 3.x and conforms to the Python database API 2.0
> > specifications
> > with a number of enhancements.
> >
> > Where do I get it?
> > http://cx-oracle.sourceforge.net
> >
> > What's new?
> >
> > http://cx-oracle.readthedocs.org/en/latest/releasenotes.html
>
> I can't update to 5.2.1:
>
>     $ pip list | grep cx
>     cx-Oracle (5.2)
>     $ pip install --upgrade cx_Oracle
>     Requirement already up-to-date: cx-Oracle in
> /Users/walter/pyvenvs/default/lib/python3.5/site-packages
>     $ pip list | grep cx
>     cx-Oracle (5.2)
>
> It seems that pip doesn't recognise the new version, because there are
> only Windows installers in the cheeseshop.
>
> Can we get the source distribution please?
>
> 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] cx_Oracle 5.2.1

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

On 19 Jan 2016, at 6:24, Anthony Tuininga wrote:

> What is cx_Oracle?
>
> cx_Oracle is a Python extension module that enables access to Oracle 
> for
> Python 2.x and 3.x and conforms to the Python database API 2.0 
> specifications
> with a number of enhancements.
>
> Where do I get it?
> http://cx-oracle.sourceforge.net
>
> What's new?
>
> http://cx-oracle.readthedocs.org/en/latest/releasenotes.html

I can't update to 5.2.1:

    $ pip list | grep cx
    cx-Oracle (5.2)
    $ pip install --upgrade cx_Oracle
    Requirement already up-to-date: cx-Oracle in 
/Users/walter/pyvenvs/default/lib/python3.5/site-packages
    $ pip list | grep cx
    cx-Oracle (5.2)

It seems that pip doesn't recognise the new version, because there are 
only Windows installers in the cheeseshop.

Can we get the source distribution please?

Servus,
    Walter

[cx-oracle-users] cx_Oracle 5.2.1

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

What is cx_Oracle?

cx_Oracle is a Python extension module that enables access to Oracle for
Python 2.x and 3.x and conforms to the Python database API 2.0 specifications
with a number of enhancements.


Where do I get it?
http://cx-oracle.sourceforge.net



What's new?

http://cx-oracle.readthedocs.org/en/latest/releasenotes.html

Re: [cx-oracle-users] Problem reading CLOB data from query result

[Andrew W. <and...@fa...>]

Shai,

Thank you for the suggestion.  While I couldn't figure out what you were referring to on the LOB page of the documentation for cx_Oracle, "for result in cursor:" seems more 'pythonic' to me and also simpler so I've updated my code as you suggested.

I also poked at cursor.arraysize.  Setting arraysize to 1 allows me to get the data from the query without error.  I tried other values but hit the error at different record counts as follows:

cursor.arraysize	Max records read without error
-------------------		--------------------------------------
500			200
50			200
10			390
5			385
2			508

The only consistent pattern I'm seeing is that something is wrong in the way the cursor handles LOB data handles if arraysize is greater than 1.

With cursor.arraysize set to 1, I'm no longer blocked by this bug.  However, if there is additional debugging I can do to help resolve the underlying issue, please let me know so that I can provide additional data.

Thanks to everyone for the help and suggestions,

Andrew

-----Original Message-----
From: Shai Berger [mailto:sh...@pl...] 
Sent: Tuesday, December 15, 2015 18:01
To: cx-...@li...
Subject: Re: [cx-oracle-users] Problem reading CLOB data from query result

On Tuesday 15 December 2015 23:04:51 Andrew Wheelwright wrote:
> 
> I have no idea what is magical about 200 in my environment.  I'm 
> guessing that I'm exhausting some resource related to our Oracle 
> server but I lack the experience to know what that might be or how to find that out.
> 

Actually, 200 sounds more like a client-side limit. The first suspect is cursor.arraysize, which is 50 by default but many set it differently. It is only supposed to affect performance, but you may have hit some corner that makes it affect more.

Also, I would replace:

	result = cursor.fetchone()
	while result is not None:
	   ...
	   result = cursor.fetchone() # <---- Invalid handle error thrown here

with 

	for result in cursor:
	   ...

just seems nicer, and fits better with
https://urldefense.proofpoint.com/v2/url?u=http-3A__cx-2Doracle.readthedocs.org_en_latest_lob.html&d=CwICAg&c=z0adcvxXWKG6LAMN6dVEqQ&r=r8n22QhFUAPJDR_VlIDWddq2o_hv2gm-i73xXR1k6jfSqv6lYXybbcyOCn6zKs-3&m=Y9Xz6SNdZPhFSPmEoxu-5YvaNMqw2XMguxLVQRFmXXw&s=FKGtfsuGd3UCHgy1GirhSS1S8fwU-HnfnJh04TYuQuI&e= 

HTH,
	Shai.

------------------------------------------------------------------------------
_______________________________________________
cx-oracle-users mailing list
cx-...@li...
https://urldefense.proofpoint.com/v2/url?u=https-3A__lists.sourceforge.net_lists_listinfo_cx-2Doracle-2Dusers&d=CwICAg&c=z0adcvxXWKG6LAMN6dVEqQ&r=r8n22QhFUAPJDR_VlIDWddq2o_hv2gm-i73xXR1k6jfSqv6lYXybbcyOCn6zKs-3&m=Y9Xz6SNdZPhFSPmEoxu-5YvaNMqw2XMguxLVQRFmXXw&s=yjEiirBXcXcRCGW9pVvJqOMEHIdHVmg-hhprAz9wQQM&e= 

Re: [cx-oracle-users] Problem reading CLOB data from query result

[Shai B. <sh...@pl...>]

On Tuesday 15 December 2015 23:04:51 Andrew Wheelwright wrote:
> 
> I have no idea what is magical about 200 in my environment.  I'm guessing
> that I'm exhausting some resource related to our Oracle server but I lack
> the experience to know what that might be or how to find that out.
> 

Actually, 200 sounds more like a client-side limit. The first suspect is 
cursor.arraysize, which is 50 by default but many set it differently. It is 
only supposed to affect performance, but you may have hit some corner that 
makes it affect more.

Also, I would replace:

	result = cursor.fetchone()
	while result is not None:
	   ...
	   result = cursor.fetchone() # <---- Invalid handle error thrown here

with 

	for result in cursor:
	   ...

just seems nicer, and fits better with 
http://cx-oracle.readthedocs.org/en/latest/lob.html

HTH,
	Shai.