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

[cx-oracle-users] Issue with UTF-8 encoding

[Guilherme M. <gui...@gm...>]

Hi,

I am having an encoding issue using Python 2.6.2, cxOracle 5.0.4 to
access an Oracle 11g database.

I am using the NLS_LANG=.AL32UTF8 environment variable. My table in
Oracle is correctly configured to accept Unicode.

I compiled cxOracle without the WITH_UNICODE flag and passed unicode()
objects to cxOracle. Everything worked without exceptions or warnings.
However, sometimes Oracle would complain that the string I was trying
to insert into a VARCHAR2 field was too big (> 4000), even when the
string size ( len(the_string.encode('utf-8')) ) was about 2300 bytes.
I used a sniffer to verify that the Oracle client was sending two
bytes for each character (even the ASCII ones), instead of sending two
bytes only for special characters.

It seemed to me that cx_Oracle accepts unicode() objects but it does
not encode() them to the correct encoding (as set in NLS_LANG
variable) if the WITH_UNICODE flag is unset. Instead, it just sends to
Oracle the internal representation of the unicode() object.

Is this behaviour expected? Am I doing something wrong?

Regards,

Guilherme.

Re: [cx-oracle-users] cx_Oracle build on AIX 5.3 with xlc_R

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

Hi,

>From the error and the location it is complaining about it looks like
the XLC compiler does not understand // as comment characters. That's
not something I'm prepared to "fix". There might be
a setting you can use to make the compiler understand those
characters. Or you can use gcc as you attempted to do. From your log
it looks like only warnings were issued which can often be safely
ignored. I know others have built successfully on AIX so hopefully
they will come out of the woodwork and clue you in. :-) A quick search
of the archives shows a number of people asking questions and some of
the replies might be enlightening to you. Good luck!

Anthony

On Tue, Nov 16, 2010 at 5:13 PM, Anurag Chourasia
<anu...@gm...> wrote:
> Dear cx-Oracle users,
> I am having problems compiling cx-Oracle version 5.0.3 on a AIX Machine. I
> would appreciate if someone could please help me.
> I downloaded the source from http://cx-oracle.sourceforge.net/
> When building using python setup.py build, i get the following error
> ---------------------------------------------------------------------------------------------------------------------------------------
> wm (wmosds) [zibal] - /u01/home/apli/wm/GDD/cx_Oracle-5.0.3 :>python
> setup.py build
> running build
> running build_ext
> building 'cx_Oracle' extension
> xlc_r -ma -I/opt/freeware/include -DAIX_GENUINE_CPLUSCPLUS -Wl,-brtl
> -DNDEBUG -O -I/u01/home/app/oracle/product/10.2/rdbms/demo
> -I/u01/home/app/oracle/product/10.2/rdbms/public
> -I/opt/freeware/include/python2.6 -c cx_Oracle.c -o
> build/temp.aix-5.3-2.6-10g/cx_Oracle.o -DBUILD_VERSION=5.0.3
> "cx_Oracle.c", line 1.1: 1506-046 (S) Syntax error.
> "cx_Oracle.c", line 2.4: 1506-166 (S) Definition of function cx_Oracle
> requires parentheses.
> "/usr/include/stdio.h", line 161.3: 1506-273 (E) Missing type in declaration
> of FILE.
> "/usr/include/stdio.h", line 167.8: 1506-166 (S) Definition of function FILE
> requires parentheses.
> "/usr/include/stdio.h", line 167.17: 1506-276 (S) Syntax error: possible
> missing '{'?
> "/usr/include/sys/inttypes.h", line 59.9: 1506-045 (S) Undeclared identifier
> intmax_t.
> .
> .
> .
> ---------------------------------------------------------------------------------------------------------------------------------------
>
> I have tried using gcc and cc and g++ as the compiler instead but all of
> them give error. Here is the error
> ---------------------------------------------------------------------------------------------------------------------------------------
> wm (wmosds) [zibal] - /u01/home/apli/wm/GDD/cx_Oracle-5.0.3 :>export CC=g++
> wm (wmosds) [zibal] - /u01/home/apli/wm/GDD/cx_Oracle-5.0.3 :>python
> setup.py build
> running build
> running build_ext
> building 'cx_Oracle' extension
> creating build
> creating build/temp.aix-5.3-2.6-10g
> g++ -DNDEBUG -O -I/u01/home/app/oracle/product/10.2/rdbms/demo
> -I/u01/home/app/oracle/product/10.2/rdbms/public
> -I/opt/freeware/include/python2.6 -c cx_Oracle.c -o
> build/temp.aix-5.3-2.6-10g/cx_Oracle.o -DBUILD_VERSION=5.0.3
> In file included from Environment.c:59,
>                  from cx_Oracle.c:166:
> Error.c:34: warning: deprecated conversion from string constant to 'char*'
> Error.c:34: warning: deprecated conversion from string constant to 'char*'
> Error.c:34: warning: deprecated conversion from string constant to 'char*'
> Error.c:34: warning: deprecated conversion from string constant to 'char*'
> In file included from cx_Oracle.c:167:
> SessionPool.c:73: warning: deprecated conversion from string constant to
> 'char*'
> .
> .
> .
> ---------------------------------------------------------------------------------------------------------------------------------------
>
> Here are the machine details.
> root [zibal]% uname -a
> AIX zibal 3 5 000A71F2D700
> root [zibal]% instfix -i |grep ML
>     All filesets for 5.3.0.0_AIX_ML were found.
>     All filesets for 5300-01_AIX_ML were found.
>     All filesets for 5300-02_AIX_ML were found.
>     All filesets for 5300-03_AIX_ML were found.
>     All filesets for 5300-04_AIX_ML were found.
>     All filesets for 5300-05_AIX_ML were found.
> root [zibal]% oslevel -s
> 5300-05-00
> Appreciate your help in guiding me through to a solution.
> Regards,
> Anurag
> ------------------------------------------------------------------------------
> Beautiful is writing same markup. Internet Explorer 9 supports
> standards for HTML5, CSS3, SVG 1.1,  ECMAScript5, and DOM L2 & L3.
> Spend less time writing and  rewriting code and more time creating great
> experiences on the web. Be a part of the beta today
> http://p.sf.net/sfu/msIE9-sfdev2dev
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>

[cx-oracle-users] cx_Oracle build on AIX 5.3 with xlc_R

[Anurag C. <anu...@gm...>]

Dear cx-Oracle users,

I am having problems compiling cx-Oracle version 5.0.3 on a AIX Machine. I
would appreciate if someone could please help me.

I downloaded the source from http://cx-oracle.sourceforge.net/

When building using python setup.py build, i get the following error
---------------------------------------------------------------------------------------------------------------------------------------
wm (wmosds) [zibal] - /u01/home/apli/wm/GDD/cx_Oracle-5.0.3 :>python
setup.py build
running build
running build_ext
building 'cx_Oracle' extension
*xlc_r* -ma -I/opt/freeware/include -DAIX_GENUINE_CPLUSCPLUS -Wl,-brtl
-DNDEBUG -O -I/u01/home/app/oracle/product/10.2/rdbms/demo
-I/u01/home/app/oracle/product/10.2/rdbms/public
-I/opt/freeware/include/python2.6 -c cx_Oracle.c -o
build/temp.aix-5.3-2.6-10g/cx_Oracle.o -DBUILD_VERSION=5.0.3
*"cx_Oracle.c", line 1.1: 1506-046 (S) Syntax error.*
*"cx_Oracle.c", line 2.4: 1506-166 (S) Definition of function cx_Oracle
requires parentheses.*
"/usr/include/stdio.h", line 161.3: 1506-273 (E) Missing type in declaration
of FILE.
"/usr/include/stdio.h", line 167.8: 1506-166 (S) Definition of function FILE
requires parentheses.
"/usr/include/stdio.h", line 167.17: 1506-276 (S) Syntax error: possible
missing '{'?
"/usr/include/sys/inttypes.h", line 59.9: 1506-045 (S) Undeclared identifier
intmax_t.
.
.
.
---------------------------------------------------------------------------------------------------------------------------------------


