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

Re: [cx-oracle-users] Types used for values returned from a query

[Paul M. <p.f...@gm...>]

On Thu, 10 Feb 2005 07:50:11 -0700, Anthony Tuininga
<an...@co...> wrote:
> > Quite possibly. I can just bind the variable as a String in Java, and
> > then load it into a Number column, I guess. Could there be any format
> > issues (exponential formats, commas in numbers, etc)? I only ask
> > because the environemnt makes it annoyingly hard to report errors, so
> > when things go wrong it's a real pain to debug :-(
> 
> Not certain. Since I've only dumped it for future import into an Oracle
> database that hasn't been an issue for me. You'll have to try -- you can
> find out what happens by simply issuing "to_char(number)" on any number
> you would care to and looking at the resulting string.

I tried it out, and it works perfectly. Thanks for the pointer. But
(see below) the result isn't always the same as to_char() gives, in
the presence of NLS parameters...

> > That may be an option, but frankly, this is pretty specialised use
> > where an exact representation is crucial. Maybe just having a way of
> > saying that a specific column in a query must be returned as a string,
> > and then doing string->Decimal in Python would be suitable. Something
> > like
[...]
> > All I really care about is having some way of being sure that there's
> > no precision loss - using strings does it, the rest is just
> > convenience (only using strings where it's really needed).
> >
> > Sorry - must dash, but I hope you get the idea...
> 
> I do and I've considered something similar. Perhaps a
> cursor.define(position, data_type) which would allow you to override the
> default retrieve definition. In your example the call would be
> "c.define(1, cx_Oracle.STRING)". I suspect it would have to take place
> before the execute() call, though. Comments, anyone?

That looks fine. In practice, though, for my requirement,
OPT_NumbersAsStrings is perfectly adequate. As things stand, I don't
have a use for anything finer grained, and the only example I can
think of is using a string to allow me to construct a Decimal without
loss of precision. It would probably be worth having some additional
use cases before expending much effort on this :-)

One question that does come up is that of format. My application was
basically like yours - dump the numbers to strings so that you can
later load the strings back into the database. As this is
Oracle->Oracle, no format issue arises. However, if you want to use
the results elsewhere, you'd need to define the format. A check of the
Oracle OCI documentation wasn't clear to me so I did some
experimenting. It *looks* like the format is

    <digits>.<digits>E<sign><digits>

with the .<digits> and the E<sign><digits> parts optional. But it's
not affected by NLS_NUMERIC_CHARACTERS, which is probably good...

OK, I'm overdoing this now. But the point remains that ultimately use
of OPT_NumbersAsStrings and any column-level equivalent is going to
hit a need to be sure what the valid formats are. Oracle's
documentation isn't very clear on this, and expecting Python
programmers to have a good understanding of OCI is probably expecting
a bit too much.

Hmm, actually it may not even be roundtrip-safe. Try this:

>>> cn = cx_Oracle.connect("scott/tiger")
>>> cx_Oracle.OPT_NumbersAsStrings = 1
>>> c = cn.cursor()
>>> c.execute("select 123456/100 from dual")
[<NumberVar object at 0x00973ED0>]
>>> n, = c.fetchone()
>>> n
'1234.56'
>>> c.execute("create table t (n number)")
>>> c.execute("insert into t values (:a)", a=n)
>>> c.execute("alter session set NLS_NUMERIC_CHARACTERS=',.'")
>>> c.execute("select 123456/100 from dual")
[<NumberVar object at 0x00973ED0>]
>>> n, = c.fetchone()
>>> n
'1234.56'
>>> c.execute("insert into t values (:a)", a=n)
Traceback (most recent call last):
  File "<stdin>", line 1, in ?
cx_Oracle.DatabaseError: ORA-01722: invalid number

>>>

Ick. This is getting complicated...

Well, it works for me. So this is not something I'm going to lose sleep over :-)

Paul.

Re: [cx-oracle-users] Types used for values returned from a query

[Anthony T. <an...@co...>]

Paul Moore wrote:

>>Oracle is quite happy to accept the string as input to a numeric column. I'm
>>not sure if that will work for you but it is an option.
> 
> 
> Quite possibly. I can just bind the variable as a String in Java, and
> then load it into a Number column, I guess. Could there be any format
> issues (exponential formats, commas in numbers, etc)? I only ask
> because the environemnt makes it annoyingly hard to report errors, so
> when things go wrong it's a real pain to debug :-(

Not certain. Since I've only dumped it for future import into an Oracle 
database that hasn't been an issue for me. You'll have to try -- you can 
find out what happens by simply issuing "to_char(number)" on any number 
you would care to and looking at the resulting string.

>>>Alternatively, is there any likelihood of cxOracle using Python 2.4's
>>>Decimal type in the near future?
>>
>>Theoretically this wouldn't be all that difficult to accomplish but
>>since there is no C implementation this would mean a significant
>>performance penalty.
>>Having used the Decimal type myself in pure Python
>>I know that it is the most efficiently coded module, either. I suppose
>>this type could be returned __only__ if the data is not an integer and
>>that would mitigate the problem somewhat. Comments?
> 
> 
> That may be an option, but frankly, this is pretty specialised use
> where an exact representation is crucial. Maybe just having a way of
> saying that a specific column in a query must be returned as a string,
> and then doing string->Decimal in Python would be suitable. Something
> like
> 
>    c = cn.cursor()
>    c.execute("select...")
>    c.col_as_string(1)
>    row = c.fetchone()
>    decval = Decimal(row[1])
> 
> All I really care about is having some way of being sure that there's
> no precision loss - using strings does it, the rest is just
> convenience (only using strings where it's really needed).
> 
> Sorry - must dash, but I hope you get the idea...

I do and I've considered something similar. Perhaps a 
cursor.define(position, data_type) which would allow you to override the 
default retrieve definition. In your example the call would be 
"c.define(1, cx_Oracle.STRING)". I suspect it would have to take place 
before the execute() call, though. Comments, anyone?

> Paul
> 
> 
> -------------------------------------------------------
> SF email is sponsored by - The IT Product Guide
> Read honest & candid reviews on hundreds of IT Products from real users.
> Discover which products truly live up to the hype. Start reading now.
> http://ads.osdn.com/?ad_id=6595&alloc_id=14396&op=click
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users

-- 
Anthony Tuininga
an...@co...

Computronix
Distinctive Software. Real People.
Suite 200, 10216 - 124 Street NW
Edmonton, AB, Canada  T5N 4A3
Phone:	(780) 454-3700
Fax:	(780) 454-3838
http://www.computronix.com

Re: [cx-oracle-users] Types used for values returned from a query

[Paul M. <p.f...@gm...>]

On Thu, 10 Feb 2005 09:32:47 +1000, Leith Parkin <lei...@gm...> wrote:
> Why cant you use cast?

Basically because I'm writing something to handle user-supplied SELECT
statements, so I don't have control over the query (beyond saying that
I don't certain types - I don't think I'll get away with refusing to
support numbers, though :-)).

Regards,
Paul.

Re: [cx-oracle-users] Types used for values returned from a query

[Leith P. <lei...@gm...>]

Why cant you use cast?

>>> import cx_Oracle as O
>>> db = O.connect('sid')
>>> s = db.cursor()
>>> s.execute('select CAST( 1234 as varchar2(255)) a_string from dual')
[<StringVar object at 0x402240b0>]



