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

Re: [cx-oracle-users] on stackoverflow? help re-open this question...

[Mark H. <mh...@pi...>]

On 1/30/13 5:20 PM, Aurélien Vallée wrote:
> I don't understand my question "How does the internetz works" was closed too. Please help me reopen it!
>
> More seriously, I do agree this clearly is not a question, seems more like you

hmm, are you feeling bad this morning, or are you just having poor reading
comprehension?  Put this on your Amazon wish list and I'll buy it for you!

     http://www.amazon.com/Cure-Constipation-Now-Doctors-Therapy/dp/0425227553

 > just are too lazy to type in "cx_Oracle tutorial" in google.

You realize that I'm one of the top google hits for this query, right?

     http://markharrison.net/cx-oracle-demos/

 > Why would anyone waste time answering this, except to get some free reputation points in a forum/mmorpg such as stackoverflow.

I do it out of gratitude to Anthony for doing such a great job on
cx_Oracle, and trying to help other people who might be interested
in using it.

You should do the same!

Cheers,
Mark

Re: [cx-oracle-users] Error Expecting numeric Data

[Ivanelson N. <iva...@gm...>]

Solved! The problem was in the type numeric fields. In numeric with
'data_scale', I converted to decimal and without 'scale', I converted to
integer. I also did the same in numeric fields null.

https://github.com/ivanelson/zim2ora/blob/master/zim2ora_bulk.py

[]s

Ivan Nunes
[image: Twitter] <http://ivanelson>
 Get a signature like this.
<http://r1.wisestamp.com/r/landing?promo=18&dest=http%3A%2F%2Fwww.wisestamp.com%2Femail-install%3Futm_source%3Dextension%26utm_medium%3Demail%26utm_campaign%3Dpromo_18>
Click
here.<http://r1.wisestamp.com/r/landing?promo=18&dest=http%3A%2F%2Fwww.wisestamp.com%2Femail-install%3Futm_source%3Dextension%26utm_medium%3Demail%26utm_campaign%3Dpromo_18>



2013/1/26 Anthony Tuininga <ant...@gm...>

> executemany() expects all of the data to be in the same format since it is
> sent to the database in one package.
> execute() is being nice to you and simply taking the effort to setup new
> buffers when the data changes -- so it is slower but behaves as you expect.
>
> Find the data type change and your executemany() will function as expected.
>
> Anthony
>
> On Sat, Jan 26, 2013 at 8:04 PM, Ivanelson Nunes <iva...@gm...
> > wrote:
>
>> One. Test: I sent the entire file of 30 to 30 lines and raised no
>> exception.
>> 2nd. Test: I changed the method "executetomany" by "execute" and recorded
>> all 2000 lines without error.
>>
>> ?!
>>
>> 2013/1/26 Anthony Tuininga <ant...@gm...>
>>
>>> On Sat, Jan 26, 2013 at 7:37 PM, Ivanelson Nunes <
>>> iva...@gm...> wrote:
>>>
>>>> Some type of limitation or restriction security. For when I insert only
>>>> 30 lines generates no exception.
>>>>
>>>
>>> No. This a programming error. You are passing *both* strings *and*
>>> numbers at the same time. Oracle insists that it be one type or the other!
>>> The first 30 lines are all of the same type, it would seem. The error
>>> occurs sometime later. One suggestion: print the types of the tuples you
>>> are inserting -- that should help you figure things out.
>>>
>>>
>>>>
>>>> My code is here:
>>>> https://github.com/ivanelson/zim2ora/blob/master/zim2ora_bulk.py
>>>>
>>>> I also found these comments on this issue:
>>>> https://github.com/dmwm/WMCore/issues/4297#issuecomment-10706585
>>>>
>>>>
>>>> Any idea?
>>>>
>>>>
>>>> []s
>>>> 2013/1/26 Roger Downing <rog...@gm...>
>>>>
>>>>> Hi,
>>>>> It would be helpful if you were to give more context to your code.
>>>>> What is 'L' for example?
>>>>>
>>>>> cursor.executemany() expects as its second argument a sequence. The
>>>>> elements of the sequence should either be sequences or parameter mappings
>>>>> as described in the documentation.
>>>>>
>>>>> The error you are getting could mean that you are trying to insert
>>>>> non-numeric data into a column which has been specified as numeric.
>>>>>
>>>>> Cheers,
>>>>>
>>>>> Roger
>>>>>
>>>>>
>>>>> On 25 January 2013 18:19, Ivanelson Nunes <iva...@gm...>wrote:
>>>>>
>>>>>> What kind of error is this?
>>>>>>
>>>>>> cursor.executemany (sql_insert, L)
>>>>>>> TypeError: expecting numeric data
>>>>>>
>>>>>>
>>>>>> I am reading a csv file with 2000 lines and 115 columns.
>>>>>>
>>>>>> Any idea?
>>>>>>
>>>>>> Thanks,
>>>>>>
>>>>>> Ivan  - Brazil
>>>>>> @ivanelson
>>>>>>
>>>>>>
>>>>>> ------------------------------------------------------------------------------
>>>>>> Master Visual Studio, SharePoint, SQL, ASP.NET <http://asp.net/>, C#
>>>>>> 2012, HTML5, CSS,
>>>>>> MVC, Windows 8 Apps, JavaScript and much more. Keep your skills
>>>>>> current
>>>>>> with LearnDevNow - 3,200 step-by-step video tutorials by Microsoft
>>>>>> MVPs and experts. ON SALE this month only -- learn more at:
>>>>>> http://p.sf.net/sfu/learnnow-d2d
>>>>>> _______________________________________________
>>>>>> cx-oracle-users mailing list
>>>>>> cx-...@li...
>>>>>> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>> ------------------------------------------------------------------------------
>>>>> Master Visual Studio, SharePoint, SQL, ASP.NET <http://asp.net/>, C#
>>>>> 2012, HTML5, CSS,
>>>>> MVC, Windows 8 Apps, JavaScript and much more. Keep your skills current
>>>>> with LearnDevNow - 3,200 step-by-step video tutorials by Microsoft
>>>>> MVPs and experts. ON SALE this month only -- learn more at:
>>>>> http://p.sf.net/sfu/learnnow-d2d
>>>>> _______________________________________________
>>>>> cx-oracle-users mailing list
>>>>> cx-...@li...
>>>>> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>>>>>
>>>>>
>>>>
>>>>
>>>>
>>>> ------------------------------------------------------------------------------
>>>> Master Visual Studio, SharePoint, SQL, ASP.NET <http://asp.net/>, C#
>>>> 2012, HTML5, CSS,
>>>> MVC, Windows 8 Apps, JavaScript and much more. Keep your skills current
>>>> with LearnDevNow - 3,200 step-by-step video tutorials by Microsoft
>>>> MVPs and experts. ON SALE this month only -- learn more at:
>>>> http://p.sf.net/sfu/learnnow-d2d
>>>> _______________________________________________
>>>> cx-oracle-users mailing list
>>>> cx-...@li...
>>>> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>>>>
>>>>
>>>
>>>
>>> ------------------------------------------------------------------------------
>>> Master Visual Studio, SharePoint, SQL, ASP.NET <http://asp.net/>, C#
>>> 2012, HTML5, CSS,
>>> MVC, Windows 8 Apps, JavaScript and much more. Keep your skills current
>>> with LearnDevNow - 3,200 step-by-step video tutorials by Microsoft
>>> MVPs and experts. ON SALE this month only -- learn more at:
>>> http://p.sf.net/sfu/learnnow-d2d
>>> _______________________________________________
>>> cx-oracle-users mailing list
>>> cx-...@li...
>>> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>>>
>>>
>>
>>
>>
>> ------------------------------------------------------------------------------
>> Master Visual Studio, SharePoint, SQL, ASP.NET <http://asp.net/>, C#
>> 2012, HTML5, CSS,
>> MVC, Windows 8 Apps, JavaScript and much more. Keep your skills current
>> with LearnDevNow - 3,200 step-by-step video tutorials by Microsoft
>> MVPs and experts. ON SALE this month only -- learn more at:
>> http://p.sf.net/sfu/learnnow-d2d
>> _______________________________________________
>> cx-oracle-users mailing list
>> cx-...@li...
>> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>>
>>
>
>
> ------------------------------------------------------------------------------
> Master Visual Studio, SharePoint, SQL, ASP.NET <http://asp.net/>, C#
> 2012, HTML5, CSS,
> MVC, Windows 8 Apps, JavaScript and much more. Keep your skills current
> with LearnDevNow - 3,200 step-by-step video tutorials by Microsoft
> MVPs and experts. ON SALE this month only -- learn more at:
> http://p.sf.net/sfu/learnnow-d2d
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>

Re: [cx-oracle-users] on stackoverflow? help re-open this question...

[Aurélien V. <val...@gm...>]

I don't understand my question "How does the internetz works" was closed
too. Please help me reopen it!