I have tried using gcc and cc and g++ as the compiler instead but all of
them give error. Here is the error

---------------------------------------------------------------------------------------------------------------------------------------
wm (wmosds) [zibal] - /u01/home/apli/wm/GDD/cx_Oracle-5.0.3 :>*export CC=g++
*
wm (wmosds) [zibal] - /u01/home/apli/wm/GDD/cx_Oracle-5.0.3 :>python
setup.py build
running build
running build_ext
building 'cx_Oracle' extension
creating build
creating build/temp.aix-5.3-2.6-10g
*g++* -DNDEBUG -O -I/u01/home/app/oracle/product/10.2/rdbms/demo
-I/u01/home/app/oracle/product/10.2/rdbms/public
-I/opt/freeware/include/python2.6 -c cx_Oracle.c -o
build/temp.aix-5.3-2.6-10g/cx_Oracle.o -DBUILD_VERSION=5.0.3
I*n file included from Environment.c:59,*
*                 from cx_Oracle.c:166:*
*Error.c:34: warning: deprecated conversion from string constant to 'char*'*
Error.c:34: warning: deprecated conversion from string constant to 'char*'
Error.c:34: warning: deprecated conversion from string constant to 'char*'
Error.c:34: warning: deprecated conversion from string constant to 'char*'
In file included from cx_Oracle.c:167:
SessionPool.c:73: warning: deprecated conversion from string constant to
'char*'
.
.
.

---------------------------------------------------------------------------------------------------------------------------------------


Here are the machine details.

root [zibal]% uname -a
AIX zibal 3 5 000A71F2D700
root [zibal]% instfix -i |grep ML
    All filesets for 5.3.0.0_AIX_ML were found.
    All filesets for 5300-01_AIX_ML were found.
    All filesets for 5300-02_AIX_ML were found.
    All filesets for 5300-03_AIX_ML were found.
    All filesets for 5300-04_AIX_ML were found.
    All filesets for 5300-05_AIX_ML were found.
root [zibal]% oslevel -s
5300-05-00

Appreciate your help in guiding me through to a solution.

Regards,
Anurag

Re: [cx-oracle-users] subscribe in cx_oracle

[Mark <mar...@bl...>]

Anthony Tuininga <anthony.tuininga@...> writes:

> This is actually just a simple problem and you'll probably kick
> yourself when you realize what is going on. 
> 
> In your method you are creating the subscription and then, as soon as
> the function ends, the subscription goes out of scope and is
> immediately destroyed! If you return the subscription from the method
> or you make sub a global by adding "global sub" just before creating
> the subscription all works as expected.

Yes, of course you were quite right. :-)

I thought it was some exotic namespace type of problem/restriction with
cx_oracle (I saw the namespace attribute on one of the cx_oracle object
definitions) ... and it turned out to be a really simple namespace / scoping
issue. Ah well.

I'd made a deliberate effort to just get on with my test using whatever seemed
to work, but once I saw your reply I fixed it to use a more natural structure -
and it looks like it's working nicely now :-)

Thanks again,
Mark.

Re: [cx-oracle-users] subscribe in cx_oracle

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

Hi,

This is actually just a simple problem and you'll probably kick
yourself when you realize what is going on. :-)

In your method you are creating the subscription and then, as soon as
the function ends, the subscription goes out of scope and is
immediately destroyed! If you return the subscription from the method
or you make sub a global by adding "global sub" just before creating
the subscription all works as expected.

Anthony

On Wed, Nov 10, 2010 at 5:45 PM, Mark Bell
<mar...@bl...> wrote:
> I'm testing the subscribe() for change notification, but I'm having some
> problems. If I copy the example really closely I get notifications ok, but if I
> try to put the call to conn.subscribe(...) into a function then it doesn't work,
> I don't get any errors but changes are not reported.
>
> e.g. if I have:
>
> ---------------
> conn = cx_Oracle.connect(user, pass, db, events = True)
> sql = "select * from <some_table>"
>
> def OnChanges(message):
>    print "Message received"
>
> sub = conn.subscribe(callback = OnChanges
>                    ,operations = cx_Oracle.OPCODE_UPDATE
>                    ,rowids = False)
> sub.registerquery(sql)
> sleep(60)
> ---------------
> it works fine.
>
> But if I do this:
>
> ---------------
> conn = cx_Oracle.connect(user, pass, db, events = True)
> sql = "select * from <some_table>"
>
> def OnChanges(message):
>    print "Message received"
>
> def subscribe():
>    sub = conn.subscribe(callback = OnChanges
>                        ,operations = cx_Oracle.OPCODE_UPDATE
>                        ,rowids = False)
>    sub.registerquery(sql)
>
> subscribe()
> sleep(60)
> ---------------
> it doesn't give any errors, but doesn't report any changes. Am I doing something
> wrong?
>
> mark.
>
>
>
> ------------------------------------------------------------------------------
> Centralized Desktop Delivery: Dell and VMware Reference Architecture
> Simplifying enterprise desktop deployment and management using
> Dell EqualLogic storage and VMware View: A highly scalable, end-to-end
> client virtualization framework. Read more!
> http://p.sf.net/sfu/dell-eql-dev2dev
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

Re: [cx-oracle-users] FW: 64bit vs. 32bit?

[W. C. T. <cra...@gm...>]

Christoph ...

As long as you use the same bit-size for the Oracle-client, cx_Oracle,
and Python, you'll be fine.  I've used both 32-bit and 64-bit Python
with cx_Oracle on 64-bit Windows 7 without issue ... as long as I keep
all 3 matched.

- Craig -

On Thu, Nov 11, 2010 at 16:35, Christoph Bussler
<chr...@xt...> wrote:
> Hi,
>
>
>
> Just tried to have everything 64 bit and it works. What helped a lot was
> using Dependency Walker for finding out missing libraries or mismatching
> libraries.
>
>
>
> Thanks,
>
> Christoph
>
>
>
> From: Christoph Bussler [mailto:chr...@xt...]
> Sent: Thursday, November 11, 2010 12:10 PM
> To: cx-...@li...
> Cc: chr...@xt...
> Subject: 64bit vs. 32bit?
>
>
>
> Hi,
>
>
>
> I have Windows 7 on Intel 64bit, and a 11.2.0 Oracle 64 bit Enterprise
> Edition database on a machine.
>
>
>
> Do I have to install 64bit python and 64bit cx_Oracle to access the database
> from python or is it possible to use 32 bit python and 32bit cx_Oracle?
>
> Thanks,
>
> Christoph
>
> ------------------------------------------------------------------------------
> Centralized Desktop Delivery: Dell and VMware Reference Architecture
> Simplifying enterprise desktop deployment and management using
> Dell EqualLogic storage and VMware View: A highly scalable, end-to-end
> client virtualization framework. Read more!
> http://p.sf.net/sfu/dell-eql-dev2dev
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>

[cx-oracle-users] FW: 64bit vs. 32bit?

[Christoph B. <chr...@xt...>]

Hi,

 

Just tried to have everything 64 bit and it works. What helped a lot was
using Dependency Walker for finding out missing libraries or mismatching
libraries.

 

Thanks,


Christoph

 

From: Christoph Bussler [mailto:chr...@xt...] 
Sent: Thursday, November 11, 2010 12:10 PM
To: cx-...@li...
Cc: chr...@xt...
Subject: 64bit vs. 32bit?

 

Hi,

 

I have Windows 7 on Intel 64bit, and a 11.2.0 Oracle 64 bit Enterprise
Edition database on a machine.

 

Do I have to install 64bit python and 64bit cx_Oracle to access the database
from python or is it possible to use 32 bit python and 32bit cx_Oracle?

Thanks,


Christoph

[cx-oracle-users] 64bit vs. 32bit?

[Christoph B. <chr...@xt...>]

Hi,

 