On Wed, 9 Feb 2005 18:46:32 +0000, Paul Moore <p.f...@gm...> wrote:
> On Wed, 09 Feb 2005 11:08:36 -0700, Anthony Tuininga
> <an...@co...> wrote:
> > This is true in Python 2.4 and up. Python 2.3 and earlier do use an
> > internal date representation which is less powerful than the Python
> > datetime data type. I'm assuming here that you are using Python 2.4.
> 
> Correct - sorry, I didn't mention this explicitly.
> 
> > The way it currently works is as follows: if the Oracle data type has no
> > decimal places, the value is converted to a long/integer value;
> > otherwise, the value is converted to a float with all of its loss of
> > precision.
> 
> That's pretty much what I'd determined by experiment.
> 
> > When faced with this problem myself (for a program which
> > dumps the contents of arbitrary queries in a format suitable for later
> > importing) I dumped all numbers as strings.
> 
> That's pretty much the same requirement as I have and so the same
> solution should apply. The only issue I have is that my loader is
> written as a Java stored procedure inside the database, so my dump
> format needs to be Java-readable.
> 
> > Oracle is quite happy to accept the string as input to a numeric column. I'm
> > not sure if that will work for you but it is an option.
> 
> Quite possibly. I can just bind the variable as a String in Java, and
> then load it into a Number column, I guess. Could there be any format
> issues (exponential formats, commas in numbers, etc)? I only ask
> because the environemnt makes it annoyingly hard to report errors, so
> when things go wrong it's a real pain to debug :-(
> 
> > If you are interested in pursuing that option, you should take a look at the
> > OPT_NumbersAsStrings option in cx_Oracle.
> 
> That sounds great. I'll look at that in the morning.
> 
> > > Alternatively, is there any likelihood of cxOracle using Python 2.4's
> > > Decimal type in the near future?
> >
> > Theoretically this wouldn't be all that difficult to accomplish but
> > since there is no C implementation this would mean a significant
> > performance penalty.
> > Having used the Decimal type myself in pure Python
> > I know that it is the most efficiently coded module, either. I suppose
> > this type could be returned __only__ if the data is not an integer and
> > that would mitigate the problem somewhat. Comments?
> 
> That may be an option, but frankly, this is pretty specialised use
> where an exact representation is crucial. Maybe just having a way of
> saying that a specific column in a query must be returned as a string,
> and then doing string->Decimal in Python would be suitable. Something
> like
> 
>   c = cn.cursor()
>   c.execute("select...")
>   c.col_as_string(1)
>   row = c.fetchone()
>   decval = Decimal(row[1])
> 
> All I really care about is having some way of being sure that there's
> no precision loss - using strings does it, the rest is just
> convenience (only using strings where it's really needed).
> 
> Sorry - must dash, but I hope you get the idea...
> 
> Paul
> 
> -------------------------------------------------------
> SF email is sponsored by - The IT Product Guide
> Read honest & candid reviews on hundreds of IT Products from real users.
> Discover which products truly live up to the hype. Start reading now.
> http://ads.osdn.com/?ad_id=6595&alloc_id=14396&op=click
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

Re: [cx-oracle-users] Types used for values returned from a query

[Paul M. <p.f...@gm...>]

On Wed, 09 Feb 2005 11:08:36 -0700, Anthony Tuininga
<an...@co...> wrote:
> This is true in Python 2.4 and up. Python 2.3 and earlier do use an
> internal date representation which is less powerful than the Python
> datetime data type. I'm assuming here that you are using Python 2.4.

Correct - sorry, I didn't mention this explicitly.

> The way it currently works is as follows: if the Oracle data type has no
> decimal places, the value is converted to a long/integer value;
> otherwise, the value is converted to a float with all of its loss of
> precision.

That's pretty much what I'd determined by experiment.

> When faced with this problem myself (for a program which
> dumps the contents of arbitrary queries in a format suitable for later
> importing) I dumped all numbers as strings.

That's pretty much the same requirement as I have and so the same
solution should apply. The only issue I have is that my loader is
written as a Java stored procedure inside the database, so my dump
format needs to be Java-readable.

> Oracle is quite happy to accept the string as input to a numeric column. I'm
> not sure if that will work for you but it is an option.

Quite possibly. I can just bind the variable as a String in Java, and
then load it into a Number column, I guess. Could there be any format
issues (exponential formats, commas in numbers, etc)? I only ask
because the environemnt makes it annoyingly hard to report errors, so
when things go wrong it's a real pain to debug :-(

> If you are interested in pursuing that option, you should take a look at the
> OPT_NumbersAsStrings option in cx_Oracle.

That sounds great. I'll look at that in the morning.

> > Alternatively, is there any likelihood of cxOracle using Python 2.4's
> > Decimal type in the near future?
> 
> Theoretically this wouldn't be all that difficult to accomplish but
> since there is no C implementation this would mean a significant
> performance penalty.
> Having used the Decimal type myself in pure Python
> I know that it is the most efficiently coded module, either. I suppose
> this type could be returned __only__ if the data is not an integer and
> that would mitigate the problem somewhat. Comments?

That may be an option, but frankly, this is pretty specialised use
where an exact representation is crucial. Maybe just having a way of
saying that a specific column in a query must be returned as a string,
and then doing string->Decimal in Python would be suitable. Something
like

   c = cn.cursor()
   c.execute("select...")
   c.col_as_string(1)
   row = c.fetchone()
   decval = Decimal(row[1])

All I really care about is having some way of being sure that there's
no precision loss - using strings does it, the rest is just
convenience (only using strings where it's really needed).

Sorry - must dash, but I hope you get the idea...

Paul

Re: [cx-oracle-users] Types used for values returned from a query

[Anthony T. <an...@co...>]

Paul Moore wrote:
> I have an application which needs to be able to execute a (relatively)
> arbitrary query and dump the output to stdout, in such a way that
> another (non-Python) program can pick up the values and rebuild them
> exactly.
> 
> I don't need to support exotic types like LOBs, but I *do* need to
> handle basic numbers, dates, and strings.
> 
> To do this, I need to understand *exactly* what types are returned by
> cursor.fetchone().
> 
> Strings and dates are OK - strings are obviously trivial, and
> dates/timestamps come back as Python datetime values, so I can convert
> to something transportable like "microseconds since the epoch,
> converted to a string".

This is true in Python 2.4 and up. Python 2.3 and earlier do use an 
internal date representation which is less powerful than the Python 
datetime data type. I'm assuming here that you are using Python 2.4.

> But I seem to be having problems with numbers. At the moment, I'm just
> doing str() on the returned value, and getting  odd results. I suspect
> this is because I'm sometimes getting floats or longs and just doing
> str() on these is (a) giving me variable formats, and (b) losing
> precision in the case of floats.
> 
> Is there a way of getting a precise value back for a numeric field, or
> is the data lost? Can I do things with NumberVar types, and get the
> full precision data from that?

The way it currently works is as follows: if the Oracle data type has no 
decimal places, the value is converted to a long/integer value; 
otherwise, the value is converted to a float with all of its loss of 
precision. When faced with this problem myself (for a program which 
dumps the contents of arbitrary queries in a format suitable for later 
importing) I dumped all numbers as strings. Oracle is quite happy to 
accept the string as input to a numeric column. I'm not sure if that 
will work for you but it is an option. If you are interested in pursuing 
that option, you should take a look at the OPT_NumbersAsStrings option 
in cx_Oracle.

> Alternatively, is there any likelihood of cxOracle using Python 2.4's
> Decimal type in the near future?

Theoretically this wouldn't be all that difficult to accomplish but 
since there is no C implementation this would mean a significant 
performance penalty. Having used the Decimal type myself in pure Python 
I know that it is the most efficiently coded module, either. I suppose 
this type could be returned __only__ if the data is not an integer and 
that would mitigate the problem somewhat. Comments?

> Thanks,
> Paul.
> 
> 
> -------------------------------------------------------
> SF email is sponsored by - The IT Product Guide
> Read honest & candid reviews on hundreds of IT Products from real users.
> Discover which products truly live up to the hype. Start reading now.
> http://ads.osdn.com/?ad_id=6595&alloc_id=14396&op=click
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users

-- 
Anthony Tuininga
an...@co...

Computronix
Distinctive Software. Real People.
Suite 200, 10216 - 124 Street NW
Edmonton, AB, Canada  T5N 4A3
Phone:	(780) 454-3700
Fax:	(780) 454-3838
http://www.computronix.com

[cx-oracle-users] Types used for values returned from a query

[Paul M. <p.f...@gm...>]

I have an application which needs to be able to execute a (relatively)
arbitrary query and dump the output to stdout, in such a way that
another (non-Python) program can pick up the values and rebuild them
exactly.

I don't need to support exotic types like LOBs, but I *do* need to
handle basic numbers, dates, and strings.

To do this, I need to understand *exactly* what types are returned by
cursor.fetchone().

Strings and dates are OK - strings are obviously trivial, and
dates/timestamps come back as Python datetime values, so I can convert
to something transportable like "microseconds since the epoch,
converted to a string".

But I seem to be having problems with numbers. At the moment, I'm just
doing str() on the returned value, and getting  odd results. I suspect
this is because I'm sometimes getting floats or longs and just doing
str() on these is (a) giving me variable formats, and (b) losing
precision in the case of floats.

Is there a way of getting a precise value back for a numeric field, or
is the data lost? Can I do things with NumberVar types, and get the
full precision data from that?

Alternatively, is there any likelihood of cxOracle using Python 2.4's
Decimal type in the near future?

Thanks,
Paul.

Re: [cx-oracle-users] cx_Oracle 4.1 crashing Python 2.3 vm under Mac OS X

[Marcos P. <ma...@bu...>]

=BFHave you tried python -v to see what exact files are being loaded?

El mar, 01-02-2005 a las 11:20 -0800, Sean P.Kane escribi=F3:
> Anthony,
>=20
> I have been trying to compile this version on Mac OS X 10.3.7 with =20
> Python 2.3 and have run into a  strange problem. Below is a copy of the=
 =20
> session, where I build and install cx_Oracle and then try to import it.=
 =20
> It crashes the python interpreter. Even though I get a few compiler =20
> errors, it seems that everything is actually going fine. I was able to =
=20
> use the previous version without a problem. Do you have any ideas what =
=20
> might be going on. I researched the error a bit and it mentioned that =20
> this can happen when something is compiled against a different version =
=20
> of Python and then the one it is being run in. I do have python 2.4 on =
=20
> the system as well, but it is in another directory and I have done some=
 =20
> testing, and am reasonable sure this isn't the problem, unless there =20
> are some hard coded paths in the module somewhere (which seems =20
> unlikely).
>=20
> Any ideas?
>=20
> Thanks,
> Sean
>=20
> ------
>=20
> $ sudo python setup.py build
>=20
> running build
> running build_ext
> building 'cx_Oracle' extension
> creating build
> creating build/temp.darwin-7.7.0-Power_Macintosh-2.3
> gcc -fno-strict-aliasing -Wno-long-double -no-cpp-precomp =20
> -mno-fused-madd -fno-common -dynamic -DNDEBUG -g -O3 -Wall =20
> -Wstrict-prototypes -I/Users/spkane/oracle/OraHome_1/rdbms/demo =20
> -I/Users/spkane/oracle/OraHome_1/rdbms/public =20
> -I/Users/spkane/oracle/OraHome_1/network/public =20
> -I/Users/spkane/oracle/OraHome_1/plsql/public =20
> -I/System/Library/Frameworks/Python.framework/Versions/2.3/include/=20
> python2.3 -c cx_Oracle.c -o =20
> build/temp.darwin-7.7.0-Power_Macintosh-2.3/cx_Oracle.o =20
> -DBUILD_TIME=3D"February 01, 2005 11:11:53"
> In file included from =20
> /Users/spkane/oracle/OraHome_1/rdbms/public/oci.h:2327,
>                   from cx_Oracle.c:9:
> /Users/spkane/oracle/OraHome_1/rdbms/public/oci1.h:148: warning: =20
> function declaration isn't a prototype
> In file included from =20
> /Users/spkane/oracle/OraHome_1/rdbms/public/ociap.h:206,
>                   from =20
> /Users/spkane/oracle/OraHome_1/rdbms/public/oci.h:2351,
>                   from cx_Oracle.c:9:
> /Users/spkane/oracle/OraHome_1/rdbms/public/nzt.h:674: warning: =20
> function declaration isn't a prototype
> /Users/spkane/oracle/OraHome_1/rdbms/public/nzt.h:2665: warning: =20
> function declaration isn't a prototype
> /Users/spkane/oracle/OraHome_1/rdbms/public/nzt.h:2674: warning: =20
> function declaration isn't a prototype
> /Users/spkane/oracle/OraHome_1/rdbms/public/nzt.h:2684: warning: =20
> function declaration isn't a prototype
> /Users/spkane/oracle/OraHome_1/rdbms/public/nzt.h:2693: warning: =20
> function declaration isn't a prototype
> /Users/spkane/oracle/OraHome_1/rdbms/public/nzt.h:2702: warning: =20
> function declaration isn't a prototype
> /Users/spkane/oracle/OraHome_1/rdbms/public/nzt.h:2711: warning: =20
> function declaration isn't a prototype
> /Users/spkane/oracle/OraHome_1/rdbms/public/nzt.h:2719: warning: =20
> function declaration isn't a prototype
> /Users/spkane/oracle/OraHome_1/rdbms/public/nzt.h:2729: warning: =20
> function declaration isn't a prototype
> /Users/spkane/oracle/OraHome_1/rdbms/public/nzt.h:2736: warning: =20
> function declaration isn't a prototype
> /Users/spkane/oracle/OraHome_1/rdbms/public/nzt.h:2744: warning: =20
> function declaration isn't a prototype
> In file included from =20
> /Users/spkane/oracle/OraHome_1/rdbms/public/oci.h:2351,
>                   from cx_Oracle.c:9:
> /Users/spkane/oracle/OraHome_1/rdbms/public/ociap.h:9952: warning: =20
> function declaration isn't a prototype
> /Users/spkane/oracle/OraHome_1/rdbms/public/ociap.h:9958: warning: =20
> function declaration isn't a prototype
> creating build/lib.darwin-7.7.0-Power_Macintosh-2.3
> gcc -Wl,-F. -Wl,-F. -bundle -framework Python =20
> build/temp.darwin-7.7.0-Power_Macintosh-2.3/cx_Oracle.o =20
> -L/Users/spkane/oracle/OraHome_1/lib -lclntsh -o =20
> build/lib.darwin-7.7.0-Power_Macintosh-2.3/cx_Oracle.so
>=20
> $ sudo python setup.py install
>=20
> running install
> running build
> running build_ext
> running install_lib
> copying build/lib.darwin-7.7.0-Power_Macintosh-2.3/cx_Oracle.so -> =20
> /System/Library/Frameworks/Python.framework/Versions/2.3/lib/python2.3/=
=20
> site-packages
>=20
> $ python
>=20
> Python 2.3 (#1, Sep 13 2003, 00:49:11)
> [GCC 3.3 20030304 (Apple Computer, Inc. build 1495)] on darwin
> Type "help", "copyright", "credits" or "license" for more information.
>  >>> import cx_Oracle
> Fatal Python error: Interpreter not initialized (version mismatch?)
> Abort trap
>=20
> $=20
>=20
>=20
>=20
> -------------------------------------------------------
> This SF.Net email is sponsored by: IntelliVIEW -- Interactive Reporting
> Tool for open source databases. Create drag-&-drop reports. Save time
> by over 75%! Publish reports on the web. Export to DOC, XLS, RTF, etc.
> Download a FREE copy at http://www.intelliview.com/go/osdn_nl
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users

[cx-oracle-users] cx_Oracle 4.1 crashing Python 2.3 vm under Mac OS X

[Sean P. K. <sp...@ge...>]

Anthony,

I have been trying to compile this version on Mac OS X 10.3.7 with  
Python 2.3 and have run into a  strange problem. Below is a copy of the  
session, where I build and install cx_Oracle and then try to import it.  
It crashes the python interpreter. Even though I get a few compiler  
errors, it seems that everything is actually going fine. I was able to  
use the previous version without a problem. Do you have any ideas what  
might be going on. I researched the error a bit and it mentioned that  
this can happen when something is compiled against a different version  
of Python and then the one it is being run in. I do have python 2.4 on  
the system as well, but it is in another directory and I have done some  
testing, and am reasonable sure this isn't the problem, unless there  
are some hard coded paths in the module somewhere (which seems  
unlikely).

Any ideas?

Thanks,
Sean

------

$ sudo python setup.py build

running build
running build_ext
building 'cx_Oracle' extension
creating build
creating build/temp.darwin-7.7.0-Power_Macintosh-2.3
gcc -fno-strict-aliasing -Wno-long-double -no-cpp-precomp  
-mno-fused-madd -fno-common -dynamic -DNDEBUG -g -O3 -Wall  
-Wstrict-prototypes -I/Users/spkane/oracle/OraHome_1/rdbms/demo  
-I/Users/spkane/oracle/OraHome_1/rdbms/public  
-I/Users/spkane/oracle/OraHome_1/network/public  
-I/Users/spkane/oracle/OraHome_1/plsql/public  
-I/System/Library/Frameworks/Python.framework/Versions/2.3/include/ 
python2.3 -c cx_Oracle.c -o  
build/temp.darwin-7.7.0-Power_Macintosh-2.3/cx_Oracle.o  
-DBUILD_TIME="February 01, 2005 11:11:53"
In file included from  
/Users/spkane/oracle/OraHome_1/rdbms/public/oci.h:2327,
                  from cx_Oracle.c:9:
/Users/spkane/oracle/OraHome_1/rdbms/public/oci1.h:148: warning:  
function declaration isn't a prototype
In file included from  
/Users/spkane/oracle/OraHome_1/rdbms/public/ociap.h:206,
                  from  
/Users/spkane/oracle/OraHome_1/rdbms/public/oci.h:2351,
                  from cx_Oracle.c:9:
/Users/spkane/oracle/OraHome_1/rdbms/public/nzt.h:674: warning:  
function declaration isn't a prototype
/Users/spkane/oracle/OraHome_1/rdbms/public/nzt.h:2665: warning:  
function declaration isn't a prototype
/Users/spkane/oracle/OraHome_1/rdbms/public/nzt.h:2674: warning:  
function declaration isn't a prototype
/Users/spkane/oracle/OraHome_1/rdbms/public/nzt.h:2684: warning:  
function declaration isn't a prototype
/Users/spkane/oracle/OraHome_1/rdbms/public/nzt.h:2693: warning:  
function declaration isn't a prototype
/Users/spkane/oracle/OraHome_1/rdbms/public/nzt.h:2702: warning:  
function declaration isn't a prototype
/Users/spkane/oracle/OraHome_1/rdbms/public/nzt.h:2711: warning:  
function declaration isn't a prototype
/Users/spkane/oracle/OraHome_1/rdbms/public/nzt.h:2719: warning:  
function declaration isn't a prototype
/Users/spkane/oracle/OraHome_1/rdbms/public/nzt.h:2729: warning:  
function declaration isn't a prototype
/Users/spkane/oracle/OraHome_1/rdbms/public/nzt.h:2736: warning:  
function declaration isn't a prototype
/Users/spkane/oracle/OraHome_1/rdbms/public/nzt.h:2744: warning:  
function declaration isn't a prototype
In file included from  
/Users/spkane/oracle/OraHome_1/rdbms/public/oci.h:2351,
                  from cx_Oracle.c:9:
/Users/spkane/oracle/OraHome_1/rdbms/public/ociap.h:9952: warning:  
function declaration isn't a prototype
/Users/spkane/oracle/OraHome_1/rdbms/public/ociap.h:9958: warning:  
function declaration isn't a prototype
creating build/lib.darwin-7.7.0-Power_Macintosh-2.3
gcc -Wl,-F. -Wl,-F. -bundle -framework Python  
build/temp.darwin-7.7.0-Power_Macintosh-2.3/cx_Oracle.o  
-L/Users/spkane/oracle/OraHome_1/lib -lclntsh -o  
build/lib.darwin-7.7.0-Power_Macintosh-2.3/cx_Oracle.so

$ sudo python setup.py install

running install
running build
running build_ext
running install_lib
copying build/lib.darwin-7.7.0-Power_Macintosh-2.3/cx_Oracle.so ->  
/System/Library/Frameworks/Python.framework/Versions/2.3/lib/python2.3/ 
site-packages

$ python

Python 2.3 (#1, Sep 13 2003, 00:49:11)
[GCC 3.3 20030304 (Apple Computer, Inc. build 1495)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
 >>> import cx_Oracle
Fatal Python error: Interpreter not initialized (version mismatch?)
Abort trap

$ 

[cx-oracle-users] RE: cx_Oracle 4.1

[Moore, P. <Pau...@at...>]

I sent this at the time of the beta, and previously while 4.0.1 was =
current. I don't recall getting any reply at the time, and it looks like =
it missed the 4.1 release :-(

Is there any chance this could be considered for a future version, or is =
there a reason you'd rather not include it?

Regards,
Paul.

-----Original Message-----
From: cx-...@li...
[mailto:cx-...@li...]On Behalf Of Moore,
Paul
Sent: 02 September 2004 10:02
To: cx-...@li...; Anthony Tuininga
Subject: [cx-oracle-users] RE: cx_Oracle 4.1 beta1


From: Anthony Tuininga
> What's new?
[...]

A while back I sent you a patch to allow specifying SERVER=3DDEDICATED =
in makedsn(). It doesn't appear to have made it into 4.1 beta 1. Is =
there any chance it could be added? I attach the patch again, it's =
against 4.0.1 but I can update it if you prefer.

My original message was as follows:

"""
Would it be possible to add a keyword parameter to makedsn,
dedicated=3DTrue/False, which adds the (SERVER=3DDEDICATED) parameter
to the generated connect string? In certain types of firewall
environments (ie, ours :-() multithreaded connections won't get
through the firewall, and the simplest answer is just to force
SERVER=3DDEDICATED.

It's easy enough to either generate my own connect string,
or modify the generated one, but being able to say
makedsn(host,port,sid,dedicated=3DTrue) would be a nice convenience.
"""

Thanks,
Paul.



_________________________________________________________________________=
_
This e-mail and the documents attached are confidential and intended=20
solely for the addressee; it may also be privileged. If you receive this =

e-mail in error, please notify the sender immediately and destroy it.
As its integrity cannot be secured on the Internet, the Atos Origin =
group=20
liability cannot be triggered for the message content. Although the=20
sender endeavours to maintain a computer virus-free network, the sender=20
does not warrant that this transmission is virus-free and will not be=20
liable for any damages resulting from any virus transmitted.
_________________________________________________________________________=
_


_________________________________________________________________________=
_
This e-mail and the documents attached are confidential and intended=20
solely for the addressee; it may also be privileged. If you receive this =

e-mail in error, please notify the sender immediately and destroy it.
As its integrity cannot be secured on the Internet, the Atos Origin =
group=20
liability cannot be triggered for the message content. Although the=20
sender endeavours to maintain a computer virus-free network, the sender=20
does not warrant that this transmission is virus-free and will not be=20
liable for any damages resulting from any virus transmitted.
_________________________________________________________________________=
_

[cx-oracle-users] cx_Oracle packaging issue

[Anthony T. <an...@co...>]

I have received a number of e-mails about the fact that the Python 2.4 
builds on Windows were missing the rather vital file cx_Oracle.pyd. :-) 
Tracking it down resulted in the discovery of a distutils bug in Python 
2.4. I got around the problem (and logged a bug on SourceForge so it 
should get fixed properly later) and new files have been uploaded to 
SourceForge. As always, you can get at them directly from my web site:

http://starship.python.net/crew/atuining

My apologies for the packaging mishap.

-- 
Anthony Tuininga
an...@co...

Computronix
Distinctive Software. Real People.
Suite 200, 10216 - 124 Street NW
Edmonton, AB, Canada  T5N 4A3
Phone:	(780) 454-3700
Fax:	(780) 454-3838
http://www.computronix.com

[cx-oracle-users] cx_Oracle 4.1

[Anthony T. <an...@co...>]

What is cx_Oracle?

cx_Oracle is a Python extension module that allows access to Oracle and
conforms to the Python database API 2.0 specifications with a few
exceptions.


Where do I get it?

http://starship.python.net/crew/atuining


What's new?

  1) Added support for Python 2.4. In Python 2.4, the datetime module is
     used for both binding and fetching of date and timestamp data. In
     Python 2.3, objects from the datetime module can be bound but the
     internal datetime objects will be returned from queries.
  2) Added pickling support for LOB and datetime data.
  3) Fully qualified the table name that was missing in an alter table
     statement in the setup test script as noted by Marc Gehling.
  4) Added a section allowing for the setting of the RPATH linker
     directive in setup.py as requested by Iustin Pop.
  5) Added code to raise a programming error exception when an attempt is
     made to access a LOB locator variable in a subsequent fetch.
  6) The username, password and dsn (tnsentry) are stored on the
     connection object when specified, regardless of whether or not a
     standard connection takes place.
  7) Added additional module level constant called "LOB" as requested by
     Joseph Canedo.
  8) Changed exception type to IntegrityError for constraint violations
     as requested by Joseph Canedo.
  9) If scale and precision are not specified, an attempt is made to
     return a long integer as requested by Joseph Canedo.