More seriously, I do agree this clearly is not a question, seems more like
you just are too lazy to type in "cx_Oracle tutorial" in google. Why would
anyone waste time answering this, except to get some free reputation points
in a forum/mmorpg such as stackoverflow.

On Thu, Jan 31, 2013 at 1:35 AM, Mark Harrison <mh...@pi...> wrote:

> Somebody oddly thinks this isn't a real question...
>
> http://stackoverflow.com/questions/592/how-do-i-access-oracle-from-python
>
> Check it out, and if you've got the points help reopen it!
>
>
> ------------------------------------------------------------------------------
> Everyone hates slow websites. So do we.
> Make your web apps faster with AppDynamics
> Download AppDynamics Lite for free today:
> http://p.sf.net/sfu/appdyn_d2d_jan
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>



-- 
Aurélien Vallée
+33 6 47 41 70 37

[cx-oracle-users] on stackoverflow? help re-open this question...

[Mark H. <mh...@pi...>]

Somebody oddly thinks this isn't a real question...

http://stackoverflow.com/questions/592/how-do-i-access-oracle-from-python

Check it out, and if you've got the points help reopen it!

Re: [cx-oracle-users] Error Expecting numeric Data

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

executemany() expects all of the data to be in the same format since it is
sent to the database in one package.
execute() is being nice to you and simply taking the effort to setup new
buffers when the data changes -- so it is slower but behaves as you expect.

Find the data type change and your executemany() will function as expected.

Anthony

On Sat, Jan 26, 2013 at 8:04 PM, Ivanelson Nunes
<iva...@gm...>wrote:

> One. Test: I sent the entire file of 30 to 30 lines and raised no
> exception.
> 2nd. Test: I changed the method "executetomany" by "execute" and recorded
> all 2000 lines without error.
>
> ?!
>
> 2013/1/26 Anthony Tuininga <ant...@gm...>
>
>> On Sat, Jan 26, 2013 at 7:37 PM, Ivanelson Nunes <
>> iva...@gm...> wrote:
>>
>>> Some type of limitation or restriction security. For when I insert only
>>> 30 lines generates no exception.
>>>
>>
>> No. This a programming error. You are passing *both* strings *and*
>> numbers at the same time. Oracle insists that it be one type or the other!
>> The first 30 lines are all of the same type, it would seem. The error
>> occurs sometime later. One suggestion: print the types of the tuples you
>> are inserting -- that should help you figure things out.
>>
>>
>>>
>>> My code is here:
>>> https://github.com/ivanelson/zim2ora/blob/master/zim2ora_bulk.py
>>>
>>> I also found these comments on this issue:
>>> https://github.com/dmwm/WMCore/issues/4297#issuecomment-10706585
>>>
>>>
>>> Any idea?
>>>
>>>
>>> []s
>>> 2013/1/26 Roger Downing <rog...@gm...>
>>>
>>>> Hi,
>>>> It would be helpful if you were to give more context to your code. What
>>>> is 'L' for example?
>>>>
>>>> cursor.executemany() expects as its second argument a sequence. The
>>>> elements of the sequence should either be sequences or parameter mappings
>>>> as described in the documentation.
>>>>
>>>> The error you are getting could mean that you are trying to insert
>>>> non-numeric data into a column which has been specified as numeric.
>>>>
>>>> Cheers,
>>>>
>>>> Roger
>>>>
>>>>
>>>> On 25 January 2013 18:19, Ivanelson Nunes <iva...@gm...>wrote:
>>>>
>>>>> What kind of error is this?
>>>>>
>>>>> cursor.executemany (sql_insert, L)
>>>>>> TypeError: expecting numeric data
>>>>>
>>>>>
>>>>> I am reading a csv file with 2000 lines and 115 columns.
>>>>>
>>>>> Any idea?
>>>>>
>>>>> Thanks,
>>>>>
>>>>> Ivan  - Brazil
>>>>> @ivanelson
>>>>>
>>>>>
>>>>> ------------------------------------------------------------------------------
>>>>> Master Visual Studio, SharePoint, SQL, ASP.NET <http://asp.net/>, C#
>>>>> 2012, HTML5, CSS,
>>>>> MVC, Windows 8 Apps, JavaScript and much more. Keep your skills current
>>>>> with LearnDevNow - 3,200 step-by-step video tutorials by Microsoft
>>>>> MVPs and experts. ON SALE this month only -- learn more at:
>>>>> http://p.sf.net/sfu/learnnow-d2d
>>>>> _______________________________________________
>>>>> cx-oracle-users mailing list
>>>>> cx-...@li...
>>>>> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>>>>>
>>>>>
>>>>
>>>>
>>>> ------------------------------------------------------------------------------
>>>> Master Visual Studio, SharePoint, SQL, ASP.NET <http://asp.net/>, C#
>>>> 2012, HTML5, CSS,
>>>> MVC, Windows 8 Apps, JavaScript and much more. Keep your skills current
>>>> with LearnDevNow - 3,200 step-by-step video tutorials by Microsoft
>>>> MVPs and experts. ON SALE this month only -- learn more at:
>>>> http://p.sf.net/sfu/learnnow-d2d
>>>> _______________________________________________
>>>> cx-oracle-users mailing list
>>>> cx-...@li...
>>>> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>>>>
>>>>
>>>
>>>
>>>
>>> ------------------------------------------------------------------------------
>>> Master Visual Studio, SharePoint, SQL, ASP.NET <http://asp.net/>, C#
>>> 2012, HTML5, CSS,
>>> MVC, Windows 8 Apps, JavaScript and much more. Keep your skills current
>>> with LearnDevNow - 3,200 step-by-step video tutorials by Microsoft
>>> MVPs and experts. ON SALE this month only -- learn more at:
>>> http://p.sf.net/sfu/learnnow-d2d
>>> _______________________________________________
>>> cx-oracle-users mailing list
>>> cx-...@li...
>>> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>>>
>>>
>>
>>
>> ------------------------------------------------------------------------------
>> Master Visual Studio, SharePoint, SQL, ASP.NET <http://asp.net/>, C#
>> 2012, HTML5, CSS,
>> MVC, Windows 8 Apps, JavaScript and much more. Keep your skills current
>> with LearnDevNow - 3,200 step-by-step video tutorials by Microsoft
>> MVPs and experts. ON SALE this month only -- learn more at:
>> http://p.sf.net/sfu/learnnow-d2d
>> _______________________________________________
>> cx-oracle-users mailing list
>> cx-...@li...
>> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>>
>>
>
>
>
> ------------------------------------------------------------------------------
> Master Visual Studio, SharePoint, SQL, ASP.NET, C# 2012, HTML5, CSS,
> MVC, Windows 8 Apps, JavaScript and much more. Keep your skills current
> with LearnDevNow - 3,200 step-by-step video tutorials by Microsoft
> MVPs and experts. ON SALE this month only -- learn more at:
> http://p.sf.net/sfu/learnnow-d2d
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>

Re: [cx-oracle-users] Error Expecting numeric Data

[Ivanelson N. <iva...@gm...>]

One. Test: I sent the entire file of 30 to 30 lines and raised no exception.
2nd. Test: I changed the method "executetomany" by "execute" and recorded
all 2000 lines without error.

?!

2013/1/26 Anthony Tuininga <ant...@gm...>