I have Windows 7 on Intel 64bit, and a 11.2.0 Oracle 64 bit Enterprise
Edition database on a machine.

 

Do I have to install 64bit python and 64bit cx_Oracle to access the database
from python or is it possible to use 32 bit python and 32bit cx_Oracle?

Thanks,


Christoph

Re: [cx-oracle-users] subscribe in cx_oracle

[Mark <mar...@bl...>]

Mark Harrison <mh@...> writes:

> Are you on 10g RAC?  If so, it's busted.  The DB should save off the 
address/port
> of your client process, but it instead saves off the address of whatever 
database
> node you've attached to.
 
Nah, don't think so. Clients might be, but we're not.

It works fine if I make the calls at the outside level, but as soon as I try
structuring the code so that I make the calls from inside a function or class
it breaks. I thought I'd read something about not being able to use classes but
I can't find it now, and even just a function seems to break it. I can probably
use it like the examples for now, it just makes the code less neat.

> I was about to go insane talking to oracle support:

:-)


Mark.

Re: [cx-oracle-users] subscribe in cx_oracle

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

On 11/10/10 4:45 PM, Mark Bell wrote:
> I'm testing the subscribe() for change notification, but I'm having some


> it doesn't give any errors, but doesn't report any changes. Am I doing something
> wrong?

Are you on 10g RAC?  If so, it's busted.  The DB should save off the address/port
of your client process, but it instead saves off the address of whatever database
node you've attached to.

I was about to go insane talking to oracle support:

"change notification doesn't work on RAC"
"we dont support python"
"type in the example OCI code from your docs.  It won't work on RAC"
"what application server are you using?"
"none, im typing in the OCI code example."
"is this a java app?"
"no, OCI is for C"
"what application are you running? peoplesoft?"
"no, the example OCI code in your docs"
"in the java"
"no, OCI is for C.  PLEASE type in the example and you will see it bust"
"can you send me your SQL query?"
"no, it's not a query, it's a change notification. look at your docs"

[escalation due to sales intervention]

"change notification doesn't work on RAC"
"we dont support python"
"type in the example OCI code from your docs.  It won't work on RAC"
"what application server are you using?"
"none, im typing in the OCI code example. its in the book but i'll send you a copy"

[days pass]

"it works great on my test system"
"are you on RAC?"
"no"

etc etc etc

There's a patch for 10gR2.

[cx-oracle-users] subscribe in cx_oracle

[Mark B. <mar...@bl...>]

I'm testing the subscribe() for change notification, but I'm having some
problems. If I copy the example really closely I get notifications ok, but if I
try to put the call to conn.subscribe(...) into a function then it doesn't work,
I don't get any errors but changes are not reported.

e.g. if I have:

---------------
conn = cx_Oracle.connect(user, pass, db, events = True)
sql = "select * from <some_table>"

def OnChanges(message): 
    print "Message received" 

sub = conn.subscribe(callback = OnChanges
                    ,operations = cx_Oracle.OPCODE_UPDATE
                    ,rowids = False) 
sub.registerquery(sql)
sleep(60)
---------------
it works fine. 

But if I do this:

---------------
conn = cx_Oracle.connect(user, pass, db, events = True)
sql = "select * from <some_table>"

def OnChanges(message): 
    print "Message received" 

def subscribe(): 
    sub = conn.subscribe(callback = OnChanges
                        ,operations = cx_Oracle.OPCODE_UPDATE
                        ,rowids = False) 
    sub.registerquery(sql)

subscribe()
sleep(60)
---------------
it doesn't give any errors, but doesn't report any changes. Am I doing something
wrong?

mark.

Re: [cx-oracle-users] Thread commits - do other threads get committed? Use Pooling?

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

On Tue, Nov 9, 2010 at 6:20 PM, Chapman, Scott <Sco...@pi...> wrote:
>
>
> -----Original Message-----
> From: Amaury Forgeot d'Arc [mailto:ama...@gm...]
> Sent: Tuesday, November 09, 2010 4:01 PM
> To: cx-...@li...
> Subject: Re: [cx-oracle-users] Thread commits - do other threads get
> committed? Use Pooling?
>
>> > 2010/11/10 Chapman, Scott <Sco...@pi...>:
>> > If I use pooling, do I need to make the Connections threaded=True?
> (I'm
>> > thinking no, because each thread will grab a connection from the
> pool,
>> > begin and commit a transaction and release it's connection back to
> the
>> > pool).
>
>> Yes you need "threaded=True"; this is mostly a signal for the Oracle
>> OCI library to synchronize access to shared resources like memory and
>> other global structures.
>
> You're saying the threaded flag is doing more than the cx_Oracle docs
> say it does, "indicates whether or not Oracle should use the mode
> OCI_THREADED to wrap accesses to connections with a mutex."  This is
> supposed to be used when different threads are using a Connection object
> simultaneously. If you're got a Connection Pool then each thread
> acquires the connection for the duration and so I'm thinking threaded
> does not need to be set.  Are you quite sure of your interpretation of
> the flag?

Look here at the Oracle documentation for OCI_THREADED

http://www.di.unipi.it/~ghelli/didattica/bdldoc/B19306_01/appdev.102/b14250/oci09adv.htm

Basically, thread safety is already maintained internally for each
environment handle by the Oracle libraries (in cx_Oracle an
environment handle is created for each connection) and its only if you
want to make use of the __same__ connection in multiple threads that
you need the threaded = True constructor argument. Hope that makes
sense!

Anthony

Re: [cx-oracle-users] Thread commits - do other threads get committed? Use Pooling?

[Chapman, S. <Sco...@pi...>]

-----Original Message-----
From: Amaury Forgeot d'Arc [mailto:ama...@gm...] 
Sent: Tuesday, November 09, 2010 4:01 PM
To: cx-...@li...
Subject: Re: [cx-oracle-users] Thread commits - do other threads get
committed? Use Pooling?

> > 2010/11/10 Chapman, Scott <Sco...@pi...>:
> > If I use pooling, do I need to make the Connections threaded=True?
(I'm
> > thinking no, because each thread will grab a connection from the
pool, 
> > begin and commit a transaction and release it's connection back to
the 
> > pool).

> Yes you need "threaded=True"; this is mostly a signal for the Oracle
> OCI library to synchronize access to shared resources like memory and 
> other global structures.

You're saying the threaded flag is doing more than the cx_Oracle docs
say it does, "indicates whether or not Oracle should use the mode
OCI_THREADED to wrap accesses to connections with a mutex."  This is
supposed to be used when different threads are using a Connection object
simultaneously. If you're got a Connection Pool then each thread
acquires the connection for the duration and so I'm thinking threaded
does not need to be set.  Are you quite sure of your interpretation of
the flag?

This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you should not disseminate, distribute, alter or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmissions cannot be guaranteed to be secure or without error as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender, therefore, does not accept liability for any errors or omissions in the contents of this message which arise during or as a result of e-mail transmission. If verification is required, please request a hard-copy version. This message is provided for information purposes and should not be construed as a solicitation or offer to buy or sell any securities or related financial instruments in any jurisdiction.

Re: [cx-oracle-users] Thread commits - do other threads get committed? Use Pooling?

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