10) Added workaround for Oracle bug which returns an invalid handle when
     the prepare call fails. Thanks to al...@hs... for providing the
     code that demonstrated the problem.
11) The cusor method arravar() will now accept the actual list so that
     it is not necessary to call cursor.arrayvar() followed immediately
     by var.setvalue().
12) Fixed bug where attempts to execute the statement "None" with bind
     variables would cause a segmentation fault.
13) Added support for binding by position (paramstyle = "numeric").
14) Removed memory leak created by calls to OCIParamGet() which were not
     mirrored by calls to OCIDescriptorFree(). Thanks to Mihai Ibanescu
     for pointing this out and providing a patch.
15) Added support for calling cursor.executemany() with statement None
     implying that the previously prepared statement ought to be
     executed. Thanks to Mihai Ibanescu for providing a patch.
16) Added support for rebinding variables when a subsequent call to
     cursor.executemany() uses a different number of rows. Thanks to
     Mihai Ibanescu for supplying a patch.
17) The microseconds are now displayed in datetime variables when
     nonzero similar to method used in the datetime module.
18) Added support for binary_float and binary_double columns in Oracle
     10g.

Changes since 4.1 beta 1

  1) Fixed bug where subclasses of Cursor do not pass the connection in
     the constructor causing a segfault.
  2) DDL statements must be reparsed before execution as noted by Mihai
     Ibanescu.
  3) Add support for setting input sizes by position.
  4) Fixed problem with catching an exception during execute and then
     still attempting to perform a fetch afterwards as noted by Leith
     Parkin.
  5) Rename the types so that they can be pickled and unpickled. Thanks
     to Harri Pasanen for pointing out the problem.
  6) Handle invalid NLS_LANG setting properly (Oracle seems to like to
     provide a handle back even though it is invalid) and determine the
     number of bytes per character in order to allow for proper support
     in the future of multibyte and variable width character sets.
  7) Remove date checking from the native case since Python already
     checks that dates are valid; enhance error message when invalid
     dates are encountered so that additional processing can be done.
  8) Fix bug executing SQL using numeric parameter names with predefined
     variables (such as what takes place when calling stored procedures
     with out parameters).
  9) Add support for reading CLOB values using multibyte or variable
     length character sets.