> On Sat, Jan 26, 2013 at 7:37 PM, Ivanelson Nunes <iva...@gm...
> > wrote:
>
>> Some type of limitation or restriction security. For when I insert only
>> 30 lines generates no exception.
>>
>
> No. This a programming error. You are passing *both* strings *and* numbers
> at the same time. Oracle insists that it be one type or the other! The
> first 30 lines are all of the same type, it would seem. The error occurs
> sometime later. One suggestion: print the types of the tuples you are
> inserting -- that should help you figure things out.
>
>
>>
>> My code is here:
>> https://github.com/ivanelson/zim2ora/blob/master/zim2ora_bulk.py
>>
>> I also found these comments on this issue:
>> https://github.com/dmwm/WMCore/issues/4297#issuecomment-10706585
>>
>>
>> Any idea?
>>
>>
>> []s
>> 2013/1/26 Roger Downing <rog...@gm...>
>>
>>> Hi,
>>> It would be helpful if you were to give more context to your code. What
>>> is 'L' for example?
>>>
>>> cursor.executemany() expects as its second argument a sequence. The
>>> elements of the sequence should either be sequences or parameter mappings
>>> as described in the documentation.
>>>
>>> The error you are getting could mean that you are trying to insert
>>> non-numeric data into a column which has been specified as numeric.
>>>
>>> Cheers,
>>>
>>> Roger
>>>
>>>
>>> On 25 January 2013 18:19, Ivanelson Nunes <iva...@gm...>wrote:
>>>
>>>> What kind of error is this?
>>>>
>>>> cursor.executemany (sql_insert, L)
>>>>> TypeError: expecting numeric data
>>>>
>>>>
>>>> I am reading a csv file with 2000 lines and 115 columns.
>>>>
>>>> Any idea?
>>>>
>>>> Thanks,
>>>>
>>>> Ivan  - Brazil
>>>> @ivanelson
>>>>
>>>>
>>>> ------------------------------------------------------------------------------
>>>> Master Visual Studio, SharePoint, SQL, ASP.NET <http://asp.net/>, C#
>>>> 2012, HTML5, CSS,
>>>> MVC, Windows 8 Apps, JavaScript and much more. Keep your skills current
>>>> with LearnDevNow - 3,200 step-by-step video tutorials by Microsoft
>>>> MVPs and experts. ON SALE this month only -- learn more at:
>>>> http://p.sf.net/sfu/learnnow-d2d
>>>> _______________________________________________
>>>> cx-oracle-users mailing list
>>>> cx-...@li...
>>>> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>>>>
>>>>
>>>
>>>
>>> ------------------------------------------------------------------------------
>>> Master Visual Studio, SharePoint, SQL, ASP.NET <http://asp.net/>, C#
>>> 2012, HTML5, CSS,
>>> MVC, Windows 8 Apps, JavaScript and much more. Keep your skills current
>>> with LearnDevNow - 3,200 step-by-step video tutorials by Microsoft
>>> MVPs and experts. ON SALE this month only -- learn more at:
>>> http://p.sf.net/sfu/learnnow-d2d
>>> _______________________________________________
>>> cx-oracle-users mailing list
>>> cx-...@li...
>>> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>>>
>>>
>>
>>
>>
>> ------------------------------------------------------------------------------
>> Master Visual Studio, SharePoint, SQL, ASP.NET <http://asp.net/>, C#
>> 2012, HTML5, CSS,
>> MVC, Windows 8 Apps, JavaScript and much more. Keep your skills current
>> with LearnDevNow - 3,200 step-by-step video tutorials by Microsoft
>> MVPs and experts. ON SALE this month only -- learn more at:
>> http://p.sf.net/sfu/learnnow-d2d
>> _______________________________________________
>> cx-oracle-users mailing list
>> cx-...@li...
>> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>>
>>
>
>
> ------------------------------------------------------------------------------
> Master Visual Studio, SharePoint, SQL, ASP.NET <http://asp.net/>, C#
> 2012, HTML5, CSS,
> MVC, Windows 8 Apps, JavaScript and much more. Keep your skills current
> with LearnDevNow - 3,200 step-by-step video tutorials by Microsoft
> MVPs and experts. ON SALE this month only -- learn more at:
> http://p.sf.net/sfu/learnnow-d2d
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>

Re: [cx-oracle-users] Error Expecting numeric Data

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

On Sat, Jan 26, 2013 at 7:37 PM, Ivanelson Nunes
<iva...@gm...>wrote:

> Some type of limitation or restriction security. For when I insert only 30
> lines generates no exception.
>

No. This a programming error. You are passing *both* strings *and* numbers
at the same time. Oracle insists that it be one type or the other! The
first 30 lines are all of the same type, it would seem. The error occurs
sometime later. One suggestion: print the types of the tuples you are
inserting -- that should help you figure things out.


>
> My code is here:
> https://github.com/ivanelson/zim2ora/blob/master/zim2ora_bulk.py
>
> I also found these comments on this issue:
> https://github.com/dmwm/WMCore/issues/4297#issuecomment-10706585
>
>
> Any idea?
>
>
> []s
> 2013/1/26 Roger Downing <rog...@gm...>
>
>> Hi,
>> It would be helpful if you were to give more context to your code. What
>> is 'L' for example?
>>
>> cursor.executemany() expects as its second argument a sequence. The
>> elements of the sequence should either be sequences or parameter mappings
>> as described in the documentation.
>>
>> The error you are getting could mean that you are trying to insert
>> non-numeric data into a column which has been specified as numeric.
>>
>> Cheers,
>>
>> Roger
>>
>>
>> On 25 January 2013 18:19, Ivanelson Nunes <iva...@gm...>wrote:
>>
>>> What kind of error is this?
>>>
>>> cursor.executemany (sql_insert, L)
>>>> TypeError: expecting numeric data
>>>
>>>
>>> I am reading a csv file with 2000 lines and 115 columns.
>>>
>>> Any idea?
>>>
>>> Thanks,
>>>
>>> Ivan  - Brazil
>>> @ivanelson
>>>
>>>
>>> ------------------------------------------------------------------------------
>>> Master Visual Studio, SharePoint, SQL, ASP.NET <http://asp.net/>, C#
>>> 2012, HTML5, CSS,
>>> MVC, Windows 8 Apps, JavaScript and much more. Keep your skills current
>>> with LearnDevNow - 3,200 step-by-step video tutorials by Microsoft
>>> MVPs and experts. ON SALE this month only -- learn more at:
>>> http://p.sf.net/sfu/learnnow-d2d
>>> _______________________________________________
>>> cx-oracle-users mailing list
>>> cx-...@li...
>>> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>>>
>>>
>>
>>
>> ------------------------------------------------------------------------------
>> Master Visual Studio, SharePoint, SQL, ASP.NET <http://asp.net/>, C#
>> 2012, HTML5, CSS,
>> MVC, Windows 8 Apps, JavaScript and much more. Keep your skills current
>> with LearnDevNow - 3,200 step-by-step video tutorials by Microsoft
>> MVPs and experts. ON SALE this month only -- learn more at:
>> http://p.sf.net/sfu/learnnow-d2d
>> _______________________________________________
>> cx-oracle-users mailing list
>> cx-...@li...
>> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>>
>>
>
>
>
> ------------------------------------------------------------------------------
> Master Visual Studio, SharePoint, SQL, ASP.NET, C# 2012, HTML5, CSS,
> MVC, Windows 8 Apps, JavaScript and much more. Keep your skills current
> with LearnDevNow - 3,200 step-by-step video tutorials by Microsoft
> MVPs and experts. ON SALE this month only -- learn more at:
> http://p.sf.net/sfu/learnnow-d2d
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>

Re: [cx-oracle-users] Error Expecting numeric Data

[Ivanelson N. <iva...@gm...>]

Some type of limitation or restriction security. For when I insert only 30
lines generates no exception.

My code is here:
https://github.com/ivanelson/zim2ora/blob/master/zim2ora_bulk.py

I also found these comments on this issue:
https://github.com/dmwm/WMCore/issues/4297#issuecomment-10706585


Any idea?


[]s
2013/1/26 Roger Downing <rog...@gm...>

> Hi,
> It would be helpful if you were to give more context to your code. What is
> 'L' for example?
>
> cursor.executemany() expects as its second argument a sequence. The
> elements of the sequence should either be sequences or parameter mappings
> as described in the documentation.
>
> The error you are getting could mean that you are trying to insert
> non-numeric data into a column which has been specified as numeric.
>
> Cheers,
>
> Roger
>
>
> On 25 January 2013 18:19, Ivanelson Nunes <iva...@gm...>wrote:
>
>> What kind of error is this?
>>
>> cursor.executemany (sql_insert, L)
>>> TypeError: expecting numeric data
>>
>>
>> I am reading a csv file with 2000 lines and 115 columns.
>>
>> Any idea?
>>
>> Thanks,
>>
>> Ivan  - Brazil
>> @ivanelson
>>
>>
>> ------------------------------------------------------------------------------
>> Master Visual Studio, SharePoint, SQL, ASP.NET <http://asp.net/>, C#
>> 2012, HTML5, CSS,
>> MVC, Windows 8 Apps, JavaScript and much more. Keep your skills current
>> with LearnDevNow - 3,200 step-by-step video tutorials by Microsoft
>> MVPs and experts. ON SALE this month only -- learn more at:
>> http://p.sf.net/sfu/learnnow-d2d
>> _______________________________________________
>> cx-oracle-users mailing list
>> cx-...@li...
>> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>>
>>
>
>
> ------------------------------------------------------------------------------
> Master Visual Studio, SharePoint, SQL, ASP.NET <http://asp.net/>, C#
> 2012, HTML5, CSS,
> MVC, Windows 8 Apps, JavaScript and much more. Keep your skills current
> with LearnDevNow - 3,200 step-by-step video tutorials by Microsoft
> MVPs and experts. ON SALE this month only -- learn more at:
> http://p.sf.net/sfu/learnnow-d2d
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>

Re: [cx-oracle-users] Error Expecting numeric Data

[Roger D. <rog...@gm...>]

Hi,
It would be helpful if you were to give more context to your code. What is
'L' for example?

cursor.executemany() expects as its second argument a sequence. The
elements of the sequence should either be sequences or parameter mappings
as described in the documentation.

The error you are getting could mean that you are trying to insert
non-numeric data into a column which has been specified as numeric.

Cheers,

Roger


On 25 January 2013 18:19, Ivanelson Nunes <iva...@gm...> wrote:

> What kind of error is this?
>
> cursor.executemany (sql_insert, L)
>> TypeError: expecting numeric data
>
>
> I am reading a csv file with 2000 lines and 115 columns.
>
> Any idea?
>
> Thanks,
>
> Ivan  - Brazil
> @ivanelson
>
>
> ------------------------------------------------------------------------------
> Master Visual Studio, SharePoint, SQL, ASP.NET, C# 2012, HTML5, CSS,
> MVC, Windows 8 Apps, JavaScript and much more. Keep your skills current
> with LearnDevNow - 3,200 step-by-step video tutorials by Microsoft
> MVPs and experts. ON SALE this month only -- learn more at:
> http://p.sf.net/sfu/learnnow-d2d
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>

[cx-oracle-users] Error Expecting numeric Data

[Ivanelson N. <iva...@gm...>]

What kind of error is this?

cursor.executemany (sql_insert, L)
> TypeError: expecting numeric data


I am reading a csv file with 2000 lines and 115 columns.

Any idea?

Thanks,

Ivan  - Brazil
@ivanelson

[cx-oracle-users] Documentation for Cursor.execute() wrong?

[Adam J. L. <ada...@gm...>]

Hello,
    It appears that the documentation at

http://cx-oracle.sourceforge.net/html/cursor.html#Cursor.execute

    is wrong with respect to what Cursor.execute() returns.

"If the statement is a query, a list of variable objects (Variable Objects) will be returned corresponding to the list of variables into which data will be fetched with the fetchone(), fetchmany() and fetchall() methods..."

    is different from the behavior I see and my reading of the source code (version 5.1.2):

-- Cursor.c, lines 1650 - 1658 --

    // for queries, return the cursor for convenience
    if (isQuery) {
        Py_INCREF(self);
        return (PyObject*) self;
    }

    // for all other statements, simply return None
    Py_INCREF(Py_None);
    return Py_None;

--

    A quick search of the archives for this mailing list didn't show a report for this.  I'm happy to submit a patch for html/cursor.html, but I thought devs might want to use their own language.

    Adam Lincoln

Re: [cx-oracle-users] Error loading cx_Oracle module: DLL load failed+PyISAPIe+IIS6

[Marilena P. <map...@ut...>]

I just downgraded to cx_Oracle 4.4.1 and I'm not getting this error any
more but I can only serve .py files with PyISAPIe. It doesn't seem that it
links with django because I get 'the page cannot be found' when I try to
link with the virtual folder. Do you have any idea why?My url
is:'(r'^login/$', 'vpms.views.login_user') where vpms is the django project
that's inside c:\wvpms. What's the proper way to see if the project works?


2013/1/14 Ivanelson Nunes <iva...@gm...>

> Recently, I had the same problem. In my case was with the Apache server.
>
> My solution was to insert inside / etc/apache2/envvars load environment
> variables:
>
> ORACLE_HOME and LD_LIBRARY_PATH
>
> In IIS, should have some startup file idem "envvars" of Apache Server.
>
> 2013/1/14 Marilena Papageorgiou <map...@ut...>
>
>> Hi,
>>
>> I have developed a Django application, and I am
>> trying to get it to run under IIS on Windows Server 2003. I'm
>> generally following the Django walkthrough [1] but my db is Oracle.
>>
>> If I run python from the command prompt and import cx_Oracle, it works
>> just fine. If I run the project with django's development server, everything works fine too.
>> However, when the Django application is loaded by IIS and
>> PyISAPIe, it says it cannot load cx_Oracle and displays the following
>> error:
>>
>> Error loading cx_Oracle module: DLL load failed: The specified module
>> could not be found.
>>
>> I'm using the 32-bit versions of the following:
>>
>> Python 2.6.6
>> PyISAPIe 1.1.0 rc4 py2.6
>> Django 1.4.3
>> Oracle Client 11.2
>> cx_Oracle 5.1 (for Oracle 11g, Python 2.6)
>>
>> Any thoughts on why this might be happening?
>>
>> [1]https://code.djangoproject.com/wiki/DjangoOnWindowsWithIISAndSQLServer
>>
>>
>>
>> ------------------------------------------------------------------------------
>> Master Visual Studio, SharePoint, SQL, ASP.NET, C# 2012, HTML5, CSS,
>> MVC, Windows 8 Apps, JavaScript and much more. Keep your skills current
>> with LearnDevNow - 3,200 step-by-step video tutorials by Microsoft
>> MVPs and experts. SALE $99.99 this month only -- learn more at:
>> http://p.sf.net/sfu/learnmore_122412
>> _______________________________________________
>> cx-oracle-users mailing list
>> cx-...@li...
>> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>>
>>
>
>
> ------------------------------------------------------------------------------
> Master Visual Studio, SharePoint, SQL, ASP.NET, C# 2012, HTML5, CSS,
> MVC, Windows 8 Apps, JavaScript and much more. Keep your skills current
> with LearnDevNow - 3,200 step-by-step video tutorials by Microsoft
> MVPs and experts. SALE $99.99 this month only -- learn more at:
> http://p.sf.net/sfu/learnmore_122412
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>

Re: [cx-oracle-users] Error loading cx_Oracle module: DLL load failed+PyISAPIe+IIS6

[Ivanelson N. <iva...@gm...>]

Recently, I had the same problem. In my case was with the Apache server.

My solution was to insert inside / etc/apache2/envvars load environment
variables:

ORACLE_HOME and LD_LIBRARY_PATH

In IIS, should have some startup file idem "envvars" of Apache Server.

2013/1/14 Marilena Papageorgiou <map...@ut...>

> Hi,
>
> I have developed a Django application, and I am
> trying to get it to run under IIS on Windows Server 2003. I'm
> generally following the Django walkthrough [1] but my db is Oracle.
>
> If I run python from the command prompt and import cx_Oracle, it works
> just fine. If I run the project with django's development server, everything works fine too.
> However, when the Django application is loaded by IIS and
> PyISAPIe, it says it cannot load cx_Oracle and displays the following
> error:
>
> Error loading cx_Oracle module: DLL load failed: The specified module
> could not be found.
>
> I'm using the 32-bit versions of the following:
>
> Python 2.6.6
> PyISAPIe 1.1.0 rc4 py2.6
> Django 1.4.3
> Oracle Client 11.2
> cx_Oracle 5.1 (for Oracle 11g, Python 2.6)
>
> Any thoughts on why this might be happening?
>
> [1]https://code.djangoproject.com/wiki/DjangoOnWindowsWithIISAndSQLServer
>
>
>
> ------------------------------------------------------------------------------
> Master Visual Studio, SharePoint, SQL, ASP.NET, C# 2012, HTML5, CSS,
> MVC, Windows 8 Apps, JavaScript and much more. Keep your skills current
> with LearnDevNow - 3,200 step-by-step video tutorials by Microsoft
> MVPs and experts. SALE $99.99 this month only -- learn more at:
> http://p.sf.net/sfu/learnmore_122412
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>

[cx-oracle-users] Error loading cx_Oracle module: DLL load failed+PyISAPIe+IIS6

[Marilena P. <map...@ut...>]

Hi,

I have developed a Django application, and I am
trying to get it to run under IIS on Windows Server 2003. I'm
generally following the Django walkthrough [1] but my db is Oracle.

If I run python from the command prompt and import cx_Oracle, it works
just fine. If I run the project with django's development server,
everything works fine too.
However, when the Django application is loaded by IIS and
PyISAPIe, it says it cannot load cx_Oracle and displays the following
error:

Error loading cx_Oracle module: DLL load failed: The specified module
could not be found.

I'm using the 32-bit versions of the following:

Python 2.6.6
PyISAPIe 1.1.0 rc4 py2.6
Django 1.4.3
Oracle Client 11.2
cx_Oracle 5.1 (for Oracle 11g, Python 2.6)

Any thoughts on why this might be happening?

[1]https://code.djangoproject.com/wiki/DjangoOnWindowsWithIISAndSQLServer

[cx-oracle-users] How to handle quotes of columns in CSV file

[Ivanelson N. <iva...@gm...>]

Oracle is giving me the error ORA: 17022. I know a type error.

I am reading a CSV with columns alpha and columns of value numeric.