2010/11/10 Chapman, Scott <Sco...@pi...>:
> If I use pooling, do I need to make the Connections threaded=True? (I'm
> thinking no, because each thread will grab a connection from the pool, begin
> and commit a transaction and release it's connection back to the pool).

Yes you need "threaded=True"; this is mostly a signal for the Oracle
OCI library
to synchronize access to shared resources like memory and other global
structures.

-- 
Amaury Forgeot d'Arc

[cx-oracle-users] Thread commits - do other threads get committed? Use Pooling?

[Chapman, S. <Sco...@pi...>]

I have an application where I want to have multiple threads doing
inserts into the database but I don't want each thread's commit to
commit the other threads.

 

Commit is done on the Connection object so I don't think I can use a
single Connection among multiple threads as a result of this?

 

So connection pooling should be the answer?  

 

If I use pooling, do I need to make the Connections threaded=True? (I'm
thinking no, because each thread will grab a connection from the pool,
begin and commit a transaction and release it's connection back to the
pool).

 

All help appreciated.

 

Thanks,

Scott

 


This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you should not disseminate, distribute, alter or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmissions cannot be guaranteed to be secure or without error as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender, therefore, does not accept liability for any errors or omissions in the contents of this message which arise during or as a result of e-mail transmission. If verification is required, please request a hard-copy version. This message is provided for information purposes and should not be construed as a solicitation or offer to buy or sell any securities or related financial instruments in any jurisdiction.

Re: [cx-oracle-users] Crash of OCIStmtRelease during cursor.close()

[<Gui...@su...>]

> There is an internal method Cursor_FreeHandle() which is called.
> Cursor_Free() does not directly call Cursor_Close() so they are not
> the same thing.

Right, but Cursor_FreeHandle is where my program crashes, during the
execution of OCIStmtRelease, so this will still get called and my
program will probably still crash for the same reason that it does now,
won't it?

But I can't just talk the talk and not walk the walk. As I said, I'll do
the tests today so we'll know for sure. 

By the way, I know it's a long shot but I found this Oracle trace when
the program crashes. If it means anything to anyone. 

_kpedbg_dmp_stack()+231
<-_kpeDbgCrash()+51
<-__VInfreq__kpustmtrelease()+341
<-_OCIStmtRelease()+87
<-_OCIStmtRelease()+49
Before that it's Cursor_FreeHandle() and Cursor_Close() that are called
and lead to OCIStmtRelease.


Thanks,
Guillaume

Re: [cx-oracle-users] Crash of OCIStmtRelease during cursor.close()

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

On Thu, Nov 4, 2010 at 11:53 AM,  <Gui...@su...> wrote:
> Ok, that seems kind of consistent with what I meant to try out.
> After you told me not to call close() explicitly, I commented the line out, but just to make sure the destructor was in fact being called I used the C/Python function Py_CLEAR(cursor), which decrements the number of handles on the cursor AND sets it to NULL afterwards.
>
> But tell me this: when the cursor actually goes out of scope and its automatically closed, won't it be cursor_close() which will be called when cursor is destroyed? If not, what will?

There is an internal method Cursor_FreeHandle() which is called.
Cursor_Free() does not directly call Cursor_Close() so they are not
the same thing.

> I meant to test this today but couldn't so I will tomorrow. You'll be first to know the outcome.

Thanks.

> Thank you very much,

You're welcome.

Anthony

Re: [cx-oracle-users] Crash of OCIStmtRelease during cursor.close()

[<Gui...@su...>]

Ok, that seems kind of consistent with what I meant to try out.
After you told me not to call close() explicitly, I commented the line out, but just to make sure the destructor was in fact being called I used the C/Python function Py_CLEAR(cursor), which decrements the number of handles on the cursor AND sets it to NULL afterwards. 

But tell me this: when the cursor actually goes out of scope and its automatically closed, won't it be cursor_close() which will be called when cursor is destroyed? If not, what will?

I meant to test this today but couldn't so I will tomorrow. You'll be first to know the outcome.

Thank you very much,

Guillaume



-----Message d'origine-----
De : Anthony Tuininga [mailto:ant...@gm...] 
Envoyé : jeudi 4 novembre 2010 17:52
À : cx-...@li...
Objet : Re: [cx-oracle-users] Crash of OCIStmtRelease during cursor.close()

Hi,

The idea I had was to null out the cursor handle after being freed so
that a second attempt to free it would not take place when
cursor.close() was being called. I later discovered that this was in
fact already taking place but not in a consistent fashion. So I have
checked in changes to deal with this (revisions 342 and 343) but I
strongly suspect that they won't have any bearing on your situation.
Of course to avoid the situation entirely just don't call close(). :-)
So if you take this route and the problem goes away that is highly
suggestive of the source of the problem.

Hope that answers your questions.

Anthony

On Thu, Nov 4, 2010 at 8:15 AM,  <Gui...@su...> wrote:
> Hi,
>
> Antony, I'm still open to trying that patch you had in mind yesterday. I'm gonna make a few tests at the client's this afternoon.
> Care to share your idea?
> I'm also going to try not explicitly closing the cursor and letting it close itself when garbage collected, see what happens.
>
> Thanks,
> Guillaume
>
>
> -----Message d'origine-----
> De : Aouizerate, Guillaume
> Envoyé : mercredi 3 novembre 2010 18:11
> À : 'cx-...@li...'
> Objet : RE: [cx-oracle-users] Crash of OCIStmtRelease during cursor.close()
>
> Hi
>
> I completely agree with the uselessness of calling executemanyprepared instead of just execute. I might just change this.
>
> I also agree on trying not to call close() after all. I was sceptical at first because our processes are actually using cxOracle from C code using the C/Python API and I wasn't sure cursors ever got out of scope but I just tried it and there's no regression, no ORA-01000.
> I'd have to test this on a client's configuration though because our problem is the kind of sick ones that only clients are able to reproduce in production but not us :-)
> But if the cursor is closed automatically, I'm wondering if we won't still get the same error but in the cursor's destructor now.
>
> Also, yes I can build my own cxOracle. This is actually what we're already doing. So any change you might want to test, I can implement in our build. I'll just have to patch a client and have them do the tests before confirming (or not) that the solution works. But this could probably be done pretty quickly.
>
> Thanks again for taking the time.
>
> Guillaume
>
>
> PS:
> Also if you're wondering why we're using cxOracle from C code, we're actually using it in a normal way from Python in our product's screens (written in Python). But the processes that actually do complex things with the data are written in C. So needing cx_Oracle for the python part, we decided (a long long time ago) to also use it in C. Ex:
> PyObject* pycrs=PyObject_CallMethod(*conn,"cursor",NULL);
> PyObject_CallMethod(pycrs,"execute","O",Py_None);
> PyObject_CallMethod(pycrs,"close",NONE);
>
>
>
>
>
>
> -----Message d'origine-----
> De : Anthony Tuininga [mailto:ant...@gm...]
> Envoyé : mercredi 3 novembre 2010 17:14
> À : cx-...@li...
> Objet : Re: [cx-oracle-users] Crash of OCIStmtRelease during cursor.close()
>
> On Wed, Nov 3, 2010 at 9:27 AM,  <Gui...@su...> wrote:
>> Hi, first off, thanks for your answer.
>
> You're welcome.
>
>>> 1) The latest version of cx_Oracle is now 5.0.4 which may or may not
>>> have resolved this problem. I've never noticed it myself so wouldn't
>>> be able to say.
>>
>> The reason we decided to use cxOracle 4.4 is because someone working with us two years ago already ported our product to 4.4 in one of our dev branches. Since 4.4 supports Oracle 11 and being in need of a quick solution, we decided to use that version.