-- 
Anthony Tuininga
an...@co...

Computronix
Distinctive Software. Real People.
Suite 200, 10216 - 124 Street NW
Edmonton, AB, Canada  T5N 4A3
Phone:	(780) 454-3700
Fax:	(780) 454-3838
http://www.computronix.com

Re: [cx-oracle-users] SQLX support ?

[Anthony T. <an...@co...>]

No, you did not make a mistake. User defined types are not currently 
supported by cx_Oracle but I do have plans to do that at some point -- 
hopefully soon but recently I've been rather busy so I wouldn't count on 
anything in the next couple of months. In the meantime your best option 
is to use PL/SQL in your executed statement and pass back to Python 
scalar types.

Roland SAIKALI wrote:

> Hi,
>  
> I'd like to use some SQLX function with cx_Oracle but it seems to be 
> not supported yet.
> Here is a very small and simple test of SQLX Query (it's working with 
> SQLPlus)...
> My configuration is Python2.3 / Oracle9i ...
>  
> ###########
> # Connection
> myDsn = cx_Oracle.makedsn(myHostname, myPort, myInstance)
> myConnection = cx_Oracle.connect(myUser,myPassword,myDsn)
>  
> # Query
> cursor = myConnection.cursor()
> sql = """SELECT XMLElement(\"Attributes\", DAT_VALEUR1) FROM 
> AC_V_DATA_ALL"""
> # Results
> cursor.execute(sql)
> for line in cursor.fetchall():
>  print line
> ###########
>  
> So, I got the following error message :
> Traceback (most recent call last):
>   File "Test.py", line 22, in ?
>     cursor.execute(sql)
> cx_Oracle.NotSupportedError: Variable_TypeByOracleDataType: unhandled 
> data type 108
>  
> Does I make some mistakes ?
> If not, does it planned to be supported in later versions of cx_Oracle ?
>  
> Thanks a lot.
>  
>
> Roland.
>
> --------------------------------------------------------------------------------
>
> */Aerocity Data Management Team/*
>
> Transiciel Technologies
>
> www.transiciel.com <http://www.transiciel.com/>
>
> --------------------------------------------------------------------------------
>  

[cx-oracle-users] SQLX support ?

[Roland S. <rsa...@ma...>]

Hi,
=20
I'd like to use some SQLX function with cx_Oracle but it seems to be not
supported yet.
Here is a very small and simple test of SQLX Query (it's working with
SQLPlus)...
My configuration is Python2.3 / Oracle9i ...
=20
###########
# Connection
myDsn =3D cx_Oracle.makedsn(myHostname, myPort, myInstance)
myConnection =3D cx_Oracle.connect(myUser,myPassword,myDsn)
=20
# Query
cursor =3D myConnection.cursor()
sql =3D """SELECT XMLElement(\"Attributes\", DAT_VALEUR1) FROM
AC_V_DATA_ALL"""

# Results
cursor.execute(sql)
for line in cursor.fetchall():
 print line
###########
=20
So, I got the following error message :
Traceback (most recent call last):
  File "Test.py", line 22, in ?
    cursor.execute(sql)
cx_Oracle.NotSupportedError: Variable_TypeByOracleDataType: unhandled =
data
type 108
=20
Does I make some mistakes ?
If not, does it planned to be supported in later versions of cx_Oracle ?
=20
Thanks a lot.
=20

Roland.

-------------------------------------------------------------------------=
---
----

Aerocity Data Management Team

Transiciel Technologies

www.transiciel.com <http://www.transiciel.com/>=20


-------------------------------------------------------------------------=
---
----

=20

Re: [cx-oracle-users] Re: How to optimize (Pro*C is 5x faster)?

[Anthony T. <an...@co...>]

Christopher J. Bottaro wrote:
> Anthony Tuininga wrote:
> 
> 
>>a) I've noted that there is
>>about a 10-15% performance penalty for writing code in Python for the
>>stuff I've written
> 
> 
> Right, that is expected and acceptable but a 100% much less a 500% penalty
> leads me to believe I'm doing something wrong.

Agreed. Be aware, however, that the overhead of fetching (creating 
tuples) and binding parameters (creating dictionaries) can be as much as 
300% in certain cases. I know this from my writing of CopyData which 
originally was about 3x slower than the original written in C++ but 
after writing executemanyprepared() and fetchraw() and the like I was 
able to get it to perform better than the C++ version.

>>b) The code "compiled_curs.execute(None, row[0], row[2]) is not valid
>>since the execute() method only accepts one argument (a list of
>>parameters) or a set of keyword parameters; what is going on here??
> 
> 
> Yeah sorry, I wrote that code snippet from memory.  It was originally
> suppose to be pseudocode, but since Python is said to be executable
> pseudocode, it was just easier to write it in Python since thats what I'm
> current used to...;)

Understood. :-)

>>c) Is there any reason you didn't use an iterator? Using the code "for
>>row in curs:" is more efficient than using the method fetchmany() and is
>>considerably easier to read as well. 
> 
> 
> I didn't know you could do that, infact I made a generator so I could say
> "for row in gen(curs):".

Sure. The generator will definitely be slower so you'll gain something 
here. :-)

>>d) Are you actually doing anything with the rows from the first cursor
>>other than using them as input to the second cursor? If so, you'd be far
>>better off to create a view that put those two queries together -- the
>>performance increase will be quite significant.
> 
> 
> I don't have the code in front of me and I wrote it almost a month ago, but
> I think the results are used in file output as well as input to the second
> cursor...among other things as well.
> 
> I actually know very little about databases, I'll have to research this
> "view" concept.

Good idea. :-)

>>e) If you really need performance and don't mind deviating from the DB
>>API to get it, there are a number of methods that can be used to avoid
>>the overhead of creating result tuples and binding parameters. This will
>>definitely bring the performance numbers more in line with the Pro*C
>>numbers but at the cost of less portable code.
> 
> 
> Where can I find documentation on this?

The documentation is included in the documentation for cx_Oracle with 
big caveats posted indicating that it deviates from the DB API. The 
documentation might be a little sparse, though, so you might want to 
acquire cx_OracleTools and look at CopyData.py and others of that nature 
to see how I used it myself. If you have further questions, feel free to 
ask.

>>f) What kind of times are we talking about here? Are you saying that one
>>method is 50 ms and the other is 10 ms? Or is it 50 minutes and 10
>>minutes? In other words, is this an operation that is being performed
>>many times? 40 ms is not a big deal if it happens a few times but a big
>>problem if it happens a few million times, right?
> 
> 
> Our programs run from 15 mins to hours.  In our development environment,
> they run for a little over a minute, where as the Pro*C runs in less than
> 10 seconds.  I've heard in the production environment, the Pro*C can take
> up to 30 mins and as we expand our stores, we can expect times to get over
> an hour.

Ok.

>>So, to summarize: I need more information in order to determine whether
>>this is a "real" performance problem or not. If you could give me some
>>actual code that I can execute to perform my own testing, that would be
>>great.
> 
> 
> I don't really know what else I could besides give you the actual code, but
> its so specific to our system and database, I don't know how you could run
> it.

That's usually the issue. You would have to provide an example that was 
similar to your code but that I could build and run myself.

> The general algorithms are the same for both the Pro*C and Python
> code...thats what is confusing me.  The Python code is actually a little
> more stripped down.  I'm almost positive its something on my end thats
> causing the problem.  When I have some time, I'm going to rewrite the Pro*C
> to exactly mimic the Python code, run some tests, and then get back to you.

Sounds good.

> Thank you for your reply, now I have a few leads to go on.

You're welcome.

>>Christopher J. Bottaro wrote:
>>
>>>I have some code structured like this:
>>>
>>>curs.execute(some_sql)
>>>rows.fetchmany()
>>>while rows:
>>>        for row in rows:
>>>                compiled_curs.execute(None, row[0], row[2])
>>>                more_rows = compiled_curs.fetchmany()
>>>                while more_rows:
>>>                        #do something with them
>>>                        more_rows = compiled_curs.fetchmany()
>>>        rows = curs.fetchmany()
>>># arraysize is set to 3000 for both cursors
>>>
>>>The problem is that it is so slow.  Yes, I realize that its not good to
>>>execute sql in a large for loop, but my problem is that this code is
>>>something like 5x as slow as the corresponding Pro*C code.
>>>
>>>Whats going on?  Am I just not using the cx_Oracle module efficiently? 
>>>Why is embedding sql in Pro*C code *so* much faster.
>>>
>>>Thanks for the help, I hate coding in Pro*C and C ever since I learned
>>>Python...please tell me I'm doing something wrong, I'd hate to go back to
>>>C!
>>>
>>>P.S.  I'm using cx_Oracle-4.1-beta1.
>>>
>>>
>>>
>>>-------------------------------------------------------
>>>The SF.Net email is sponsored by: Beat the post-holiday blues
>>>Get a FREE limited edition SourceForge.net t-shirt from ThinkGeek.
>>>It's fun and FREE -- well, almost....http://www.thinkgeek.com/sfshirt
>>>_______________________________________________
>>>cx-oracle-users mailing list
>>>cx-...@li...
>>>https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>>
> 
> 
> 
> 
> -------------------------------------------------------
> The SF.Net email is sponsored by: Beat the post-holiday blues
> Get a FREE limited edition SourceForge.net t-shirt from ThinkGeek.
> It's fun and FREE -- well, almost....http://www.thinkgeek.com/sfshirt
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users

-- 
Anthony Tuininga
an...@co...

Computronix
Distinctive Software. Real People.
Suite 200, 10216 - 124 Street NW
Edmonton, AB, Canada  T5N 4A3
Phone:	(780) 454-3700
Fax:	(780) 454-3838
http://www.computronix.com

Re: [cx-oracle-users] How to optimize (Pro*C is 5x faster)?

[Anthony T. <an...@co...>]

The only examples are to be found in CopyData.py which is part of the 
cx_OracleTools package found at http://starship.python.net/crew/atuining.

The documentation for its use (albeit sparse) is to be found in the 
online documentation found at the same web site or included in the 
cx_Oracle package. If you have some particular questions about it, feel 
free to ask. I'll see whether I can explain it better.

Jim Baker wrote:
> Anthony,
> 
> I'm curious about your point (e). Would this in particular be 
> executemanyprepared()? And if so, are there any examples of its use? 
> There was some discussion about it 
> (http://mail.python.org/pipermail/db-sig/2004-April/004026.html), but no 
> actual examples. Instead the discussion veered off to the use of 
> external tables, but for me, external tables have too many DBA issues to 
> actually use.
> 
> - Jim
> 
> 
> Anthony Tuininga wrote:
> 
>> A few points:
>>
>> ...
>>
>> e) If you really need performance and don't mind deviating from the DB 
>> API to get it, there are a number of methods that can be used to avoid 
>> the overhead of creating result tuples and binding parameters. This 
>> will definitely bring the performance numbers more in line with the 
>> Pro*C numbers but at the cost of less portable code.
> 
> 
> 
> 
> 
> -------------------------------------------------------
> The SF.Net email is sponsored by: Beat the post-holiday blues
> Get a FREE limited edition SourceForge.net t-shirt from ThinkGeek.
> It's fun and FREE -- well, almost....http://www.thinkgeek.com/sfshirt
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users

-- 
Anthony Tuininga
an...@co...

Computronix
Distinctive Software. Real People.
Suite 200, 10216 - 124 Street NW
Edmonton, AB, Canada  T5N 4A3
Phone:	(780) 454-3700
Fax:	(780) 454-3838
http://www.computronix.com

Re: [cx-oracle-users] cx_Oracle on 64 bit RH EL3.0

[D.R. B. <da...@as...>]

Hoi Joel,

The following two shared libraries that your cx_Oracle.so is referring to,
seem suspicious to me.

>         libclntsh.so.8.0 =>
> /dirs/home/jbard/smilesScript/libclntsh.so.8.0

>         libnnz10.so =>
> /usr/lib/oracle/10.1.0.2/client/lib/libnnz10.so
> (0x40ce9000)

The libclntsh.so is an Oracle 8 library and the libnnz10.so is an Oracle 10g
library.
You might try changing your $LD_LIBRARY_PATH to get this right.

                                                   Danny



On Tue, Jan 04, 2005 at 09:24:01AM -0800, Joel Bard wrote:
> Hi-
> 
> I've been unable to get cx_Oracle to import in the 64
> bit version of RH EL 3.0.
> 
> Here's the error:
> 
> Python 2.2.3 (#1, Aug  8 2003, 08:44:27)
> [GCC 3.2.3 20030502 (Red Hat Linux 3.2.3-13)] on
> linux2
> Type "help", "copyright", "credits" or "license" for
> more information.
> >>> import cx_Oracle
> Traceback (most recent call last):
>   File "<stdin>", line 1, in ?
> ImportError:
> /usr/lib/python2.2/site-packages/cx_Oracle.so: cannot
> open shared object file: No such file or directory
>  
> results of uname -a:
> Linux cela048010 2.4.21-20.ELsmp #1 SMP Wed Aug 18
> 20:34:58 EDT 2004 x86_64 x86_64 x86_64 GNU/Linux
> 
> I've tried: 
> $ ldd /usr/lib/python2.2/site-packages/cx_Oracle.so
>         libclntsh.so.8.0 =>
> /dirs/home/jbard/smilesScript/libclntsh.so.8.0
> (0x40026000)
>         libc.so.6 => /lib/tls/libc.so.6 (0x40bb1000)
>         libnnz10.so =>
> /usr/lib/oracle/10.1.0.2/client/lib/libnnz10.so
> (0x40ce9000)
>         libdl.so.2 => /lib/libdl.so.2 (0x40f1d000)
>         libm.so.6 => /lib/tls/libm.so.6 (0x40f20000)
>         libpthread.so.0 => /lib/tls/libpthread.so.0
> (0x40f42000)
>         libnsl.so.1 => /lib/libnsl.so.1 (0x40f53000)
>         /lib/ld-linux.so.2 => /lib/ld-linux.so.2
> (0x40000000)
> 
> So it looks like the libs are there.  Could it be have
> to do with the presence of both 32 and 64 bit versions
> of some libs?
> 
> Unfortunately, I have the client only Oracle setup so
> I haven't been able to build cx_Oracle from source.
> 
> I've had cx_Oracle running no problem on other, non 64
> bit, Redhat Linux machines.
> 
> Thanks!
> 
> Joel
> 
> 
> __________________________________________________
> Do You Yahoo!?
> Tired of spam?  Yahoo! Mail has the best spam protection around 
> http://mail.yahoo.com 
> 
> 
> -------------------------------------------------------
> The SF.Net email is sponsored by: Beat the post-holiday blues
> Get a FREE limited edition SourceForge.net t-shirt from ThinkGeek.
> It's fun and FREE -- well, almost....http://www.thinkgeek.com/sfshirt
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users

[cx-oracle-users] Re: How to optimize (Pro*C is 5x faster)?

[Christopher J. B. <cjb...@al...>]

Anthony Tuininga wrote:

> a) I've noted that there is
> about a 10-15% performance penalty for writing code in Python for the
> stuff I've written

Right, that is expected and acceptable but a 100% much less a 500% penalty
leads me to believe I'm doing something wrong.
 
> b) The code "compiled_curs.execute(None, row[0], row[2]) is not valid
> since the execute() method only accepts one argument (a list of
> parameters) or a set of keyword parameters; what is going on here??

Yeah sorry, I wrote that code snippet from memory.  It was originally
suppose to be pseudocode, but since Python is said to be executable
pseudocode, it was just easier to write it in Python since thats what I'm
current used to...;)

> c) Is there any reason you didn't use an iterator? Using the code "for
> row in curs:" is more efficient than using the method fetchmany() and is
> considerably easier to read as well. 

I didn't know you could do that, infact I made a generator so I could say
"for row in gen(curs):".

> d) Are you actually doing anything with the rows from the first cursor
> other than using them as input to the second cursor? If so, you'd be far
> better off to create a view that put those two queries together -- the
> performance increase will be quite significant.

I don't have the code in front of me and I wrote it almost a month ago, but
I think the results are used in file output as well as input to the second
cursor...among other things as well.

I actually know very little about databases, I'll have to research this
"view" concept.
 
> e) If you really need performance and don't mind deviating from the DB
> API to get it, there are a number of methods that can be used to avoid
> the overhead of creating result tuples and binding parameters. This will
> definitely bring the performance numbers more in line with the Pro*C
> numbers but at the cost of less portable code.

Where can I find documentation on this?

> f) What kind of times are we talking about here? Are you saying that one
> method is 50 ms and the other is 10 ms? Or is it 50 minutes and 10
> minutes? In other words, is this an operation that is being performed
> many times? 40 ms is not a big deal if it happens a few times but a big
> problem if it happens a few million times, right?

Our programs run from 15 mins to hours.  In our development environment,
they run for a little over a minute, where as the Pro*C runs in less than
10 seconds.  I've heard in the production environment, the Pro*C can take
up to 30 mins and as we expand our stores, we can expect times to get over
an hour.

> So, to summarize: I need more information in order to determine whether
> this is a "real" performance problem or not. If you could give me some
> actual code that I can execute to perform my own testing, that would be
> great.

I don't really know what else I could besides give you the actual code, but
its so specific to our system and database, I don't know how you could run
it.

The general algorithms are the same for both the Pro*C and Python
code...thats what is confusing me.  The Python code is actually a little
more stripped down.  I'm almost positive its something on my end thats
causing the problem.  When I have some time, I'm going to rewrite the Pro*C
to exactly mimic the Python code, run some tests, and then get back to you.

Thank you for your reply, now I have a few leads to go on.

> Christopher J. Bottaro wrote:
>> I have some code structured like this:
>> 
>> curs.execute(some_sql)
>> rows.fetchmany()
>> while rows:
>>         for row in rows:
>>                 compiled_curs.execute(None, row[0], row[2])
>>                 more_rows = compiled_curs.fetchmany()
>>                 while more_rows:
>>                         #do something with them
>>                         more_rows = compiled_curs.fetchmany()
>>         rows = curs.fetchmany()
>> # arraysize is set to 3000 for both cursors
>> 
>> The problem is that it is so slow.  Yes, I realize that its not good to
>> execute sql in a large for loop, but my problem is that this code is
>> something like 5x as slow as the corresponding Pro*C code.
>> 
>> Whats going on?  Am I just not using the cx_Oracle module efficiently? 
>> Why is embedding sql in Pro*C code *so* much faster.
>> 
>> Thanks for the help, I hate coding in Pro*C and C ever since I learned
>> Python...please tell me I'm doing something wrong, I'd hate to go back to
>> C!
>> 
>> P.S.  I'm using cx_Oracle-4.1-beta1.
>> 
>> 
>> 
>> -------------------------------------------------------
>> The SF.Net email is sponsored by: Beat the post-holiday blues
>> Get a FREE limited edition SourceForge.net t-shirt from ThinkGeek.
>> It's fun and FREE -- well, almost....http://www.thinkgeek.com/sfshirt
>> _______________________________________________
>> cx-oracle-users mailing list
>> cx-...@li...
>> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> 

Re: [cx-oracle-users] How to optimize (Pro*C is 5x faster)?

[Jim B. <ma...@ji...>]

Anthony,

I'm curious about your point (e). Would this in particular be 
executemanyprepared()? And if so, are there any examples of its use? 
There was some discussion about it 
(http://mail.python.org/pipermail/db-sig/2004-April/004026.html), but no 
actual examples. Instead the discussion veered off to the use of 
external tables, but for me, external tables have too many DBA issues to 
actually use.

- Jim


Anthony Tuininga wrote:

> A few points:
>
> ...
>
> e) If you really need performance and don't mind deviating from the DB 
> API to get it, there are a number of methods that can be used to avoid 
> the overhead of creating result tuples and binding parameters. This 
> will definitely bring the performance numbers more in line with the 
> Pro*C numbers but at the cost of less portable code.

Re: [cx-oracle-users] Re: cx_Oracle on 64 bit RH EL3.0

[Mihai I. <mi...@re...>]

Not sure if it helps any, but I am successfully running cx_Oracle in 64-bit
mode on a Fedora Core 3 amd64 box. I did build cx_Oracle myself against a
64-bit Oracle client install though (and what a nightmare to install it it
was...)

Misa

On Tue, Jan 04, 2005 at 11:35:40AM -0800, Joel Bard wrote:
> Hi again-
> 
> Thanks for the info.  Here's the strace output
> concerning the loading of cx_Oracle.  It does look
> like it needs to be recompiled.  Any advice on how to
> get the necessary Oracle includes?  I have lots of
> undefined names when I try to compile now.
> 
> Thanks,
> 
> Joel
> 
> open("/usr/lib/python2.2/site-packages/cx_Oracle.so",
> O_RDONLY) = 3
> fstat(3, {st_mode=S_IFREG|0755, st_size=56296, ...}) =
> 0
> open("/usr/lib/python2.2/site-packages/cx_Oracle.so",
> O_RDONLY) = 4
> read(4,
> "\177ELF\1\1\1\0\0\0\0\0\0\0\0\0\3\0\3\0\1\0\0\0\200)\0"...,
> 640) = 640
> close(4)                                = 0
> close(3)                                = 0
> write(2, "Traceback (most recent call last"...,
> 35Traceback (most recent call last):
> ) = 35
> open("test.py", O_RDONLY)               = 3
> write(2, "  File \"test.py\", line 1, in ?\n", 31 
> File "test.py", line 1, in ?
> ) = 31
> fstat(3, {st_mode=S_IFREG|0644, st_size=17, ...}) = 0
> mmap(NULL, 32768, PROT_READ|PROT_WRITE,
> MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x2a9566d000
> read(3, "import cx_Oracle\n", 32768)    = 17
> write(2, "    ", 4    )                     = 4
> write(2, "import cx_Oracle\n", 17import cx_Oracle
> )      = 17
> close(3)                                = 0
> munmap(0x2a9566d000, 32768)             = 0
> write(2, "ImportError", 11ImportError)             =
> 11
> write(2, ": ", 2: )                       = 2
> write(2, "/usr/lib/python2.2/site-packages"...,
> 104/usr/lib/python2.2/site-packages/cx_Oracle.so:
> cannot open shared object file: No such file or
> directory) = 104
> write(2, "\n", 1
> )                       = 1
> rt_sigaction(SIGINT, NULL, {0x451e30, [], 0x4000000},
> 8) = 0
> rt_sigaction(SIGINT, {SIG_DFL}, NULL, 8) = 0
> exit_group(1)                           = ?
> 
> 
> 
> 		
> __________________________________ 
> Do you Yahoo!? 
> Read only the mail you want - Yahoo! Mail SpamGuard. 
> http://promotions.yahoo.com/new_mail 
> 
> 
> -------------------------------------------------------
> The SF.Net email is sponsored by: Beat the post-holiday blues
> Get a FREE limited edition SourceForge.net t-shirt from ThinkGeek.
> It's fun and FREE -- well, almost....http://www.thinkgeek.com/sfshirt
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users

Re: [cx-oracle-users] Re: cx_Oracle on 64 bit RH EL3.0

[Anthony T. <an...@co...>]

You can't install the client for yourself? You can acquire the 
$ORACLE_HOME/rdbms/demo directory from another machine or you can beg 
for someone to provide an archive of that directory from their own 
machine. You do, however, need to make sure that the Oracle client 
version matches on source and destination.

Joel Bard wrote:
> Hi again-
> 
> Thanks for the info.  Here's the strace output
> concerning the loading of cx_Oracle.  It does look
> like it needs to be recompiled.  Any advice on how to
> get the necessary Oracle includes?  I have lots of
> undefined names when I try to compile now.
> 
> Thanks,
> 
> Joel
> 
> open("/usr/lib/python2.2/site-packages/cx_Oracle.so",
> O_RDONLY) = 3
> fstat(3, {st_mode=S_IFREG|0755, st_size=56296, ...}) =
> 0
> open("/usr/lib/python2.2/site-packages/cx_Oracle.so",
> O_RDONLY) = 4
> read(4,
> "\177ELF\1\1\1\0\0\0\0\0\0\0\0\0\3\0\3\0\1\0\0\0\200)\0"...,
> 640) = 640
> close(4)                                = 0
> close(3)                                = 0
> write(2, "Traceback (most recent call last"...,
> 35Traceback (most recent call last):
> ) = 35
> open("test.py", O_RDONLY)               = 3
> write(2, "  File \"test.py\", line 1, in ?\n", 31 
> File "test.py", line 1, in ?
> ) = 31
> fstat(3, {st_mode=S_IFREG|0644, st_size=17, ...}) = 0
> mmap(NULL, 32768, PROT_READ|PROT_WRITE,
> MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x2a9566d000
> read(3, "import cx_Oracle\n", 32768)    = 17
> write(2, "    ", 4    )                     = 4
> write(2, "import cx_Oracle\n", 17import cx_Oracle
> )      = 17
> close(3)                                = 0
> munmap(0x2a9566d000, 32768)             = 0
> write(2, "ImportError", 11ImportError)             =
> 11
> write(2, ": ", 2: )                       = 2
> write(2, "/usr/lib/python2.2/site-packages"...,
> 104/usr/lib/python2.2/site-packages/cx_Oracle.so:
> cannot open shared object file: No such file or
> directory) = 104
> write(2, "\n", 1
> )                       = 1
> rt_sigaction(SIGINT, NULL, {0x451e30, [], 0x4000000},
> 8) = 0
> rt_sigaction(SIGINT, {SIG_DFL}, NULL, 8) = 0
> exit_group(1)                           = ?
> 
> 
> 
> 		
> __________________________________ 
> Do you Yahoo!? 
> Read only the mail you want - Yahoo! Mail SpamGuard. 
> http://promotions.yahoo.com/new_mail 
> 
> 
> -------------------------------------------------------
> The SF.Net email is sponsored by: Beat the post-holiday blues
> Get a FREE limited edition SourceForge.net t-shirt from ThinkGeek.
> It's fun and FREE -- well, almost....http://www.thinkgeek.com/sfshirt
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users

-- 
Anthony Tuininga
an...@co...

Computronix
Distinctive Software. Real People.
Suite 200, 10216 - 124 Street NW
Edmonton, AB, Canada  T5N 4A3
Phone:	(780) 454-3700
Fax:	(780) 454-3838
http://www.computronix.com

[cx-oracle-users] Re: cx_Oracle on 64 bit RH EL3.0

[Joel B. <joe...@ya...>]

Hi again-

Thanks for the info.  Here's the strace output
concerning the loading of cx_Oracle.  It does look
like it needs to be recompiled.  Any advice on how to
get the necessary Oracle includes?  I have lots of
undefined names when I try to compile now.

Thanks,

Joel

open("/usr/lib/python2.2/site-packages/cx_Oracle.so",
O_RDONLY) = 3
fstat(3, {st_mode=S_IFREG|0755, st_size=56296, ...}) =
0
open("/usr/lib/python2.2/site-packages/cx_Oracle.so",
O_RDONLY) = 4
read(4,
"\177ELF\1\1\1\0\0\0\0\0\0\0\0\0\3\0\3\0\1\0\0\0\200)\0"...,
640) = 640
close(4)                                = 0
close(3)                                = 0
write(2, "Traceback (most recent call last"...,
35Traceback (most recent call last):
) = 35
open("test.py", O_RDONLY)               = 3
write(2, "  File \"test.py\", line 1, in ?\n", 31 
File "test.py", line 1, in ?
) = 31
fstat(3, {st_mode=S_IFREG|0644, st_size=17, ...}) = 0
mmap(NULL, 32768, PROT_READ|PROT_WRITE,
MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x2a9566d000
read(3, "import cx_Oracle\n", 32768)    = 17
write(2, "    ", 4    )                     = 4
write(2, "import cx_Oracle\n", 17import cx_Oracle
)      = 17
close(3)                                = 0
munmap(0x2a9566d000, 32768)             = 0
write(2, "ImportError", 11ImportError)             =
11
write(2, ": ", 2: )                       = 2
write(2, "/usr/lib/python2.2/site-packages"...,
104/usr/lib/python2.2/site-packages/cx_Oracle.so:
cannot open shared object file: No such file or
directory) = 104
write(2, "\n", 1
)                       = 1
rt_sigaction(SIGINT, NULL, {0x451e30, [], 0x4000000},
8) = 0
rt_sigaction(SIGINT, {SIG_DFL}, NULL, 8) = 0
exit_group(1)                           = ?



		
__________________________________ 
Do you Yahoo!? 
Read only the mail you want - Yahoo! Mail SpamGuard. 
http://promotions.yahoo.com/new_mail 

Re: [cx-oracle-users] cx_Oracle on 64 bit RH EL3.0

[Anthony T. <an...@co...>]

You might want to try using strace to track down which file it can't 
load. I suspect it is one of the other files located in an Oracle client 
installation but that should track down which one.

I suspect, though, that if you want to use a 64-bit Oracle installation, 
you will have to compile cx_Oracle yourself since the provided binary 
was built for 32-bit installations. If you get past the "no such file or 
directory" error, you'll probably be greeted with segfaults because of 
the changes in sizes of a number of structures -- you might be lucky but 
I wouldn't count on it.... :-)

Joel Bard wrote:
> Hi-
> 
> I've been unable to get cx_Oracle to import in the 64
> bit version of RH EL 3.0.
> 
> Here's the error:
> 
> Python 2.2.3 (#1, Aug  8 2003, 08:44:27)
> [GCC 3.2.3 20030502 (Red Hat Linux 3.2.3-13)] on
> linux2
> Type "help", "copyright", "credits" or "license" for
> more information.
> 
>>>>import cx_Oracle
> 
> Traceback (most recent call last):
>   File "<stdin>", line 1, in ?
> ImportError:
> /usr/lib/python2.2/site-packages/cx_Oracle.so: cannot
> open shared object file: No such file or directory
>  
> results of uname -a:
> Linux cela048010 2.4.21-20.ELsmp #1 SMP Wed Aug 18
> 20:34:58 EDT 2004 x86_64 x86_64 x86_64 GNU/Linux
> 
> I've tried: 
> $ ldd /usr/lib/python2.2/site-packages/cx_Oracle.so
>         libclntsh.so.8.0 =>
> /dirs/home/jbard/smilesScript/libclntsh.so.8.0
> (0x40026000)
>         libc.so.6 => /lib/tls/libc.so.6 (0x40bb1000)
>         libnnz10.so =>
> /usr/lib/oracle/10.1.0.2/client/lib/libnnz10.so
> (0x40ce9000)
>         libdl.so.2 => /lib/libdl.so.2 (0x40f1d000)
>         libm.so.6 => /lib/tls/libm.so.6 (0x40f20000)
>         libpthread.so.0 => /lib/tls/libpthread.so.0
> (0x40f42000)
>         libnsl.so.1 => /lib/libnsl.so.1 (0x40f53000)
>         /lib/ld-linux.so.2 => /lib/ld-linux.so.2
> (0x40000000)
> 
> So it looks like the libs are there.  Could it be have
> to do with the presence of both 32 and 64 bit versions
> of some libs?
> 
> Unfortunately, I have the client only Oracle setup so
> I haven't been able to build cx_Oracle from source.
> 
> I've had cx_Oracle running no problem on other, non 64
> bit, Redhat Linux machines.
> 
> Thanks!
> 
> Joel
> 
> 
> __________________________________________________
> Do You Yahoo!?
> Tired of spam?  Yahoo! Mail has the best spam protection around 
> http://mail.yahoo.com 
> 
> 
> -------------------------------------------------------
> The SF.Net email is sponsored by: Beat the post-holiday blues
> Get a FREE limited edition SourceForge.net t-shirt from ThinkGeek.
> It's fun and FREE -- well, almost....http://www.thinkgeek.com/sfshirt
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users

-- 
Anthony Tuininga
an...@co...

Computronix
Distinctive Software. Real People.
Suite 200, 10216 - 124 Street NW
Edmonton, AB, Canada  T5N 4A3
Phone:	(780) 454-3700
Fax:	(780) 454-3838
http://www.computronix.com

[cx-oracle-users] cx_Oracle on 64 bit RH EL3.0

[Joel B. <joe...@ya...>]

Hi-

I've been unable to get cx_Oracle to import in the 64
bit version of RH EL 3.0.

Here's the error:

Python 2.2.3 (#1, Aug  8 2003, 08:44:27)
[GCC 3.2.3 20030502 (Red Hat Linux 3.2.3-13)] on
linux2
Type "help", "copyright", "credits" or "license" for
more information.
>>> import cx_Oracle
Traceback (most recent call last):
  File "<stdin>", line 1, in ?
ImportError:
/usr/lib/python2.2/site-packages/cx_Oracle.so: cannot
open shared object file: No such file or directory
 
results of uname -a:
Linux cela048010 2.4.21-20.ELsmp #1 SMP Wed Aug 18
20:34:58 EDT 2004 x86_64 x86_64 x86_64 GNU/Linux

I've tried: 
$ ldd /usr/lib/python2.2/site-packages/cx_Oracle.so
        libclntsh.so.8.0 =>
/dirs/home/jbard/smilesScript/libclntsh.so.8.0
(0x40026000)
        libc.so.6 => /lib/tls/libc.so.6 (0x40bb1000)
        libnnz10.so =>
/usr/lib/oracle/10.1.0.2/client/lib/libnnz10.so
(0x40ce9000)
        libdl.so.2 => /lib/libdl.so.2 (0x40f1d000)
        libm.so.6 => /lib/tls/libm.so.6 (0x40f20000)
        libpthread.so.0 => /lib/tls/libpthread.so.0
(0x40f42000)
        libnsl.so.1 => /lib/libnsl.so.1 (0x40f53000)
        /lib/ld-linux.so.2 => /lib/ld-linux.so.2
(0x40000000)

So it looks like the libs are there.  Could it be have
to do with the presence of both 32 and 64 bit versions
of some libs?

Unfortunately, I have the client only Oracle setup so
I haven't been able to build cx_Oracle from source.

I've had cx_Oracle running no problem on other, non 64
bit, Redhat Linux machines.

Thanks!

Joel


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

Re: [cx-oracle-users] How to optimize (Pro*C is 5x faster)?

[Anthony T. <an...@co...>]

A few points:

a) C is always going to be faster than Python when coded using the same 
algorithms and data structures; in general the advantage of Python is 
the ability to use advanced algorithms and data structures with little 
effort compared to writing the same code in C; I've noted that there is 
about a 10-15% performance penalty for writing code in Python for the 
stuff I've written (although clearly it depends on what has been 
written) but I've also noted that due to the ability to write more 
advanced algorithms in Python some code I originally wrote in C++ was 
actually slower than the original code written in C/C++.

b) The code "compiled_curs.execute(None, row[0], row[2]) is not valid 
since the execute() method only accepts one argument (a list of 
parameters) or a set of keyword parameters; what is going on here??

c) Is there any reason you didn't use an iterator? Using the code "for 
row in curs:" is more efficient than using the method fetchmany() and is 
considerably easier to read as well. I've never checked the difference 
in performance, though -- the timeit module is your friend.... :-)

d) Are you actually doing anything with the rows from the first cursor 
other than using them as input to the second cursor? If so, you'd be far 
better off to create a view that put those two queries together -- the 
performance increase will be quite significant.

e) If you really need performance and don't mind deviating from the DB 
API to get it, there are a number of methods that can be used to avoid 
the overhead of creating result tuples and binding parameters. This will 
definitely bring the performance numbers more in line with the Pro*C 
numbers but at the cost of less portable code.

f) What kind of times are we talking about here? Are you saying that one 
method is 50 ms and the other is 10 ms? Or is it 50 minutes and 10 
minutes? In other words, is this an operation that is being performed 
many times? 40 ms is not a big deal if it happens a few times but a big 
problem if it happens a few million times, right?

So, to summarize: I need more information in order to determine whether 
this is a "real" performance problem or not. If you could give me some 
actual code that I can execute to perform my own testing, that would be 
great.

Christopher J. Bottaro wrote:
> I have some code structured like this:
> 
> curs.execute(some_sql)
> rows.fetchmany()
> while rows:
>         for row in rows:
>                 compiled_curs.execute(None, row[0], row[2])
>                 more_rows = compiled_curs.fetchmany()
>                 while more_rows:
>                         #do something with them
>                         more_rows = compiled_curs.fetchmany()
>         rows = curs.fetchmany()
> # arraysize is set to 3000 for both cursors
> 
> The problem is that it is so slow.  Yes, I realize that its not good to
> execute sql in a large for loop, but my problem is that this code is
> something like 5x as slow as the corresponding Pro*C code.
> 
> Whats going on?  Am I just not using the cx_Oracle module efficiently?  Why
> is embedding sql in Pro*C code *so* much faster.
> 
> Thanks for the help, I hate coding in Pro*C and C ever since I learned
> Python...please tell me I'm doing something wrong, I'd hate to go back to
> C!
> 
> P.S.  I'm using cx_Oracle-4.1-beta1.
> 
> 
> 
> -------------------------------------------------------
> The SF.Net email is sponsored by: Beat the post-holiday blues
> Get a FREE limited edition SourceForge.net t-shirt from ThinkGeek.
> It's fun and FREE -- well, almost....http://www.thinkgeek.com/sfshirt
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users

-- 
Anthony Tuininga
an...@co...

Computronix
Distinctive Software. Real People.
Suite 200, 10216 - 124 Street NW
Edmonton, AB, Canada  T5N 4A3
Phone:	(780) 454-3700
Fax:	(780) 454-3838
http://www.computronix.com