The method csv.reader is making all columns as alpha and do not know how to
handle this in cx_Oracle, although I believe this is not a function of
cx_Oracle. :(


L = []
>
> sql_insert = ('INSERT INTO {0}_TEMP (%s) VALUES (%s)'.format(vTable) %
>
>               (','.join('%s' % name for name in vTables),
>
>                ','.join(':{0}'.format(vIndex + 1) for vIndex in
>> range(len(vTables)))))
>
>
>> reader = csv.reader(open(infile), delimiter=';', quotechar='"')
>
> for row in reader:
>
>         print row
>
>         L.append(tuple(row))
>
>
>> cursor.prepare(sql_insert)
>
> cursor.executemany(sql_insert, L)
>
>
My CSV:

"   2102";"LINDOMAR MARTINS DE FREITAS";"RUA 02
> NR.270";"FORMOSA";"98130860";"TIMON";"MA";"ADMINISTRATIVO";"FATURISTA";"LINDOMAR";"S";20130103;20130103;20130103;
> *100.00*;"TDC";"4";"88792334";"";"";""



 My print row:

> ['   2102', 'LINDOMAR MARTINS DE FREITAS', 'RUA 02 NR.270', 'FORMOSA',
> '98130860', 'TIMON', 'MA', 'ADMINISTRATIVO', 'FATURISTA', 'LINDOMAR', 'S',
> '20130103', '20130103', '20130103',* '100.00'*, 'TDC', '4', '88792334',
> '', '', '']


Bad quotes :(

Reference: http://www.dbaportal.eu/?q=node/143

[]s Thanks

[cx-oracle-users] timeout for query execution & connect

[Philip L. <ph...@yh...>]

I was wondering if the cx_oracle developers had considered adding timeout
support to the API (both for query execution & connection)?

We had an occurrence where firewall maintenance caused a query which
normally finishes very quickly to hang forever.  Adding long timeout
arguments to pymssql was trivial although there doesn't appear to be
support to do this in the Oracle driver yet. :(

-- 
Philip Lowman

[cx-oracle-users] easy_install broken

[<Ben...@cs...>]

Just a small thing that could be related to the sourceforge outage yesterday - but it seems that easy_install cx_Oracle isn't pointing at the correct file.

# easy_install --upgrade cx_Oracle
Searching for cx-Oracle
Reading http://www.python.org/pypi/cx_Oracle/
Reading http://cx-oracle.sourceforge.net
Reading http://www.python.org/pypi/cx_Oracle/5.1.2
Best match: cx-Oracle 5.1.2
Downloading http://dl.sourceforge.net/sourceforge/cx-oracle/cx_Oracle-5.1.2.tar.gz
error: Unexpected HTML page found at http://dl.sourceforge.net/sourceforge/cx-oracle/cx_Oracle-5.1.2.tar.gz

cheers

Ben

[cx-oracle-users] right way to use Unicode under Python 2.7?

[Chris R. <chr...@hu...>]

So, Unicode mode / WITH_UNICODE seems to have been removed a while ago
(https://bitbucket.org/anthony_tuininga/cx_oracle/commits/33157874357bc4209
48b90c7db28865d0138a3e2 ), although it's still referenced in the docs in a
couple places 
(http://cx-oracle.sourceforge.net/html/connection.html#Connection.encoding
<http://cx-oracle.sourceforge.net/html/connection.html?highlight=encoding#C
onnection.encoding> ).

What, then, is currently The Right Way™ to setup cx_Oracle to completely
use Unicode under Python 2.7?

Doesn't seem to be working quite right out-of-the-box (nor are the error
message or traceback particularly helpful):

>>> query = u"SELECT COL_A, COL_B FROM some_db.some_table WHERE COL_A LIKE
>>>:0 ESCAPE '@' ORDER BY COL_A ASC"
>>> param_vals = [u"%☂%"]  # U+2602 UMBRELLA
>>> param_vals
[u'%\u2602%']
>>> cur = connection.cursor()
>>> cur.execute(query, param_vals)
Traceback (most recent call last):
File "<console>", line 1, in <module>
UnicodeEncodeError: 'ascii' codec can't encode character u'\u2602' in
position 1: ordinal not in range(128)



Relevant versions:
Python 2.7.3
cx_Oracle 5.1.2
Oracle Instant Client for Linux x86-64 11.2.0.3.0


Thanks,
Chris Rebert

Re: [cx-oracle-users] cx_Oracle connection recovery subsequent to successful two-phase sequence

[Christian F. <ca...@ya...>]

Dear Michael,
I posted this message (https://sourceforge.net/mailarchive/message.php?msg_id=28850922) on this list on 2012-02-17 and I'm copying it again due to some limitations of the SourceForge archive viewer.
Unfortunately no one replyed me and I guessed there is no real interest in distributed transaction programming inside the Python community.

Regards
Ch.F.


From the previous post:

Dear All,
I'm the founder of LIXA project ( http://sourceforge.net/projects/lixa/ http://lixa.sourceforge.net/manuals/ ) and I'm interested in integrating LIXA with cx_Oracle.
LIXA
 is LIbre XA, a free and open source XA compliant transaction manager: 
it implements XA interface to deal with the Resouce Managers and 
implements TX interface to deal with the Application Programs.
LIXA 
supports Oracle as a Resource Manager and allows to create distributed 
transaction s (2 phase commit) with PostgreSQL, MySQL, Oracle and DB2.
XA and TX are interfaces designed for C (and C++) programs.
I am now interested in expanding the LIXA project scope to Python language and Python programmers.
It seems to me cx_Oracle is a good start point because it wraps the Oracle C API; LIXA too uses OCI.

It's time to describe my issue to ask some help to you.
First of all, I'm not a Python expert.
I've
 generated a LIXA wrapper using SWIG and it works fine, the usage is 
something like this (my development environment is based on Ubuntu 
10.04):

tiian@mojan:~/src/swig$ python
Python 2.6.5 (r265:79063, Apr 16 2010, 13:09:56)
[GCC 4.4.3] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import lixa
>>> lixa.tx_open()
0
>>> lixa.tx_begin()
0
>>> lixa.tx_commit()
0
>>> lixa.tx_close()
0
>>>


tx_open(),
 tx_begin(), tx_commit(), tx_close() are the standard functions 
described in "TX (Transaction Demarcation) Specification" (CAE X/Open 
standard).
The operations related to the Resource Managers must be put between "tx_begin()" and "tx_commit()".

TX
 standard specifies the Resource Managers must be opened by the 
Transaction Manager using "tx_open()" by the Application Program. This 
is the key point: cx_Oracle supplies its own method to get a valid 
Oracle session: "cx_Oracle.connect(...)", while LIXA wants to create it 
using "lixa.tx_open()": the Transaction Manager must open all the 
Resource Managers, not only Oracle.
Using C as the development 
language, Oracle supplies some convenience methods to retrieve the 
environment and the context for a connection opened by an XA Transaction
 Manager: xaoEnv, xaoSvcCtx.

The following one is an excerpt from a C sample (http://lixa.svn.sourceforge.net/viewvc/lixa/doc/examples/example2_ora.c?revision=703&view=markup): 

[...]

    /* open the resource manager(s) */
    if (TX_OK != (txrc = tx_open())) {
        fprintf(stderr, "tx_open error: %d\n", txrc);
        exit(txrc);
    }

    /* retrieve environment and context */
    if (NULL == (oci_env = xaoEnv(NULL))) {
        fprintf(stderr, "xaoEnv returned a NULL pointer\n");
        exit(1);
    }
    if (NULL == (oci_svc_ctx = xaoSvcCtx(NULL))) {
        fprintf(stderr, "xaoSvcCtx returned a NULL pointer\n");
        exit(1);
    }

    /* allocate statement and error handles */
    if (0 != OCIHandleAlloc( (dvoid *)oci_env, (dvoid **)&stmt_hndl,
                             OCI_HTYPE_STMT, (size_t)0, (dvoid **)0)) {
        fprintf(stderr, "Unable to allocate statement handle\n");
        exit(1);
    }
    if (0 != OCIHandleAlloc( (dvoid *)oci_env, (dvoid **)&err_hndl,
                             OCI_HTYPE_ERROR, (size_t)0, (dvoid **)0)) {
        fprintf(stderr, "Unable to allocate error handle\n");
        exit(1);
    }

[...]

the
 Application Program does not use "OCIEnvNlsCreate(...)": the 
Transaction Manager calls "xa_open()" ("xaoopen") inside "tx_open()" and
 the Application Program retrieves the environment using a convenience 
method (xaoEnv).
The same happens when the connection must be closed using "tx_close()".

Thinking about integration between LIXA and cx_Oracle I'm proposing three different paths:
1.
 patching cx_Oracle substituting "OCIEnvNlsCreate" with "xaoEnv" and 
removing some functions using user/password, SID, etc... I don't like 
this solution because the patch must be managed and tested every time 
cx_Oracle releases something new; I don't think it would be well 
accepted by the users too

2. it could be easy to overload the 
"cx_Oracle.connect()" method: if it accepted something like a wrapped 
"OCIEnv *" too, the integration would be something like: 
cx_Oracle.connect(lixa.lixa_ora_get_ocienv() )
The proposed use case would be something like this:
import lixa
import cx_Oracle
import ...        (other Resource Manager modules)
lixa.tx_open()    ... create Transaction Manager context 
                  and open Resource Managers
liora = lixa.lixa_ora_get_ocienv()
conn = cx_Oracle.connect( some_function(liora) )
... some stuff ...
lixa.tx_begin()
... insert/update/delete using conn object ...
lixa.tx_commit()
conn.close()      ... it should destroy the Python object without calling the Oracle's close function
lixa.tx_close()   ... destroy Transaction Manager context 
                  and close Resource Manager connections 
                  using xa_close() (xaoclose)

3. it could be possible to call the logic of cx_Oracle.connect() and conn.close() inside lixa.tx_open() and lixa.tx_close()
The proposed use case would be something like this:
import lixa
import cx_Oracle
import ...        (other Resource Manager modules)
lixa.tx_open()    ... create Transaction Manager context, 
                  open Resource Managers, 
                  call cx_Oracle.connect logic (without OCIEnvNlsCreate)
conn = lixa.lixa_ora_get_conn()
... some stuff ...
lixa.tx_begin()
... insert/update/delete using conn object ...
lixa.tx_commit()
lixa.tx_close()   ... destroy Transaction Manager context, 
                  close Resource Manager connections, destroy 
                  conn connection object calling 
                  conn.close (cx_Oracle logic)

Option
 3 requires more integration between LIXA and cx_Oracle object but could
 be more acceptable from the development community.

What's your opinions and suggestions?


>________________________________
> Da: Michael Bayer <mi...@zz...>
>A: cx-...@li... 
>Inviato: Venerdì 7 Dicembre 2012 0:01
>Oggetto: [cx-oracle-users] cx_Oracle connection recovery subsequent to successful two-phase sequence
> 
>Hi all -
>
>I've identified a probable bug in the mechanism by which cx_oracle engages in a two-phase transaction.
>
>The state of the connection changes permanently after a two-phase begin() is emitted, and the connection has completed, either with commit() or rollback(), subsequent to the prepare() call.   It appears that once an XID is associated with the connection, this association cannot be removed, and the traditional DBAPI commit sequence no longer functions - a call to begin() is required for all subsequent usages, else the error "ORA-24776: cannot start a new transaction" is emitted.  Additionally, once this error is emitted, the connection is then in a permanently unusable state - many methods, including rollback(), commit(), and even close(),  will now produce this error unconditionally, whether or not begin() is called.   The connection cannot even be closed without exiting the application.
>
>The attached test case illustrates this, by running three different scenarios, followed by a traditional DBAPI conversation with two statements and two commit() calls.  When preceded by a successful two-phase sequence, the connection can no longer be used without emitting begin() on each call; the test suite then fails within its tearDown() sequence as the connection cannot even be closed.  I'm running with cx_Oracle 5.1.2, Python 2.6 against Oracle 10g Expression Edition on Fedora Linux.
>
>python test_oracle_twophase.py 
>..EE
>======================================================================
>ERROR: test_run_after_twophase_begin (__main__.OracleTwoPhaseTest)
>----------------------------------------------------------------------
>Traceback (most recent call last):
>File "test_oracle_twophase.py", line 78, in test_run_after_twophase_begin
>   self._run_traditional_dbapi_transaction(conn)
>File "test_oracle_twophase.py", line 46, in _run_traditional_dbapi_transaction
>   conn.commit()
>DatabaseError: ORA-24776: cannot start a new transaction
>
>
>======================================================================
>ERROR: test_run_after_twophase_begin (__main__.OracleTwoPhaseTest)
>----------------------------------------------------------------------
>Traceback (most recent call last):
>File "test_oracle_twophase.py", line 33, in tearDown
>   self.conn.close()
>DatabaseError: ORA-24776: cannot start a new transaction
>
>
>----------------------------------------------------------------------
>Ran 3 tests in 0.853s
>
>FAILED (errors=2)
>
>I've tried to take some naive shots at the solution to this, which appears that OCITransDetach() (maybe?) should be called after a commit() or rollback() from a two phase transaction (http://docs.oracle.com/cd/B14117_01/appdev.101/b10779/oci16ms8.htm#423200).   Though it appears this function needs to be called carefully and my initial attempts at just sticking it in there were not successful.
>
>I'm very much hoping the cx_Oracle community can confirm this behavior is considered as a bug, or at least provide guidance on some means of returning a connection back to a usable state after this error condition has proceeded.   With some guidelines on whether or not OCITransDetach() is the right path, I can attempt to provide a patch.
>
>
>
>
>
>
>
>------------------------------------------------------------------------------
>LogMeIn Rescue: Anywhere, Anytime Remote support for IT. Free Trial
>Remotely access PCs and mobile devices and provide instant support
>Improve your efficiency, and focus on delivering more value-add services
>Discover what IT Professionals Know. Rescue delivers
>http://p.sf.net/sfu/logmein_12329d2d
>_______________________________________________
>cx-oracle-users mailing list
>cx-...@li...
>https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>
> 

[cx-oracle-users] cx_Oracle connection recovery subsequent to successful two-phase sequence

[Michael B. <mi...@zz...>]

Hi all -

I've identified a probable bug in the mechanism by which cx_oracle engages in a two-phase transaction.

The state of the connection changes permanently after a two-phase begin() is emitted, and the connection has completed, either with commit() or rollback(), subsequent to the prepare() call.   It appears that once an XID is associated with the connection, this association cannot be removed, and the traditional DBAPI commit sequence no longer functions - a call to begin() is required for all subsequent usages, else the error "ORA-24776: cannot start a new transaction" is emitted.  Additionally, once this error is emitted, the connection is then in a permanently unusable state - many methods, including rollback(), commit(), and even close(),  will now produce this error unconditionally, whether or not begin() is called.   The connection cannot even be closed without exiting the application.

The attached test case illustrates this, by running three different scenarios, followed by a traditional DBAPI conversation with two statements and two commit() calls.  When preceded by a successful two-phase sequence, the connection can no longer be used without emitting begin() on each call; the test suite then fails within its tearDown() sequence as the connection cannot even be closed.  I'm running with cx_Oracle 5.1.2, Python 2.6 against Oracle 10g Expression Edition on Fedora Linux.

python test_oracle_twophase.py 
..EE
======================================================================
ERROR: test_run_after_twophase_begin (__main__.OracleTwoPhaseTest)
----------------------------------------------------------------------
Traceback (most recent call last):
 File "test_oracle_twophase.py", line 78, in test_run_after_twophase_begin
   self._run_traditional_dbapi_transaction(conn)
 File "test_oracle_twophase.py", line 46, in _run_traditional_dbapi_transaction
   conn.commit()
DatabaseError: ORA-24776: cannot start a new transaction


======================================================================
ERROR: test_run_after_twophase_begin (__main__.OracleTwoPhaseTest)
----------------------------------------------------------------------
Traceback (most recent call last):
 File "test_oracle_twophase.py", line 33, in tearDown
   self.conn.close()
DatabaseError: ORA-24776: cannot start a new transaction


----------------------------------------------------------------------
Ran 3 tests in 0.853s

FAILED (errors=2)

I've tried to take some naive shots at the solution to this, which appears that OCITransDetach() (maybe?) should be called after a commit() or rollback() from a two phase transaction (http://docs.oracle.com/cd/B14117_01/appdev.101/b10779/oci16ms8.htm#423200).   Though it appears this function needs to be called carefully and my initial attempts at just sticking it in there were not successful.

I'm very much hoping the cx_Oracle community can confirm this behavior is considered as a bug, or at least provide guidance on some means of returning a connection back to a usable state after this error condition has proceeded.   With some guidelines on whether or not OCITransDetach() is the right path, I can attempt to provide a patch.

Re: [cx-oracle-users] Query Result Change Notification

[Yann C. <yan...@un...>]

Le 24/11/2012 04:53, Glen Walker a écrit :
> Hi Yann,
>
> In the example code I used qos=cx_Oracle.SUBSCR_QOS_PURGE_ON_NTFN in 
> the call to connection.subscribe. This was intended to demonstrate use 
> of the option, but in your case you don't want this behaviour. If you 
> remove the qos argument from the call to connection.subscribe you 
> should continue to receive notifications until you unsubscribe.
>
> Regards,
> Glen
>
I spent so much time trying to understand the behaviour focusing
on the low-level layer, that I didn't even look at the options I was 
using...
I'm feeling so stupid now...

Thanks a lot !

-- 
Yann Cézard  -  infrastructures - administrateur systèmes serveurs
Centre de ressources informatiques    -     http://cri.univ-pau.fr
Université de Pau et des pays de l'Adour -  http://www.univ-pau.fr

Re: [cx-oracle-users] Query Result Change Notification

[Glen W. <gl...@wa...>]

On 24/11/12 3:56 AM, Yann Cézard wrote:
> Le 23/11/2012 09:09, Yann Cézard a écrit :
>> Le 23/11/2012 03:43, Glen Walker a écrit :
>>>
>>> [...]
>>> Hi,
>>>
>>> I've submitted a pull request with my changes to support query 
>>> result set change notification. You can find it on Bitbucket 
>>> <https://bitbucket.org/anthony_tuininga/cx_oracle/pull-request/2/> 
>>> if you don't want to wait. Sample code below.
>>>
>>> Kind regards,
>>> Glen
>>>
>> Thanks a lot, Glen.
>> I'll do some tests as soon as possible (this afternoon if I can) and
>> I will give my feedback.
>>
>> Regards,
> Ok, so I did try a similar example as yours, I just updated the query 
> to my use case,
> and it works ! but only one time... :-)
> I mean, the first change in the database that modifies the query 
> result set is well
> sent to my client. But than, any other change that affects the query 
> result set isn't
> notified anymore. If I close the client, and restart it, the first 
> change is notified and
> then nothing.
>
> It's as if the server deregistered my subscription after the first 
> notification.
>
> If I look at the USER_CHANGE_NOTIFICATION_REGS table, after the client 
> is launched,
> the query is there. After the first notification, it isn't anymore.
>
> And as a matter of fact, in the USER_CHANGE_NOTIFICATION_REGS table, 
> REGFLAGS is
> 6 (if rowids is enabled) or 2 (if rowids is disabled) in QRCN mode, 
> and it's 0 (rowids disabled)
> or 4 (rowids enabled) in OCN mode (which doesn't have this behaviour).
> So it looks like :
> QOS_DEREG_NFY    BINARY_INTEGER    2    Purge registration on first 
> notification
> is enabled by default ?
> (http://docs.oracle.com/cd/B28359_01/appdev.111/b28424/adfns_cqn.htm#autoId21)
>
> Is it a wanted behaviour ? Am I the only one to have this behaviour by 
> default ?
> And is there a way to disable it ? (I didn't find any mention of 
> QOS_DEREG_NFY
> in the code... I'll try to look further !).
>
> Thanks for your work anyway, it is so close to what I was expecting !
>
> Regards,
> -- 
> Yann Cézard - administrateur systèmes serveurs
> Centre de ressources informatiques    -http://cri.univ-pau.fr
> Université de Pau et des pays de l'Adour -http://www.univ-pau.fr
Hi Yann,

In the example code I used qos=cx_Oracle.SUBSCR_QOS_PURGE_ON_NTFN in the 
call to connection.subscribe. This was intended to demonstrate use of 
the option, but in your case you don't want this behaviour. If you 
remove the qos argument from the call to connection.subscribe you should 
continue to receive notifications until you unsubscribe.

Regards,
Glen

Re: [cx-oracle-users] Query Result Change Notification

[Yann C. <yan...@un...>]

Le 23/11/2012 09:09, Yann Cézard a écrit :
> Le 23/11/2012 03:43, Glen Walker a écrit :
>>
>> [...]
>> Hi,
>>
>> I've submitted a pull request with my changes to support query result
>> set change notification. You can find it on Bitbucket
>> <https://bitbucket.org/anthony_tuininga/cx_oracle/pull-request/2/> if
>> you don't want to wait. Sample code below.
>>
>> Kind regards,
>> Glen
>>
> Thanks a lot, Glen.
> I'll do some tests as soon as possible (this afternoon if I can) and
> I will give my feedback.
>
> Regards,
Ok, so I did try a similar example as yours, I just updated the query to
my use case,
and it works ! but only one time... :-)
I mean, the first change in the database that modifies the query result
set is well
sent to my client. But than, any other change that affects the query
result set isn't
notified anymore. If I close the client, and restart it, the first
change is notified and
then nothing.

It's as if the server deregistered my subscription after the first
notification.

If I look at the USER_CHANGE_NOTIFICATION_REGS table, after the client
is launched,
the query is there. After the first notification, it isn't anymore.

And as a matter of fact, in the USER_CHANGE_NOTIFICATION_REGS table,
REGFLAGS is
6 (if rowids is enabled) or 2 (if rowids is disabled) in QRCN mode, and
it's 0 (rowids disabled)
or 4 (rowids enabled) in OCN mode (which doesn't have this behaviour).
So it looks like :
QOS_DEREG_NFY    BINARY_INTEGER    2    Purge registration on first
notification
is enabled by default ?
(http://docs.oracle.com/cd/B28359_01/appdev.111/b28424/adfns_cqn.htm#autoId21)

Is it a wanted behaviour ? Am I the only one to have this behaviour by
default ?
And is there a way to disable it ? (I didn't find any mention of
QOS_DEREG_NFY
in the code... I'll try to look further !).

Thanks for your work anyway, it is so close to what I was expecting !

Regards,

-- 
Yann Cézard - administrateur systèmes serveurs
Centre de ressources informatiques    -     http://cri.univ-pau.fr
Université de Pau et des pays de l'Adour -  http://www.univ-pau.fr

Re: [cx-oracle-users] Query Result Change Notification

[Yann C. <yan...@un...>]

Le 23/11/2012 03:43, Glen Walker a écrit :
>
> On 17/11/12 9:17 AM, Yann Cézard wrote:
>> Le 16/11/2012 14:20, Glen Walker a écrit :
>>> Hi,
>>>
>>> 2012/11/15 Yann Cézard <yann.cezard@un...>:
>>> > I have recently discovered the Oracle "Database Change Notification"
>>> > functionnality, which is really useful for one of my project, and looking at
>>> > some docs to understand more about it, I found about QRCN (Query Result Change
>>> > Notification) which might responds to my needs even more than DCN.
>>> > 
>>> > Searching the web about QRCN and cx_Oracle, I found this really useful message
>>> > which was posted one year and a half ago on this same list :
>>> > (http://osdir.com/ml/python-db-cx-oracle/2011-04/msg00004.html)
>>>
>>> > So now I was wondering if there are any plan to introduce the QRCN feature in
>>> > cx_Oracle in the future, or if anybody tried (and succeed ?) to make the
>>> > suggested code modification ? Or is such a functionnality is include under
>>> > another name in cx_Oracle ?
>>>
>>> As luck would have it a project I am working on would also benefit from QRCN.
>>> After reading the message from 2011-04 a few days ago I decided to implement
>>> this myself and contribute it to the project. I have now finished the update
>>> and need to know the best way to submit a pull request or patch. I have just
>>> joined the mailing list, so perhaps a list regular could point me in the right
>>> direction - I see that cx_Oracle is on Bitbucket:
>>>   https://bitbucket.org/anthony_tuininga/cx_oracle/
>>> as well as Sourceforge:
>>>   http://sourceforge.net/projects/cx-oracle/
>>>
>>> Regards,
>>> Glen
>>>
>> Great, that's very good news !
>>
>> I will test as soon as it is available. Thanks a lot !
>>
>> Best regards,
>>
>> Yann
>
> Hi,
>
> I've submitted a pull request with my changes to support query result
> set change notification. You can find it on Bitbucket
> <https://bitbucket.org/anthony_tuininga/cx_oracle/pull-request/2/> if
> you don't want to wait. Sample code below.
>
> Kind regards,
> Glen
>
Thanks a lot, Glen.
I'll do some tests as soon as possible (this afternoon if I can) and
I will give my feedback.

Regards,
>
>
> import time
> import cx_Oracle
>
>
> def EventToString(event):
>     if event == cx_Oracle.EVENT_NONE:
>         return 'None'
>     elif event == cx_Oracle.EVENT_STARTUP:
>         return 'Startup'
>     elif event == cx_Oracle.EVENT_SHUTDOWN:
>         return 'Shutdown'
>     elif event == cx_Oracle.EVENT_SHUTDOWN_ANY:
>         return 'ShutdownAny'
>     elif event == cx_Oracle.EVENT_DEREG:
>         return 'Dereg'
>     elif event == cx_Oracle.EVENT_OBJCHANGE:
>         return 'ObjChange'
>     elif event == cx_Oracle.EVENT_QUERYCHANGE:
>         return 'QueryChange'
>     return 'Unknown'
>
>
> def OperationToString(operation):
>     operations = []
>     if operation & cx_Oracle.OPCODE_INSERT:
>         operations.append("insert")
>     if operation & cx_Oracle.OPCODE_UPDATE:
>         operations.append("update")
>     if operation & cx_Oracle.OPCODE_DELETE:
>         operations.append("delete")
>     if operation & cx_Oracle.OPCODE_ALTER:
>         operations.append("alter")
>     if operation & cx_Oracle.OPCODE_DROP:
>         operations.append("drop")
>     if operation & cx_Oracle.OPCODE_ALLOPS:
>         operations.append("all operations")
>     if operation & cx_Oracle.OPCODE_ALLROWS:
>         operations.append("all rows")
>     return ", ".join(operations)
>
>
> def onChange(message):
>    print "=" * 60
>     print 'onChange subscription id %d' % (message.subscription.id, )
>
>     print "Message type:", message.type, EventToString(message.type)
>     print "Message database name:", message.dbname
>     if message.tables is not None:
>         print "Message tables:"
>         for table in message.tables:
>             print "--> Table Name:", table.name
>             print "--> Table Operation:", table.operation,
> OperationToString(table.operation)
>             if table.rows is not None:
>                 print "--> Table Rows:"
>                 for row in table.rows:
>                     print "--> --> Row RowId:", row.rowid
>                     print "--> --> Row Operation:", row.operation,
> OperationToString(row.operation)
>                     print "-" * 60
>             print "=" * 60
>     if message.queries is not None:
>         print "Message queries:"
>         for query in message.queries:
>             print "--> Query ID:", query.id
>             print "--> Query Operation:", query.operation,
> EventToString(query.operation)
>             if query.tables is not None:
>                 print "--> Query tables:"
>                 for table in query.tables:
>                     print "--> --> Table Name:", table.name
>                     print "--> --> Table Operation:", table.operation,
> OperationToString(table.operation)
>                     if table.rows is not None:
>                         print "--> --> Table Rows:"
>                         for row in table.rows:
>                             print "--> --> --> Row RowId:", row.rowid
>                             print "--> --> --> Row Operation:",
> row.operation, OperationToString(row.operation)
>                             print "-" * 60
>             print "=" * 60
>
>
> print 'Connecting'
>
> dsn = cx_Oracle.makedsn(...)
> connection = cx_Oracle.connect(user=username, password=password,
> dsn=dsn, threaded=True, events=True)
>
>
> subscription = connection.subscribe(callback=onChange,
> operations=cx_Oracle.OPCODE_ALLOPS,
>        
> qos=cx_Oracle.SUBSCR_QOS_PURGE_ON_NTFN,cqqos=cx_Oracle.SUBSCR_CQ_QOS_QUERY,
>         rowids=True)
>
> print "Subscription:", subscription
> print "--> Connection:", subscription.connection
> print "--> Callback:", subscription.callback
> print "--> Namespace:", subscription.namespace
> print "--> Protocol:", subscription.protocol
> print "--> Port:", subscription.port
> print "--> Timeout:", subscription.timeout
> print "--> Operations:", subscription.operations
> print "--> QOS:", subscription.qos
> print "--> CQ QOS:", subscription.cqqos
> print "--> Rowids:", subscription.rowids
> print "--> Id:", subscription.id
>
> queryId1 = subscription.registerquery('SELECT * FROM TEST_TABLE WHERE
> TEST_INT <= 3')
> print "Query ID 1:", queryId1
>
> queryId2 = subscription.registerquery('SELECT * FROM TEST_TABLE WHERE
> TEST_INT > 3')
> print "Query ID 2:", queryId2
>
> print 'Waiting'
> try:
>     time.sleep(6000)
> except KeyboardInterrupt:
>     pass
>
>
> print 'Unsubscribing'
> subscription = None
>
> print 'Disconnecting'
> connection.close()


-- 
Yann Cézard - administrateur systèmes serveurs
Centre de ressources informatiques    -     http://cri.univ-pau.fr
Université de Pau et des pays de l'Adour -  http://www.univ-pau.fr
bâtiment d'Alembert (anciennement IFR), rue Jules Ferry, 64000 Pau
Téléphone : +33 (0)5 59 40 77 94

Re: [cx-oracle-users] Query Result Change Notification

[Glen W. <gl...@wa...>]

On 17/11/12 9:17 AM, Yann Cézard wrote:
> Le 16/11/2012 14:20, Glen Walker a écrit :
>> Hi,
>>
>> 2012/11/15 Yann Cézard<yann.cezard@un...>:
>> > I have recently discovered the Oracle "Database Change Notification"
>> > functionnality, which is really useful for one of my project, and looking at
>> > some docs to understand more about it, I found about QRCN (Query Result Change
>> > Notification) which might responds to my needs even more than DCN.
>> >
>> > Searching the web about QRCN and cx_Oracle, I found this really useful message
>> > which was posted one year and a half ago on this same list :
>> > (http://osdir.com/ml/python-db-cx-oracle/2011-04/msg00004.html)
>>
>> > So now I was wondering if there are any plan to introduce the QRCN feature in
>> > cx_Oracle in the future, or if anybody tried (and succeed ?) to make the
>> > suggested code modification ? Or is such a functionnality is include under
>> > another name in cx_Oracle ?
>>
>> As luck would have it a project I am working on would also benefit from QRCN.
>> After reading the message from 2011-04 a few days ago I decided to implement
>> this myself and contribute it to the project. I have now finished the update
>> and need to know the best way to submit a pull request or patch. I have just
>> joined the mailing list, so perhaps a list regular could point me in the right
>> direction - I see that cx_Oracle is on Bitbucket:
>>    https://bitbucket.org/anthony_tuininga/cx_oracle/
>> as well as Sourceforge:
>>    http://sourceforge.net/projects/cx-oracle/
>>
>> Regards,
>> Glen
>>
> Great, that's very good news !
>
> I will test as soon as it is available. Thanks a lot !
>
> Best regards,
>
> Yann

Hi,

I've submitted a pull request with my changes to support query result 
set change notification. You can find it on Bitbucket 
<https://bitbucket.org/anthony_tuininga/cx_oracle/pull-request/2/> if 
you don't want to wait. Sample code below.

Kind regards,
Glen



import time
import cx_Oracle


def EventToString(event):
     if event == cx_Oracle.EVENT_NONE:
         return 'None'
     elif event == cx_Oracle.EVENT_STARTUP:
         return 'Startup'
     elif event == cx_Oracle.EVENT_SHUTDOWN:
         return 'Shutdown'
     elif event == cx_Oracle.EVENT_SHUTDOWN_ANY:
         return 'ShutdownAny'
     elif event == cx_Oracle.EVENT_DEREG:
         return 'Dereg'
     elif event == cx_Oracle.EVENT_OBJCHANGE:
         return 'ObjChange'
     elif event == cx_Oracle.EVENT_QUERYCHANGE:
         return 'QueryChange'
     return 'Unknown'


def OperationToString(operation):
     operations = []
     if operation & cx_Oracle.OPCODE_INSERT:
         operations.append("insert")
     if operation & cx_Oracle.OPCODE_UPDATE:
         operations.append("update")
     if operation & cx_Oracle.OPCODE_DELETE:
         operations.append("delete")
     if operation & cx_Oracle.OPCODE_ALTER:
         operations.append("alter")
     if operation & cx_Oracle.OPCODE_DROP:
         operations.append("drop")
     if operation & cx_Oracle.OPCODE_ALLOPS:
         operations.append("all operations")
     if operation & cx_Oracle.OPCODE_ALLROWS:
         operations.append("all rows")
     return ", ".join(operations)


def onChange(message):
print "=" * 60
     print 'onChange subscription id %d' % (message.subscription.id, )

     print "Message type:", message.type, EventToString(message.type)
     print "Message database name:", message.dbname
     if message.tables is not None:
         print "Message tables:"
         for table in message.tables:
             print "--> Table Name:", table.name
             print "--> Table Operation:", table.operation, 
OperationToString(table.operation)
             if table.rows is not None:
                 print "--> Table Rows:"
                 for row in table.rows:
                     print "--> --> Row RowId:", row.rowid
                     print "--> --> Row Operation:", row.operation, 
OperationToString(row.operation)
                     print "-" * 60
             print "=" * 60
     if message.queries is not None:
         print "Message queries:"
         for query in message.queries:
             print "--> Query ID:", query.id
             print "--> Query Operation:", query.operation, 
EventToString(query.operation)
             if query.tables is not None:
                 print "--> Query tables:"
                 for table in query.tables:
                     print "--> --> Table Name:", table.name
                     print "--> --> Table Operation:", table.operation, 
OperationToString(table.operation)
                     if table.rows is not None:
                         print "--> --> Table Rows:"
                         for row in table.rows:
                             print "--> --> --> Row RowId:", row.rowid
                             print "--> --> --> Row Operation:", 
row.operation, OperationToString(row.operation)
                             print "-" * 60
             print "=" * 60


print 'Connecting'

dsn = cx_Oracle.makedsn(...)
connection = cx_Oracle.connect(user=username, password=password, 
dsn=dsn, threaded=True, events=True)


subscription = connection.subscribe(callback=onChange, 
operations=cx_Oracle.OPCODE_ALLOPS,
         
qos=cx_Oracle.SUBSCR_QOS_PURGE_ON_NTFN,cqqos=cx_Oracle.SUBSCR_CQ_QOS_QUERY,
         rowids=True)

print "Subscription:", subscription
print "--> Connection:", subscription.connection
print "--> Callback:", subscription.callback
print "--> Namespace:", subscription.namespace
print "--> Protocol:", subscription.protocol
print "--> Port:", subscription.port
print "--> Timeout:", subscription.timeout
print "--> Operations:", subscription.operations
print "--> QOS:", subscription.qos
print "--> CQ QOS:", subscription.cqqos
print "--> Rowids:", subscription.rowids
print "--> Id:", subscription.id

queryId1 = subscription.registerquery('SELECT * FROM TEST_TABLE WHERE 
TEST_INT <= 3')
print "Query ID 1:", queryId1

queryId2 = subscription.registerquery('SELECT * FROM TEST_TABLE WHERE 
TEST_INT > 3')
print "Query ID 2:", queryId2

print 'Waiting'
try:
     time.sleep(6000)
except KeyboardInterrupt:
     pass


print 'Unsubscribing'
subscription = None

print 'Disconnecting'
connection.close()