>> It might be worth looking into 5.0.4 but there will certainly be some regressions with our software, as with 4.4 at first, which could take time to solve with no assurance of seeing our original problem solved.
>
> Ok.
>
>>> 2) Not sure why you wouldn't simply call cursor.execute(stmt) over
>>> cursor.prepare() and cursor.executemanyprepared(). Those last two are
>>> only needed for specialized situations and your code doesn't appear to
>>> require special processing.
>>
>> We call prepare() and then executemanyprepared() because calling prepare() allows us to check that cursor.statementType is not OCI_STMT_SELECT and to throw an exception if it is.
>
> Just reacting to the code you were showing me. Realize that if you try
> to fetch from a non-statement you will get a "not a query" exception
> anyway so you could catch that. I also don't see any reason in the
> code you demonstrated for executemanyprepared(). If that isn't
> acceptable you can also retrieve cursor.description which will be None
> in the case of a non-query. You can also quite easily call execute()
> even after prepare(). The code behaves exactly as if you attempted to
> execute a statement multiple times. The prepare phase is skipped.
>
> All of these are just suggestions. Feel free to ignore any or all of them. :-)
>
>>> 3) There is no need to call cursor.close() so you can try leaving that
>>> out as well.
>>
>> Ok, but won't I get an "ORA-01000: maximum open cursors exceeded" error if I don't close it? Unless you're suggesting that we use the same cursor for the execution of all statements.
>
> Once the cursor goes out of scope, it will be closed automatically. So
> you won't get ORA-1000 errors unless you never allow the cursor to go
> out of scope for some reason -- probably not a good thing anyway. :-)
>
>> If this is what you mean, the thing is we have several hundred processes in our software that our actually much much more complicated than the loop I wrote in my first email, and more and more clients are getting this problem but never on the same processes. Changing all of them is not really an option as this would take weeks of development and extensive testing.
>
> Of course. I wouldn't suggest that as a permanent solution -- just a
> means of determining the source of the problem.
>
>> I'd much rather know why cursor_close() is crashing than stop using it.
>
> Naturally. So would I. :-) I almost never use cursor.close() and I
> think most other people don't use it either so if there is a problem
> with cursor.close() it doesn't show up often. After reviewing the code
> a little more carefully I think I may have come up with a possible
> cause, however. Are you able to build cx_Oracle yourself and would you
> be willing to attempt a particular patch and test it in your
> environment? The only other options are for me to build a binary and
> send it to you for testing or for you to provide me a test case that
> demonstrates the problem. I'd prefer the latter given a choice, of
> course.
>
>>> 4) Do you have multiple threads in your application that are accessing
>>> Oracle? Database change notification? Either of those require threaded
>>> = True as part of the constructor of the connection object.
>>
>> Yes, our application is indeed multithreaded but we are already opening a threaded=True connection.
>
> Good.
>
> Anthony
>
>
>
> ------------------------------------------------------------------------------
> The Next 800 Companies to Lead America's Growth: New Video Whitepaper
> David G. Thomson, author of the best-selling book "Blueprint to a
> Billion" shares his insights and actions to help propel your
> business during the next growth cycle. Listen Now!
> http://p.sf.net/sfu/SAP-dev2dev
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

------------------------------------------------------------------------------
The Next 800 Companies to Lead America's Growth: New Video Whitepaper
David G. Thomson, author of the best-selling book "Blueprint to a 
Billion" shares his insights and actions to help propel your 
business during the next growth cycle. Listen Now!
http://p.sf.net/sfu/SAP-dev2dev
_______________________________________________
cx-oracle-users mailing list
cx-...@li...
https://lists.sourceforge.net/lists/listinfo/cx-oracle-users

Re: [cx-oracle-users] Crash of OCIStmtRelease during cursor.close()

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

Hi,

The idea I had was to null out the cursor handle after being freed so
that a second attempt to free it would not take place when
cursor.close() was being called. I later discovered that this was in
fact already taking place but not in a consistent fashion. So I have
checked in changes to deal with this (revisions 342 and 343) but I
strongly suspect that they won't have any bearing on your situation.
Of course to avoid the situation entirely just don't call close(). :-)
So if you take this route and the problem goes away that is highly
suggestive of the source of the problem.

Hope that answers your questions.

Anthony

On Thu, Nov 4, 2010 at 8:15 AM,  <Gui...@su...> wrote:
> Hi,
>
> Antony, I'm still open to trying that patch you had in mind yesterday. I'm gonna make a few tests at the client's this afternoon.
> Care to share your idea?
> I'm also going to try not explicitly closing the cursor and letting it close itself when garbage collected, see what happens.
>
> Thanks,
> Guillaume
>
>
> -----Message d'origine-----
> De : Aouizerate, Guillaume
> Envoyé : mercredi 3 novembre 2010 18:11
> À : 'cx-...@li...'
> Objet : RE: [cx-oracle-users] Crash of OCIStmtRelease during cursor.close()
>
> Hi
>
> I completely agree with the uselessness of calling executemanyprepared instead of just execute. I might just change this.
>
> I also agree on trying not to call close() after all. I was sceptical at first because our processes are actually using cxOracle from C code using the C/Python API and I wasn't sure cursors ever got out of scope but I just tried it and there's no regression, no ORA-01000.
> I'd have to test this on a client's configuration though because our problem is the kind of sick ones that only clients are able to reproduce in production but not us :-)
> But if the cursor is closed automatically, I'm wondering if we won't still get the same error but in the cursor's destructor now.
>
> Also, yes I can build my own cxOracle. This is actually what we're already doing. So any change you might want to test, I can implement in our build. I'll just have to patch a client and have them do the tests before confirming (or not) that the solution works. But this could probably be done pretty quickly.
>
> Thanks again for taking the time.
>
> Guillaume
>
>
> PS:
> Also if you're wondering why we're using cxOracle from C code, we're actually using it in a normal way from Python in our product's screens (written in Python). But the processes that actually do complex things with the data are written in C. So needing cx_Oracle for the python part, we decided (a long long time ago) to also use it in C. Ex:
> PyObject* pycrs=PyObject_CallMethod(*conn,"cursor",NULL);
> PyObject_CallMethod(pycrs,"execute","O",Py_None);
> PyObject_CallMethod(pycrs,"close",NONE);
>
>
>
>
>
>
> -----Message d'origine-----
> De : Anthony Tuininga [mailto:ant...@gm...]
> Envoyé : mercredi 3 novembre 2010 17:14
> À : cx-...@li...
> Objet : Re: [cx-oracle-users] Crash of OCIStmtRelease during cursor.close()
>
> On Wed, Nov 3, 2010 at 9:27 AM,  <Gui...@su...> wrote:
>> Hi, first off, thanks for your answer.
>
> You're welcome.
>
>>> 1) The latest version of cx_Oracle is now 5.0.4 which may or may not
>>> have resolved this problem. I've never noticed it myself so wouldn't
>>> be able to say.
>>
>> The reason we decided to use cxOracle 4.4 is because someone working with us two years ago already ported our product to 4.4 in one of our dev branches. Since 4.4 supports Oracle 11 and being in need of a quick solution, we decided to use that version.
>> It might be worth looking into 5.0.4 but there will certainly be some regressions with our software, as with 4.4 at first, which could take time to solve with no assurance of seeing our original problem solved.
>
> Ok.
>
>>> 2) Not sure why you wouldn't simply call cursor.execute(stmt) over
>>> cursor.prepare() and cursor.executemanyprepared(). Those last two are
>>> only needed for specialized situations and your code doesn't appear to
>>> require special processing.
>>
>> We call prepare() and then executemanyprepared() because calling prepare() allows us to check that cursor.statementType is not OCI_STMT_SELECT and to throw an exception if it is.
>
> Just reacting to the code you were showing me. Realize that if you try
> to fetch from a non-statement you will get a "not a query" exception
> anyway so you could catch that. I also don't see any reason in the
> code you demonstrated for executemanyprepared(). If that isn't
> acceptable you can also retrieve cursor.description which will be None
> in the case of a non-query. You can also quite easily call execute()
> even after prepare(). The code behaves exactly as if you attempted to
> execute a statement multiple times. The prepare phase is skipped.
>
> All of these are just suggestions. Feel free to ignore any or all of them. :-)
>
>>> 3) There is no need to call cursor.close() so you can try leaving that
>>> out as well.
>>
>> Ok, but won't I get an "ORA-01000: maximum open cursors exceeded" error if I don't close it? Unless you're suggesting that we use the same cursor for the execution of all statements.
>
> Once the cursor goes out of scope, it will be closed automatically. So
> you won't get ORA-1000 errors unless you never allow the cursor to go
> out of scope for some reason -- probably not a good thing anyway. :-)
>
>> If this is what you mean, the thing is we have several hundred processes in our software that our actually much much more complicated than the loop I wrote in my first email, and more and more clients are getting this problem but never on the same processes. Changing all of them is not really an option as this would take weeks of development and extensive testing.
>
> Of course. I wouldn't suggest that as a permanent solution -- just a
> means of determining the source of the problem.
>
>> I'd much rather know why cursor_close() is crashing than stop using it.
>
> Naturally. So would I. :-) I almost never use cursor.close() and I
> think most other people don't use it either so if there is a problem
> with cursor.close() it doesn't show up often. After reviewing the code
> a little more carefully I think I may have come up with a possible
> cause, however. Are you able to build cx_Oracle yourself and would you
> be willing to attempt a particular patch and test it in your
> environment? The only other options are for me to build a binary and
> send it to you for testing or for you to provide me a test case that
> demonstrates the problem. I'd prefer the latter given a choice, of
> course.
>
>>> 4) Do you have multiple threads in your application that are accessing
>>> Oracle? Database change notification? Either of those require threaded
>>> = True as part of the constructor of the connection object.
>>
>> Yes, our application is indeed multithreaded but we are already opening a threaded=True connection.
>
> Good.
>
> Anthony
>
>
>
> ------------------------------------------------------------------------------
> The Next 800 Companies to Lead America's Growth: New Video Whitepaper
> David G. Thomson, author of the best-selling book "Blueprint to a
> Billion" shares his insights and actions to help propel your
> business during the next growth cycle. Listen Now!
> http://p.sf.net/sfu/SAP-dev2dev
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

Re: [cx-oracle-users] Crash of OCIStmtRelease during cursor.close()

[<Gui...@su...>]

Hi,

Antony, I'm still open to trying that patch you had in mind yesterday. I'm gonna make a few tests at the client's this afternoon.
Care to share your idea?
I'm also going to try not explicitly closing the cursor and letting it close itself when garbage collected, see what happens.

Thanks,
Guillaume


-----Message d'origine-----
De : Aouizerate, Guillaume 
Envoyé : mercredi 3 novembre 2010 18:11
À : 'cx-...@li...'
Objet : RE: [cx-oracle-users] Crash of OCIStmtRelease during cursor.close()

Hi

I completely agree with the uselessness of calling executemanyprepared instead of just execute. I might just change this.

I also agree on trying not to call close() after all. I was sceptical at first because our processes are actually using cxOracle from C code using the C/Python API and I wasn't sure cursors ever got out of scope but I just tried it and there's no regression, no ORA-01000. 
I'd have to test this on a client's configuration though because our problem is the kind of sick ones that only clients are able to reproduce in production but not us :-)
But if the cursor is closed automatically, I'm wondering if we won't still get the same error but in the cursor's destructor now.

Also, yes I can build my own cxOracle. This is actually what we're already doing. So any change you might want to test, I can implement in our build. I'll just have to patch a client and have them do the tests before confirming (or not) that the solution works. But this could probably be done pretty quickly. 

Thanks again for taking the time.

Guillaume


PS:
Also if you're wondering why we're using cxOracle from C code, we're actually using it in a normal way from Python in our product's screens (written in Python). But the processes that actually do complex things with the data are written in C. So needing cx_Oracle for the python part, we decided (a long long time ago) to also use it in C. Ex:
PyObject* pycrs=PyObject_CallMethod(*conn,"cursor",NULL);
PyObject_CallMethod(pycrs,"execute","O",Py_None);
PyObject_CallMethod(pycrs,"close",NONE);






-----Message d'origine-----
De : Anthony Tuininga [mailto:ant...@gm...] 
Envoyé : mercredi 3 novembre 2010 17:14
À : cx-...@li...
Objet : Re: [cx-oracle-users] Crash of OCIStmtRelease during cursor.close()

On Wed, Nov 3, 2010 at 9:27 AM,  <Gui...@su...> wrote:
> Hi, first off, thanks for your answer.

You're welcome.

>> 1) The latest version of cx_Oracle is now 5.0.4 which may or may not
>> have resolved this problem. I've never noticed it myself so wouldn't
>> be able to say.
>
> The reason we decided to use cxOracle 4.4 is because someone working with us two years ago already ported our product to 4.4 in one of our dev branches. Since 4.4 supports Oracle 11 and being in need of a quick solution, we decided to use that version.
> It might be worth looking into 5.0.4 but there will certainly be some regressions with our software, as with 4.4 at first, which could take time to solve with no assurance of seeing our original problem solved.

Ok.

>> 2) Not sure why you wouldn't simply call cursor.execute(stmt) over
>> cursor.prepare() and cursor.executemanyprepared(). Those last two are
>> only needed for specialized situations and your code doesn't appear to
>> require special processing.
>
> We call prepare() and then executemanyprepared() because calling prepare() allows us to check that cursor.statementType is not OCI_STMT_SELECT and to throw an exception if it is.

Just reacting to the code you were showing me. Realize that if you try
to fetch from a non-statement you will get a "not a query" exception
anyway so you could catch that. I also don't see any reason in the
code you demonstrated for executemanyprepared(). If that isn't
acceptable you can also retrieve cursor.description which will be None
in the case of a non-query. You can also quite easily call execute()
even after prepare(). The code behaves exactly as if you attempted to
execute a statement multiple times. The prepare phase is skipped.

All of these are just suggestions. Feel free to ignore any or all of them. :-)

>> 3) There is no need to call cursor.close() so you can try leaving that
>> out as well.
>
> Ok, but won't I get an "ORA-01000: maximum open cursors exceeded" error if I don't close it? Unless you're suggesting that we use the same cursor for the execution of all statements.

Once the cursor goes out of scope, it will be closed automatically. So
you won't get ORA-1000 errors unless you never allow the cursor to go
out of scope for some reason -- probably not a good thing anyway. :-)

> If this is what you mean, the thing is we have several hundred processes in our software that our actually much much more complicated than the loop I wrote in my first email, and more and more clients are getting this problem but never on the same processes. Changing all of them is not really an option as this would take weeks of development and extensive testing.

Of course. I wouldn't suggest that as a permanent solution -- just a
means of determining the source of the problem.

> I'd much rather know why cursor_close() is crashing than stop using it.

Naturally. So would I. :-) I almost never use cursor.close() and I
think most other people don't use it either so if there is a problem
with cursor.close() it doesn't show up often. After reviewing the code
a little more carefully I think I may have come up with a possible
cause, however. Are you able to build cx_Oracle yourself and would you
be willing to attempt a particular patch and test it in your
environment? The only other options are for me to build a binary and
send it to you for testing or for you to provide me a test case that
demonstrates the problem. I'd prefer the latter given a choice, of
course.

>> 4) Do you have multiple threads in your application that are accessing
>> Oracle? Database change notification? Either of those require threaded
>> = True as part of the constructor of the connection object.
>
> Yes, our application is indeed multithreaded but we are already opening a threaded=True connection.

Good.

Anthony

Re: [cx-oracle-users] Crash of OCIStmtRelease during cursor.close()

[<Gui...@su...>]

Hi

I completely agree with the uselessness of calling executemanyprepared instead of just execute. I might just change this.

I also agree on trying not to call close() after all. I was sceptical at first because our processes are actually using cxOracle from C code using the C/Python API and I wasn't sure cursors ever got out of scope but I just tried it and there's no regression, no ORA-01000. 
I'd have to test this on a client's configuration though because our problem is the kind of sick ones that only clients are able to reproduce in production but not us :-)
But if the cursor is closed automatically, I'm wondering if we won't still get the same error but in the cursor's destructor now.

Also, yes I can build my own cxOracle. This is actually what we're already doing. So any change you might want to test, I can implement in our build. I'll just have to patch a client and have them do the tests before confirming (or not) that the solution works. But this could probably be done pretty quickly. 

Thanks again for taking the time.

Guillaume


PS:
Also if you're wondering why we're using cxOracle from C code, we're actually using it in a normal way from Python in our product's screens (written in Python). But the processes that actually do complex things with the data are written in C. So needing cx_Oracle for the python part, we decided (a long long time ago) to also use it in C. Ex:
PyObject* pycrs=PyObject_CallMethod(*conn,"cursor",NULL);
PyObject_CallMethod(pycrs,"execute","O",Py_None);
PyObject_CallMethod(pycrs,"close",NONE);






-----Message d'origine-----
De : Anthony Tuininga [mailto:ant...@gm...] 
Envoyé : mercredi 3 novembre 2010 17:14
À : cx-...@li...
Objet : Re: [cx-oracle-users] Crash of OCIStmtRelease during cursor.close()

On Wed, Nov 3, 2010 at 9:27 AM,  <Gui...@su...> wrote:
> Hi, first off, thanks for your answer.

You're welcome.

>> 1) The latest version of cx_Oracle is now 5.0.4 which may or may not
>> have resolved this problem. I've never noticed it myself so wouldn't
>> be able to say.
>
> The reason we decided to use cxOracle 4.4 is because someone working with us two years ago already ported our product to 4.4 in one of our dev branches. Since 4.4 supports Oracle 11 and being in need of a quick solution, we decided to use that version.
> It might be worth looking into 5.0.4 but there will certainly be some regressions with our software, as with 4.4 at first, which could take time to solve with no assurance of seeing our original problem solved.

Ok.

>> 2) Not sure why you wouldn't simply call cursor.execute(stmt) over
>> cursor.prepare() and cursor.executemanyprepared(). Those last two are
>> only needed for specialized situations and your code doesn't appear to
>> require special processing.
>
> We call prepare() and then executemanyprepared() because calling prepare() allows us to check that cursor.statementType is not OCI_STMT_SELECT and to throw an exception if it is.

Just reacting to the code you were showing me. Realize that if you try
to fetch from a non-statement you will get a "not a query" exception
anyway so you could catch that. I also don't see any reason in the
code you demonstrated for executemanyprepared(). If that isn't
acceptable you can also retrieve cursor.description which will be None
in the case of a non-query. You can also quite easily call execute()
even after prepare(). The code behaves exactly as if you attempted to
execute a statement multiple times. The prepare phase is skipped.

All of these are just suggestions. Feel free to ignore any or all of them. :-)

>> 3) There is no need to call cursor.close() so you can try leaving that
>> out as well.
>
> Ok, but won't I get an "ORA-01000: maximum open cursors exceeded" error if I don't close it? Unless you're suggesting that we use the same cursor for the execution of all statements.

Once the cursor goes out of scope, it will be closed automatically. So
you won't get ORA-1000 errors unless you never allow the cursor to go
out of scope for some reason -- probably not a good thing anyway. :-)

> If this is what you mean, the thing is we have several hundred processes in our software that our actually much much more complicated than the loop I wrote in my first email, and more and more clients are getting this problem but never on the same processes. Changing all of them is not really an option as this would take weeks of development and extensive testing.

Of course. I wouldn't suggest that as a permanent solution -- just a
means of determining the source of the problem.

> I'd much rather know why cursor_close() is crashing than stop using it.

Naturally. So would I. :-) I almost never use cursor.close() and I
think most other people don't use it either so if there is a problem
with cursor.close() it doesn't show up often. After reviewing the code
a little more carefully I think I may have come up with a possible
cause, however. Are you able to build cx_Oracle yourself and would you
be willing to attempt a particular patch and test it in your
environment? The only other options are for me to build a binary and
send it to you for testing or for you to provide me a test case that
demonstrates the problem. I'd prefer the latter given a choice, of
course.

>> 4) Do you have multiple threads in your application that are accessing
>> Oracle? Database change notification? Either of those require threaded
>> = True as part of the constructor of the connection object.
>
> Yes, our application is indeed multithreaded but we are already opening a threaded=True connection.

Good.

Anthony

Re: [cx-oracle-users] Crash of OCIStmtRelease during cursor.close()

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

On Wed, Nov 3, 2010 at 9:27 AM,  <Gui...@su...> wrote:
> Hi, first off, thanks for your answer.

You're welcome.

>> 1) The latest version of cx_Oracle is now 5.0.4 which may or may not
>> have resolved this problem. I've never noticed it myself so wouldn't
>> be able to say.
>
> The reason we decided to use cxOracle 4.4 is because someone working with us two years ago already ported our product to 4.4 in one of our dev branches. Since 4.4 supports Oracle 11 and being in need of a quick solution, we decided to use that version.
> It might be worth looking into 5.0.4 but there will certainly be some regressions with our software, as with 4.4 at first, which could take time to solve with no assurance of seeing our original problem solved.

Ok.

>> 2) Not sure why you wouldn't simply call cursor.execute(stmt) over
>> cursor.prepare() and cursor.executemanyprepared(). Those last two are
>> only needed for specialized situations and your code doesn't appear to
>> require special processing.
>
> We call prepare() and then executemanyprepared() because calling prepare() allows us to check that cursor.statementType is not OCI_STMT_SELECT and to throw an exception if it is.

Just reacting to the code you were showing me. Realize that if you try
to fetch from a non-statement you will get a "not a query" exception
anyway so you could catch that. I also don't see any reason in the
code you demonstrated for executemanyprepared(). If that isn't
acceptable you can also retrieve cursor.description which will be None
in the case of a non-query. You can also quite easily call execute()
even after prepare(). The code behaves exactly as if you attempted to
execute a statement multiple times. The prepare phase is skipped.

All of these are just suggestions. Feel free to ignore any or all of them. :-)

>> 3) There is no need to call cursor.close() so you can try leaving that
>> out as well.
>
> Ok, but won't I get an "ORA-01000: maximum open cursors exceeded" error if I don't close it? Unless you're suggesting that we use the same cursor for the execution of all statements.

Once the cursor goes out of scope, it will be closed automatically. So
you won't get ORA-1000 errors unless you never allow the cursor to go
out of scope for some reason -- probably not a good thing anyway. :-)

> If this is what you mean, the thing is we have several hundred processes in our software that our actually much much more complicated than the loop I wrote in my first email, and more and more clients are getting this problem but never on the same processes. Changing all of them is not really an option as this would take weeks of development and extensive testing.

Of course. I wouldn't suggest that as a permanent solution -- just a
means of determining the source of the problem.

> I'd much rather know why cursor_close() is crashing than stop using it.

Naturally. So would I. :-) I almost never use cursor.close() and I
think most other people don't use it either so if there is a problem
with cursor.close() it doesn't show up often. After reviewing the code
a little more carefully I think I may have come up with a possible
cause, however. Are you able to build cx_Oracle yourself and would you
be willing to attempt a particular patch and test it in your
environment? The only other options are for me to build a binary and
send it to you for testing or for you to provide me a test case that
demonstrates the problem. I'd prefer the latter given a choice, of
course.

>> 4) Do you have multiple threads in your application that are accessing
>> Oracle? Database change notification? Either of those require threaded
>> = True as part of the constructor of the connection object.
>
> Yes, our application is indeed multithreaded but we are already opening a threaded=True connection.

Good.

Anthony

Re: [cx-oracle-users] Crash of OCIStmtRelease during cursor.close()

[<Gui...@su...>]

Hi, first off, thanks for your answer.


> 1) The latest version of cx_Oracle is now 5.0.4 which may or may not
> have resolved this problem. I've never noticed it myself so wouldn't
> be able to say.

The reason we decided to use cxOracle 4.4 is because someone working with us two years ago already ported our product to 4.4 in one of our dev branches. Since 4.4 supports Oracle 11 and being in need of a quick solution, we decided to use that version.
It might be worth looking into 5.0.4 but there will certainly be some regressions with our software, as with 4.4 at first, which could take time to solve with no assurance of seeing our original problem solved.


> 2) Not sure why you wouldn't simply call cursor.execute(stmt) over
> cursor.prepare() and cursor.executemanyprepared(). Those last two are
> only needed for specialized situations and your code doesn't appear to
> require special processing.

We call prepare() and then executemanyprepared() because calling prepare() allows us to check that cursor.statementType is not OCI_STMT_SELECT and to throw an exception if it is.


> 3) There is no need to call cursor.close() so you can try leaving that
> out as well.

Ok, but won't I get an "ORA-01000: maximum open cursors exceeded" error if I don't close it? Unless you're suggesting that we use the same cursor for the execution of all statements. 

If this is what you mean, the thing is we have several hundred processes in our software that our actually much much more complicated than the loop I wrote in my first email, and more and more clients are getting this problem but never on the same processes. Changing all of them is not really an option as this would take weeks of development and extensive testing. 

I'd much rather know why cursor_close() is crashing than stop using it.


> 4) Do you have multiple threads in your application that are accessing
> Oracle? Database change notification? Either of those require threaded
> = True as part of the constructor of the connection object.

Yes, our application is indeed multithreaded but we are already opening a threaded=True connection.



Thanks for your help,
Guillaume


-----Message d'origine-----
De : Anthony Tuininga [mailto:ant...@gm...] 
Envoyé : mercredi 3 novembre 2010 14:50
À : cx-...@li...
Objet : Re: [cx-oracle-users] Crash of OCIStmtRelease during cursor.close()

Hi, just a few observations/suggestions

1) The latest version of cx_Oracle is now 5.0.4 which may or may not
have resolved this problem. I've never noticed it myself so wouldn't
be able to say.

2) Not sure why you wouldn't simply call cursor.execute(stmt) over
cursor.prepare() and cursor.executemanyprepared(). Those last two are
only needed for specialized situations and your code doesn't appear to
require special processing.

3) There is no need to call cursor.close() so you can try leaving that
out as well.

4) Do you have multiple threads in your application that are accessing
Oracle? Database change notification? Either of those require threaded
= True as part of the constructor of the connection object.

Hopefully one of those resolves your issue.

Anthony

On Wed, Nov 3, 2010 at 5:09 AM,  <Gui...@su...> wrote:
> Hello,
>
>
>
> I am using cxOracle 4.4 on a Windows XP 32 bits platform with an Oracle 11.2
> client and a distant Oracle 11.2 server.
>
>
>
> The problem I am getting since we migrated our software to Oracle 11 is that
> during one of our long running processes, the software will sometimes
> suddenly crash with no exception raised.
>
> Our process calls a PL/SQL package hundreds of thousands of times and
> sometimes crashes after one of those calls has ended, in the middle of
> cursor.close().
>
> I determined that inside Cursor_Close(...) it is Cursor_FreeHandle(...) and then
> the call to OCIStmtRelease that crashes.
>
>
>
> Here's a simplified view of what we do:
>
>
>
> for(~200 000 times){
>
> crs = Cursor()
>
> crs.prepare(stmt) # stmt is a call to a PL/SQL package
>
> crs.executemanyprepared(1)
>
> crs.close() => sometimes crashes in Cursor_FreeHandle during call to
> OCIStmtRelease, never in the same iteration of the loop
>
> }
>
>
>
> The thing is that this crash does not happen systematically but rather in
> about one out of two executions of our process.
>
>
>
> We previously used cxOracle 4.1.1 but moved to 4.4 when noticing this
> problem with Oracle 11. But this 4.4 version doesn't seem to be solving the
> problem, unless we need to make more changes to the way we use cxOracle
> after migrating to 4.4.
>
>
>
> Does anybody have a clue as to why OCIStmtRelease would sometimes
> unexpectedly crash like that without throwing any exception?
>
>
>
> Thanks a lot for the help you can give me. I've been working for more than a
> month on this problem and it's becoming critical for our clients that a
> solution be found.
>
>
>
> Guillaume Aouizerate

Re: [cx-oracle-users] Crash of OCIStmtRelease during cursor.close()

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

Hi, just a few observations/suggestions

1) The latest version of cx_Oracle is now 5.0.4 which may or may not
have resolved this problem. I've never noticed it myself so wouldn't
be able to say.

2) Not sure why you wouldn't simply call cursor.execute(stmt) over
cursor.prepare() and cursor.executemanyprepared(). Those last two are
only needed for specialized situations and your code doesn't appear to
require special processing.

3) There is no need to call cursor.close() so you can try leaving that
out as well.

4) Do you have multiple threads in your application that are accessing
Oracle? Database change notification? Either of those require threaded
= True as part of the constructor of the connection object.

Hopefully one of those resolves your issue.

Anthony

On Wed, Nov 3, 2010 at 5:09 AM,  <Gui...@su...> wrote:
> Hello,
>
>
>
> I am using cxOracle 4.4 on a Windows XP 32 bits platform with an Oracle 11.2
> client and a distant Oracle 11.2 server.
>
>
>
> The problem I am getting since we migrated our software to Oracle 11 is that
> during one of our long running processes, the software will sometimes
> suddenly crash with no exception raised.
>
> Our process calls a PL/SQL package hundreds of thousands of times and
> sometimes crashes after one of those calls has ended, in the middle of
> cursor.close().
>
> I determined that inside Cursor_Close(…) it is Cursor_FreeHandle(…) and then
> the call to OCIStmtRelease that crashes.
>
>
>
> Here’s a simplified view of what we do:
>
>
>
> for(~200 000 times){
>
> crs = Cursor()
>
> crs.prepare(stmt) # stmt is a call to a PL/SQL package
>
> crs.executemanyprepared(1)
>
> crs.close() => sometimes crashes in Cursor_FreeHandle during call to
> OCIStmtRelease, never in the same iteration of the loop
>
> }
>
>
>
> The thing is that this crash does not happen systematically but rather in
> about one out of two executions of our process.
>
>
>
> We previously used cxOracle 4.1.1 but moved to 4.4 when noticing this
> problem with Oracle 11. But this 4.4 version doesn’t seem to be solving the
> problem, unless we need to make more changes to the way we use cxOracle
> after migrating to 4.4.
>
>
>
> Does anybody have a clue as to why OCIStmtRelease would sometimes
> unexpectedly crash like that without throwing any exception?
>
>
>
> Thanks a lot for the help you can give me. I’ve been working for more than a
> month on this problem and it’s becoming critical for our clients that a
> solution be found.
>
>
>
> Guillaume Aouizerate
>
>
>
>
>
> ------------------------------------------------------------------------------
> Achieve Improved Network Security with IP and DNS Reputation.
> Defend against bad network traffic, including botnets, malware,
> phishing sites, and compromised hosts - saving your company time,
> money, and embarrassment.   Learn More!
> http://p.sf.net/sfu/hpdev2dev-nov
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>

[cx-oracle-users] Crash of OCIStmtRelease during cursor.close()

[<Gui...@su...>]

Hello,

 

I am using cxOracle 4.4 on a Windows XP 32 bits platform with an Oracle
11.2 client and a distant Oracle 11.2 server.

 

The problem I am getting since we migrated our software to Oracle 11 is
that during one of our long running processes, the software will
sometimes suddenly crash with no exception raised.

Our process calls a PL/SQL package hundreds of thousands of times and
sometimes crashes after one of those calls has ended, in the middle of
cursor.close().

I determined that inside Cursor_Close(...) it is Cursor_FreeHandle(...)
and then the call to OCIStmtRelease that crashes. 

 

Here's a simplified view of what we do: 

 

for(~200 000 times){

crs = Cursor()

crs.prepare(stmt) # stmt is a call to a PL/SQL package

crs.executemanyprepared(1)

crs.close() => sometimes crashes in Cursor_FreeHandle during call to
OCIStmtRelease, never in the same iteration of the loop

}

 

The thing is that this crash does not happen systematically but rather
in about one out of two executions of our process. 

 

We previously used cxOracle 4.1.1 but moved to 4.4 when noticing this
problem with Oracle 11. But this 4.4 version doesn't seem to be solving
the problem, unless we need to make more changes to the way we use
cxOracle after migrating to 4.4.

 

Does anybody have a clue as to why OCIStmtRelease would sometimes
unexpectedly crash like that without throwing any exception?

 

Thanks a lot for the help you can give me. I've been working for more
than a month on this problem and it's becoming critical for our clients
that a solution be found.

 

Guillaume Aouizerate