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

Re: [cx-oracle-users] How to deal with lost connection to Oracle ?

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

> If you (i) can't use ping (ii) find its internal implementation
> doesn't catch everything you classify as a connection error (iii) or
> don't want to use it because it causes a roundtrip to the DB and hence
> reduces scalability, then you could try explicitly testing error
> codes.  A fairly good set of codes to identify if an error is a
> connection error is given in the PHP_OCI_HANDLE_ERROR macro used by
> PHP error checking.  See
> http://svn.php.net/viewvc/php/php-src/branches/PHP_5_3/ext/oci8/php_oci8_int.h?view=markup
> There's an OCI attribute check (also used in PHP_OCI_HANDLE_ERROR)
> that does this test but cx_Oracle doesn't expose it.

That can be fixed. :-)

Just to be clear: this attribute is only set __after__ an attempt is
made to access the server. So at that point the typical solution would
be:

try:
    cursor.execute(some_sql)
except cx_Oracle.DatabaseError:
    if cursor.connection.connected:
        raise
    print "*** Server not connected: do something about it!"

The other option is to check this attribute when the error is being
created and store it on the error object instead -- but I suspect
exposing the object on the connection will be more flexible. Comments
anyone?

Anthony

Re: [cx-oracle-users] How to deal with lost connection to Oracle ?

[Christopher J. <chr...@or...>]

On 07/21/2010 10:50 AM, Anthony Tuininga wrote:

>>> On Wed, Jul 21, 2010 at 7:14 AM,<prz...@po...>  wrote:

>>>> Each Oracle has its own backup time. What I need is to
>>>> properly handle lost connections , e.g.:
>>>> minute 1: fetch data
>>>> minute 2: fetch data
>>>> minute 3: fetch data
>>>> minute 4: lost connection [1] (how to detect it ?) - backup started
>>>> minute 5: try to connect - failed [2] (how to detect it ?) - ongoing backup process
>>>> minute 6: try to connect - failed ...
>>>> minute 7: try to connect - failed ...
>>>> minute 8: try to connect - failed ...
>>>> minute 9: try to connect - successful ...
>>>> minute10: fetch data
>>>>
>>>> How can I detect [1] and [2] ?
>>>
>>> It depends what version of Oracle you are using. For 10.2 and up you
>>> can use connection.ping() which will attempt to contact the server and
>>> raise an error if the connection has dropped.
>>>
>>> try:
>>>      connection.ping()

If you (i) can't use ping (ii) find its internal implementation
doesn't catch everything you classify as a connection error (iii) or
don't want to use it because it causes a roundtrip to the DB and hence
reduces scalability, then you could try explicitly testing error
codes.  A fairly good set of codes to identify if an error is a
connection error is given in the PHP_OCI_HANDLE_ERROR macro used by
PHP error checking.  See
http://svn.php.net/viewvc/php/php-src/branches/PHP_5_3/ext/oci8/php_oci8_int.h?view=markup
There's an OCI attribute check (also used in PHP_OCI_HANDLE_ERROR)
that does this test but cx_Oracle doesn't expose it.

Chris

PS.  Off topic (but of interest to Python & Oracle users) is a free,
online Oracle Tuxedo training event includes a hands-on Python lab.
Learn more at http://www.oracle.com/goto/otnvdd
The American-time zone lab is tomorrow.


-- 
Email: chr...@or...
Tel:  +1 650 506 8630
Blog:  http://blogs.oracle.com/opal/
Free PHP Book: http://tinyurl.com/ugpomhome

Re: [cx-oracle-users] speeding up TB data download

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

Sure. You want something like this -- untested but it should give you
the right idea.

connection = cx_Oracle.Connection(....)
columnCursor = connection.cursor()
queryCursor = connection.cursor()
columnCursor.execute("""
        select table_name, column_name, data_type
        from user_tab_columns
        order by table_name, column_id""")
for tableName, columnName, dataType in columnCursor:
    if dataType in ("CHAR", "VARCHAR2"):
        selectItem = "length(%s)" % columnName
    elif dataType == "NUMBER":
        selectItem = "length(to_char(%s)) % columnName
    else:
        continue
    queryCursor.execute("select max(%s) from %s" % (selectItem, tableName))
    result, = queryCursor.fetchone()
    print "Maximum length for %s.%s is %s" % (tableName, columnName, result)

Anthony

On Mon, Jul 26, 2010 at 10:46 AM, Chuck White <chu...@ch...> wrote:
> Thanks for your response. Can you please send me examples which query those tables (user_tab_columns, all_tab_columns or dba_tab_columns)?
>
> ---- Anthony Tuininga <ant...@gm...> wrote:
>> Hi,
>>
>> Just a suggestion: avoid the parsing completely if you can do it. If
>> all you're trying to do is calculate the maximum size of fields, you
>> can easily write something that does this:
>>
>> select max(length(<COL_NAME>)) from <TABLE_NAME>
>>
>> or for numbers
>>
>> select max(length(to_char(<COL_NAME>)) from <TABLE_NAME>
>>
>> This sort of query is far more efficient and can be done independently
>> of your extracts. You can quite easily generate all of these queries
>> by reading from user_tab_columns, all_tab_columns or dba_tab_columns.
>> If you need help with that, let me know.
>>
>> Anthony
>
>

Re: [cx-oracle-users] speeding up TB data download

[Chuck W. <chu...@ch...>]

Thanks for your response. Can you please send me examples which query those tables (user_tab_columns, all_tab_columns or dba_tab_columns)?

---- Anthony Tuininga <ant...@gm...> wrote: 
> Hi,
> 
> Just a suggestion: avoid the parsing completely if you can do it. If
> all you're trying to do is calculate the maximum size of fields, you
> can easily write something that does this:
> 
> select max(length(<COL_NAME>)) from <TABLE_NAME>
> 
> or for numbers
> 
> select max(length(to_char(<COL_NAME>)) from <TABLE_NAME>
> 
> This sort of query is far more efficient and can be done independently
> of your extracts. You can quite easily generate all of these queries
> by reading from user_tab_columns, all_tab_columns or dba_tab_columns.
> If you need help with that, let me know.
> 
> Anthony

Re: [cx-oracle-users] speeding up TB data download

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

Hi,

Just a suggestion: avoid the parsing completely if you can do it. If
all you're trying to do is calculate the maximum size of fields, you
can easily write something that does this:

select max(length(<COL_NAME>)) from <TABLE_NAME>

or for numbers

select max(length(to_char(<COL_NAME>)) from <TABLE_NAME>

This sort of query is far more efficient and can be done independently
of your extracts. You can quite easily generate all of these queries
by reading from user_tab_columns, all_tab_columns or dba_tab_columns.
If you need help with that, let me know.

Anthony

On Sun, Jul 25, 2010 at 9:44 PM, Chuck White <chu...@ch...> wrote:
> Hello -- I am trying to export data from an Oracle 10 db to a Netezza db. The ORCL db has ~100tables (uncompressed ~ 5TB) with tables which range from a few kb to 1TB.
>
> I am using Python 2.7/cx_Oracle 5.0.4 to download the data as bzipped csv files and then loading in Netezza. The code to download data looks like this:
>
>    =====================
>    self.connection = cx_Oracle.Connection(conninfo)
>    ...
>    cursor = self.connection.cursor()
>    cursor.numbersAsStrings = True
>    cursor.arraysize = 500
>    ...
>    cursor.execute(query)
>
>    fil_out = bz2.BZ2File(lower("%s.csv.bz2"%fname), "wb")
>    csv.register_dialect("test", delimiter="|", quoting=csv.QUOTE_NONE, escapechar="\\", doublequote=False)
>    csv_out = csv.writer(fil_out, "test")
>
>    for row in cursor:
>        for n,(pars,cell) in enumerate(zip(col_parser,row)):
>        if cell:
>            ...
>        else:
>            ...
>        csv_out.writerow(rowparsed)
>    cursor.close()
>    =====================
>
> The reason the data is being parsed is that I am try to obtain the min and max field size so that I can get the NZ schema to be efficient.  Also, the reason I am using numbersAsStrings so that I can easily get the size of the numbers.
>
> The above method is transferring about 3.5GB/hour (uncompressed) or 0.6GB/hour (bz2 compressed). Using the multiprocessing library, I am able to spawn four processes on a quad-core machine which definitely help!
>
> I was wondering there way any way to speed this up further?
>
> Thanks.
>
>
> ------------------------------------------------------------------------------
> The Palm PDK Hot Apps Program offers developers who use the
> Plug-In Development Kit to bring their C/C++ apps to Palm for a share
> of $1 Million in cash or HP Products. Visit us here for more details:
> http://ad.doubleclick.net/clk;226879339;13503038;l?
> http://clk.atdmt.com/CRS/go/247765532/direct/01/
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

[cx-oracle-users] speeding up TB data download

[Chuck W. <chu...@ch...>]

Hello -- I am trying to export data from an Oracle 10 db to a Netezza db. The ORCL db has ~100tables (uncompressed ~ 5TB) with tables which range from a few kb to 1TB.

I am using Python 2.7/cx_Oracle 5.0.4 to download the data as bzipped csv files and then loading in Netezza. The code to download data looks like this:

    =====================
    self.connection = cx_Oracle.Connection(conninfo)
    ...
    cursor = self.connection.cursor()
    cursor.numbersAsStrings = True
    cursor.arraysize = 500
    ...
    cursor.execute(query)

    fil_out = bz2.BZ2File(lower("%s.csv.bz2"%fname), "wb")
    csv.register_dialect("test", delimiter="|", quoting=csv.QUOTE_NONE, escapechar="\\", doublequote=False)
    csv_out = csv.writer(fil_out, "test")
    
    for row in cursor:
        for n,(pars,cell) in enumerate(zip(col_parser,row)):
        if cell:
            ...
        else:
            ...
        csv_out.writerow(rowparsed)
    cursor.close()
    =====================

The reason the data is being parsed is that I am try to obtain the min and max field size so that I can get the NZ schema to be efficient.  Also, the reason I am using numbersAsStrings so that I can easily get the size of the numbers.

The above method is transferring about 3.5GB/hour (uncompressed) or 0.6GB/hour (bz2 compressed). Using the multiprocessing library, I am able to spawn four processes on a quad-core machine which definitely help!

I was wondering there way any way to speed this up further?

Thanks.

Re: [cx-oracle-users] cx_Oracle opens an excessive number of sessions

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

Hi,

I have never seen that behavior before and I have been using cx_Oracle
for well over a decade. Can you try the same thing with SQL*Plus? They
use the same base libraries so if there is a problem with the client
installation or other issue in your environment, that should show it
up. Perhaps try an older version of the instant client (11.1)?
Hopefully someone else may have seen this sort of behavior before and
can help you out further.

Anthony

On Thu, Jul 22, 2010 at 1:26 AM, Hannes Bretschneider
<hab...@gm...> wrote:
> I have a problem using instantclient 11.2 and cx_Oracle 5.0.3. When I query
> the database, cx_Oracle creates an excessive number of sessions to the
> database (about 70). I already crashed the database once, because I went
> over the allowed number of sessions (Oracle default is 150), after which my
> sysadmin increased the number of allowed connections to about 4000. However,
> he tells me I have to reduce the amount of resources I use and the number of
> sessions, so he can set the allowed number of connections back to something
> reasonable once the server goes into production.
> The one thing I tried was to set cursor.arraysize to something smaller, but
> it doesn't make any difference.
> This is was happens:
> import cx_Oracle
> db = cx_Oracle.connect(user, pass, dsn)   # Nothing bad happens here
> cursor = db.cursor()  # This is fine too
> cursor.execute('SELECT foo FROM bar")   # Opens an excessive number of
> sessions
> cursor.fetchone()                                      # Read some records
> cursor.fetchmany()
> cursor.close()                                           # Doesn't close the
> many sessions
> db.close()                                                #  Closes the
> sessions.
> --Hannes
> ------------------------------------------------------------------------------
> This SF.net email is sponsored by Sprint
> What will you do first with EVO, the first 4G phone?
> Visit sprint.com/first -- http://p.sf.net/sfu/sprint-com-first
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>

[cx-oracle-users] cx_Oracle opens an excessive number of sessions

[Hannes B. <hab...@gm...>]

I have a problem using instantclient 11.2 and cx_Oracle 5.0.3. When I query
the database, cx_Oracle creates an excessive number of sessions to the
database (about 70). I already crashed the database once, because I went
over the allowed number of sessions (Oracle default is 150), after which my
sysadmin increased the number of allowed connections to about 4000. However,
he tells me I have to reduce the amount of resources I use and the number of
sessions, so he can set the allowed number of connections back to something
reasonable once the server goes into production.

The one thing I tried was to set cursor.arraysize to something smaller, but
it doesn't make any difference.

This is was happens:

import cx_Oracle

db = cx_Oracle.connect(user, pass, dsn)   # Nothing bad happens here
cursor = db.cursor()  # This is fine too

cursor.execute('SELECT foo FROM bar")   # Opens an excessive number of
sessions

cursor.fetchone()                                      # Read some records
cursor.fetchmany()

cursor.close()                                           # Doesn't close the
many sessions
db.close()                                                #  Closes the
sessions.

--Hannes

Re: [cx-oracle-users] cx_Oracle.connect suddenly exit

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

Hi, what version of cx_Oracle are you using? Can you connect
successfully using SQL*Plus? Since you built it, can you run gdb on it
and determine where it is crashing?

Anthony

On Wed, Jul 21, 2010 at 2:30 PM, Gleiwer Montoya <gl...@gm...> wrote:
> Hello there,
>
> i'm having the next problem, whenever i run the cx_Oracle.connect function,
> it throws me out of the script with exit value 1.
>
> Like this...
>
> root@gleiwer# python
> Python 2.6.5 (r265:79063, Apr 16 2010, 13:09:56)
> [GCC 4.4.3] on linux2
> Type "help", "copyright", "credits" or "license" for more information.
>>>> import cx_Oracle
>>>> dsn_tns=cx_Oracle.makedsn("10.1.1.1","1522","XE")
>>>> conexiondb=cx_Oracle.connect("username","password",dsn_tns)
> root@gleiwer#
>
> i compiled cx_Oracle with instantclient_11_2
> the environment variables are set, can anybody help me to solve this out,
> i've been trying for weeks.
>
> --
> Gleiwer Montoya
> gl...@gm...
>
> ------------------------------------------------------------------------------
> This SF.net email is sponsored by Sprint
> What will you do first with EVO, the first 4G phone?
> Visit sprint.com/first -- http://p.sf.net/sfu/sprint-com-first
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>

[cx-oracle-users] cx_Oracle.connect suddenly exit

[Gleiwer M. <gl...@gm...>]

Hello there,

i'm having the next problem, whenever i run the cx_Oracle.connect function,
it throws me out of the script with exit value 1.

Like this...

root@gleiwer# python
Python 2.6.5 (r265:79063, Apr 16 2010, 13:09:56)
[GCC 4.4.3] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import cx_Oracle
>>> dsn_tns=cx_Oracle.makedsn("10.1.1.1","1522","XE")
>>> conexiondb=cx_Oracle.connect("username","password",dsn_tns)
root@gleiwer#

i compiled cx_Oracle with instantclient_11_2
the environment variables are set, can anybody help me to solve this out,
i've been trying for weeks.

-- 
Gleiwer Montoya
gl...@gm...

Re: [cx-oracle-users] How to deal with lost connection to Oracle ?

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

On Wed, Jul 21, 2010 at 9:03 AM,  <prz...@po...> wrote:
> On Wed, Jul 21, 2010 at 08:05:13AM -0600, Anthony Tuininga wrote:
>> On Wed, Jul 21, 2010 at 7:14 AM,  <prz...@po...> wrote:
>> > Hello,
>> >
>> > (I am python and cx_oracle beginner so please excuse me ...)
>> >
>> > I am going to write small python application which will fetch some small
>> > data from many Oracles _once_ a minute for 24h/365.
>> > These Oracles are backuped offline (shutdown, snapshot, startup).
>>
>> Hmm, any reason these aren't backed up online? That would make the
>> databases available 24/7. That doesn't eliminate the problem since
>> they could go down for other reasons but it eliminates most of the
>> problems.
>
> I know. But this is beyond my ability - our DBAs didn't make decision to change it.

Ok. Such is life. :-)

I have a set of tools called cx_OracleDBATools that make performing
things like backups, restores whether online or offline very simple
and the same whether on Linux or Windows. I am hoping to release a new
version of those tools tomorrow.

>> > Each Oracle has its own backup time. What I need is to
>> > properly handle lost connections , e.g.:
>> > minute 1: fetch data
>> > minute 2: fetch data
>> > minute 3: fetch data
>> > minute 4: lost connection [1] (how to detect it ?) - backup started
>> > minute 5: try to connect - failed [2] (how to detect it ?) - ongoing backup process
>> > minute 6: try to connect - failed ...
>> > minute 7: try to connect - failed ...
>> > minute 8: try to connect - failed ...
>> > minute 9: try to connect - successful ...
>> > minute10: fetch data
>> >
>> > How can I detect [1] and [2] ?
>>
>> It depends what version of Oracle you are using. For 10.2 and up you
>> can use connection.ping() which will attempt to contact the server and
>> raise an error if the connection has dropped.
>>
>> try:
>>     connection.ping()
>> except cx_Oracle.DatabaseError:
>>     print "*** Connection dropped ***"
>>
>> The same goes for attempting to connect
>>
>> try:
>>     connection = cx_Oracle.Connection(connectString)
>> except cx_Oracle.DatabaseError:
>>     print "*** Connection failed to establish ***"
>>
>> Note that those catch any database exception, not just "the database
>> is not available". If you need to be more careful about which
>> exceptions to catch then you need something like this:
>>
>> try:
>>     connnection = cx_Oracle.Connection(connectString)
>> except cx_Oracle.DatabaseError, e:
>>     error, = e.args
>>     if error.code == 1017:
>>         print "*** Invalid username/password ***"
>>     else:
>>         print "*** Some other error:", error.message, "***"
>
> I need just availability of the database. Which exception would be
> the most useful ?

That's a good question. This is what I use in cx_OracleDBATools to
determine if a database is up.

def IsAvailable(self):
    try:
        cursor = self.connection.cursor()
        return True
    except self.driver.DatabaseError as errorInfo:
        errorInfo, = errorInfo.args
        if errorInfo.code in (1034, 12500):    # Oracle not started up
            return False
        elif errorInfo.code == 1031: # insufficient privileges
            return True
        elif errorInfo.code in (1089, 1090):  # shutdown in progress
            return True
        raise

self.driver is a particular flavor of cx_Oracle imported dynamically
in order to support different versions of Oracle at the same time.

Hope that helps.

Anthony

Re: [cx-oracle-users] How to deal with lost connection to Oracle ?

[<prz...@po...>]

On Wed, Jul 21, 2010 at 08:05:13AM -0600, Anthony Tuininga wrote:
> On Wed, Jul 21, 2010 at 7:14 AM,  <prz...@po...> wrote:
> > Hello,
> >
> > (I am python and cx_oracle beginner so please excuse me ...)
> >
> > I am going to write small python application which will fetch some small
> > data from many Oracles _once_ a minute for 24h/365.
> > These Oracles are backuped offline (shutdown, snapshot, startup).
> 
> Hmm, any reason these aren't backed up online? That would make the
> databases available 24/7. That doesn't eliminate the problem since
> they could go down for other reasons but it eliminates most of the
> problems.

I know. But this is beyond my ability - our DBAs didn't make decision to change it.

> > Each Oracle has its own backup time. What I need is to
> > properly handle lost connections , e.g.:
> > minute 1: fetch data
> > minute 2: fetch data
> > minute 3: fetch data
> > minute 4: lost connection [1] (how to detect it ?) - backup started
> > minute 5: try to connect - failed [2] (how to detect it ?) - ongoing backup process
> > minute 6: try to connect - failed ...
> > minute 7: try to connect - failed ...
> > minute 8: try to connect - failed ...
> > minute 9: try to connect - successful ...
> > minute10: fetch data
> >
> > How can I detect [1] and [2] ?
> 
> It depends what version of Oracle you are using. For 10.2 and up you
> can use connection.ping() which will attempt to contact the server and
> raise an error if the connection has dropped.
> 
> try:
>     connection.ping()
> except cx_Oracle.DatabaseError:
>     print "*** Connection dropped ***"
> 
> The same goes for attempting to connect
> 
> try:
>     connection = cx_Oracle.Connection(connectString)
> except cx_Oracle.DatabaseError:
>     print "*** Connection failed to establish ***"
> 
> Note that those catch any database exception, not just "the database
> is not available". If you need to be more careful about which
> exceptions to catch then you need something like this:
> 
> try:
>     connnection = cx_Oracle.Connection(connectString)
> except cx_Oracle.DatabaseError, e:
>     error, = e.args
>     if error.code == 1017:
>         print "*** Invalid username/password ***"
>     else:
>         print "*** Some other error:", error.message, "***"

I need just availability of the database. Which exception would be
the most useful ?


Regards
Przemyslaw Bak (przemol)





























----------------------------------------------------------------------
Wyprzedaz do -70%!!! Wejdz na Endo.pl
http://linkint.pl/f2791

Re: [cx-oracle-users] How to deal with lost connection to Oracle ?

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

On Wed, Jul 21, 2010 at 7:14 AM,  <prz...@po...> wrote:
> Hello,
>
> (I am python and cx_oracle beginner so please excuse me ...)
>
> I am going to write small python application which will fetch some small
> data from many Oracles _once_ a minute for 24h/365.
> These Oracles are backuped offline (shutdown, snapshot, startup).

Hmm, any reason these aren't backed up online? That would make the
databases available 24/7. That doesn't eliminate the problem since
they could go down for other reasons but it eliminates most of the
problems.

> Each Oracle has its own backup time. What I need is to
> properly handle lost connections , e.g.:
> minute 1: fetch data
> minute 2: fetch data
> minute 3: fetch data
> minute 4: lost connection [1] (how to detect it ?) - backup started
> minute 5: try to connect - failed [2] (how to detect it ?) - ongoing backup process
> minute 6: try to connect - failed ...
> minute 7: try to connect - failed ...
> minute 8: try to connect - failed ...
> minute 9: try to connect - successful ...
> minute10: fetch data
>
> How can I detect [1] and [2] ?

It depends what version of Oracle you are using. For 10.2 and up you
can use connection.ping() which will attempt to contact the server and
raise an error if the connection has dropped.

try:
    connection.ping()
except cx_Oracle.DatabaseError:
    print "*** Connection dropped ***"

The same goes for attempting to connect

try:
    connection = cx_Oracle.Connection(connectString)
except cx_Oracle.DatabaseError:
    print "*** Connection failed to establish ***"

Note that those catch any database exception, not just "the database
is not available". If you need to be more careful about which
exceptions to catch then you need something like this:

try:
    connnection = cx_Oracle.Connection(connectString)
except cx_Oracle.DatabaseError, e:
    error, = e.args
    if error.code == 1017:
        print "*** Invalid username/password ***"
    else:
        print "*** Some other error:", error.message, "***"

Hope that helps.

Anthony

[cx-oracle-users] How to deal with lost connection to Oracle ?

[<prz...@po...>]

Hello,

(I am python and cx_oracle beginner so please excuse me ...)

I am going to write small python application which will fetch some small
data from many Oracles _once_ a minute for 24h/365.
These Oracles are backuped offline (shutdown, snapshot, startup).
Each Oracle has its own backup time. What I need is to
properly handle lost connections , e.g.:
minute 1: fetch data
minute 2: fetch data
minute 3: fetch data
minute 4: lost connection [1] (how to detect it ?) - backup started
minute 5: try to connect - failed [2] (how to detect it ?) - ongoing backup process
minute 6: try to connect - failed ...
minute 7: try to connect - failed ...
minute 8: try to connect - failed ...
minute 9: try to connect - successful ...
minute10: fetch data

How can I detect [1] and [2] ?


Regards
Przemyslaw Bak (przemol)





























----------------------------------------------------------------------
Duzi chlopcy lubia wampiry i krew.
http://linkint.pl/f2718

[cx-oracle-users] cx_Oracle 5.0.4

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

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://cx-oracle.sourceforge.net


What's new?

1) Added support for Python 2.7.

2) Added support for new parameter (port) for subscription() call
which allows the client to specify the listening port for callback
notifications from the database server. Thanks to Geoffrey Weber for
the initial patch.

3) Fixed compilation under Oracle 9i.

4) Fixed a few error messages.

Re: [cx-oracle-users] Performance problems with stored procedure calls

[Henning v. B. <hen...@ar...>]

> Hi there,
> I am using cx_Oracle 5.0.3 on Python 2.4 with Oracle Instant client 11.0.7
> speaking to an Oracle 11gR2 RAC system.
> 
> The code:
> import cx_Oracle
> conn = cx_Oracle.connect(user='********',password='********',dsn='********')
> cur = conn.cursor()
> cur.arraysize = 50
> 
> for i in range(100):
>   params =
> ('/tdls-1/i99/data/2010/rogfile1-%d'%(i),12345678,'tdls-1','container','i99','mx123-4',0)
> 
>   print params
>   cur.callproc('PKG_STORAGED.add_file',parameters=params)
> 
> Performance of this code has been highly variable, from 300 seconds down to
> 1.6 seconds. A similar simple loop run via SQLPlus finishes in about 1.5
> seconds reliably. I have tried changing cur.arraysize with no real change
> observed.
> 
> When the code is running slowly, it stays that way for quite some time; yet
> I find that this morning it is running quickly. Before you state the
> obvious, during a period of slowness, SQLPlus is still able to execute the
> loop in under 2 seconds.
> 
> I have also noticed that when it is slow if I try to change the code to use
> executemany() instead then that too is slow, seeming to hang (strace shows
> though that it is still active).
> 
> My question is, therefore, what factors could there be specific to cx_Oracle
> that would be affecting performance so dramatically? The machine is
> relatively high spec with 4 cores and 4G of RAM and is otherwise idle.
> 
> Hoping you can help!
> 
> Roger

You should use session tracing and TKPROF to see what's really going on.
HTH
Henning

Re: [cx-oracle-users] AIX Oracle 11gR2 Patchlevel 11.2.0.1.1

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

I can't say for certain but I suspect that you can put that stuff
(DESCRIPTION=.....) in tnsnames.ora or use an LDAP server to host that
name resolution. Then it becomes "user/pwd@the_name_you_chose_to_use".

On Fri, Jul 9, 2010 at 1:47 AM, BOCKHOLD, Lars (MEAIS)
<Lar...@ai...> wrote:
>> Hi,
>> I am using
>> cx_Oracle 5.0.2 und python 2.6.4 to access Oracle 10g (Client HPUX B.11.11 U 9000/785 4042425557)
>> works fine.
>>
>> We will upgrade our Database to AIX Oracle 11gR2 Patchlevel 11.2.0.1.1with load balance.
>> Do I have to change something ?
>> How do Iconnect now ?
>>
>> Example:
>>  JAVA-App
>> ===============
>> JDBC-Thin-Clients (JAVA 1.5.x) folgender oder ähnlicher Ansatz funktioniert (=> reine Konfigurations- u. keine Codeänderung):
>> Ansatz:
>> jdbc:oracle:thin:@<HOST>:1521:<SID>
>> must be now:
>> jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=on)
>> (ADDRESS=(PROTOCOL=TCP)(HOST=host1) (PORT=1521))
>> (ADDRESS=(PROTOCOL=TCP)(HOST=host2) (PORT=1521))
>> (CONNECT_DATA=(SERVICE_NAME=service)))
>>
>> How do I realize this in cx_Oracle Connect ?
>> Old is now and works fine:
>> """
>> export ORACLE_HOME=/do/pro/P/oracle/102
>> export NLS_CHARACTERSET=WE8ISO8859p9
>> export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
>> export TNS_ADMIN=$MEAIS/tools/DXM-methods/etc/network/admin
>> dbConnection = cx_Oracle.connect(<user>/<passwd>@odexn.world)
>> """
>> But now I want the Load Balancing like in java explained above.
>>
>> Possible ?
>> Do I need to built cx_Oracle 5.0.3 ?
>>
>> Thanks in advance
>> Lars
>>
>>
>>
>>
>> Lars Bockhold
>> on behalf of Misdorf Engineering and IT-Solutions GmbH & Co. KG
>> Subcontractor for Functional and Data Support Germany - IDOB2
>> (AIRBUS Operations GmbH)
>>
>> Phone: +49 (0) 40 743 67837
>> Phone: +49 (0) 421 538 6121
>> Mobile: +49 (0) 151 58246174
>> Mailto:Lar...@ai...
>>
>>
>>
>
> The information in this e-mail is confidential. The contents may not be disclosed or used by anyone other than the addressee. Access to this e-mail by anyone else is unauthorised.
> If you are not the intended recipient, please notify Airbus immediately and delete this e-mail.
> Airbus cannot accept any responsibility for the accuracy or completeness of this e-mail as it has been sent over public networks. If you have any concerns over the content of this message or its Accuracy or Integrity, please contact Airbus immediately.
> All outgoing e-mails from Airbus are checked using regularly updated virus scanning software but you should take whatever measures you deem to be appropriate to ensure that this message and any attachments are virus free.
>
>
> ------------------------------------------------------------------------------
> This SF.net email is sponsored by Sprint
> What will you do first with EVO, the first 4G phone?
> Visit sprint.com/first -- http://p.sf.net/sfu/sprint-com-first
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

Re: [cx-oracle-users] Performance problems with stored procedure calls

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

Hi,

Sorry I can't really be of much help. I've never seen this sort of
behavior myself. All I can suggest is looking at the status of the
query when it is taking a long time (is it ACTIVE or INACTIVE -- in
Oracle doing something or not in Oracle doing something else) or
performing a trace to see where the holdup is. Another suggestion to
confirm is that you are using the same Oracle home for your cx_Oracle
work and your SQL*Plus work -- sometimes that can bite you. :-) And if
you find something intriguing, please let us know!

Anthony

On Fri, Jul 9, 2010 at 2:32 AM, Roger Downing <rog...@gm...> wrote:
> Hi there,
> I am using cx_Oracle 5.0.3 on Python 2.4 with Oracle Instant client 11.0.7
> speaking to an Oracle 11gR2 RAC system.
>
> The code:
> import cx_Oracle
> conn = cx_Oracle.connect(user='********',password='********',dsn='********')
> cur = conn.cursor()
> cur.arraysize = 50
>
> for i in range(100):
>   params =
> ('/tdls-1/i99/data/2010/rogfile1-%d'%(i),12345678,'tdls-1','container','i99','mx123-4',0)
>
>   print params
>   cur.callproc('PKG_STORAGED.add_file',parameters=params)
>
> Performance of this code has been highly variable, from 300 seconds down to
> 1.6 seconds. A similar simple loop run via SQLPlus finishes in about 1.5
> seconds reliably. I have tried changing cur.arraysize with no real change
> observed.
>
> When the code is running slowly, it stays that way for quite some time; yet
> I find that this morning it is running quickly. Before you state the
> obvious, during a period of slowness, SQLPlus is still able to execute the
> loop in under 2 seconds.
>
> I have also noticed that when it is slow if I try to change the code to use
> executemany() instead then that too is slow, seeming to hang (strace shows
> though that it is still active).
>
> My question is, therefore, what factors could there be specific to cx_Oracle
> that would be affecting performance so dramatically? The machine is
> relatively high spec with 4 cores and 4G of RAM and is otherwise idle.
>
> Hoping you can help!
>
> Roger
>
> ------------------------------------------------------------------------------
> This SF.net email is sponsored by Sprint
> What will you do first with EVO, the first 4G phone?
> Visit sprint.com/first -- http://p.sf.net/sfu/sprint-com-first
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>

Re: [cx-oracle-users] installation proplems

[Christian K. <ckl...@no...>]

Hi

this does not help...

i give up...

thanks christian

> On 7/9/10 2:35 AM, Christian Klinger wrote:
>> Hi again,
>>
>> my osx version is 10.6.4.
>> and i have 2.66 intel core processor.
>>
>> If i remove the clntsh from libs i can build cx_Oracle. But when i
>> try to import cx_Oracle i run into this error.
>>
>> any ideas...
>
> hmm... let's check 3 things:
>
> Did you follow the steps below, so that everything is
> in /usr/lib?
>
> What is the result of running /usr/bin/sqlplus?
>
> Which instantclient (exact name of file) did you install?
> There is a *_x86* and *_x64*.  (x64 is the one you need)
>
> ------------------------------------------------------------------------------
> This SF.net email is sponsored by Sprint
> What will you do first with EVO, the first 4G phone?
> Visit sprint.com/first -- http://p.sf.net/sfu/sprint-com-first

Re: [cx-oracle-users] installation proplems

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

On 7/9/10 2:35 AM, Christian Klinger wrote:
> Hi again,
>
> my osx version is 10.6.4.
> and i have 2.66 intel core processor.
>
> If i remove the clntsh from libs i can build cx_Oracle. But when i
> try to import cx_Oracle i run into this error.
>
> any ideas...

hmm... let's check 3 things:

Did you follow the steps below, so that everything is
in /usr/lib?

What is the result of running /usr/bin/sqlplus?

Which instantclient (exact name of file) did you install?
There is a *_x86* and *_x64*.  (x64 is the one you need)

Re: [cx-oracle-users] installation proplems

[Christian K. <ckl...@no...>]

Hi again,

my osx version is 10.6.4.
and i have 2.66 intel core processor.

If i remove the clntsh from libs i can build cx_Oracle. But when i
try to import cx_Oracle i run into this error.

any ideas...

yeti-ii:fernlehrgang christian$ ../bin/python
Python 2.6.5 (r265:79063, Jun 22 2010, 15:40:32)
[GCC 4.2.1 (Apple Inc. build 5646)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
 >>> import cx_Oracle
Traceback (most recent call last):
   File "<stdin>", line 1, in <module>
   File "build/bdist.macosx-10.4-x86_64/egg/cx_Oracle.py", line 7, in 
<module>
   File "build/bdist.macosx-10.4-x86_64/egg/cx_Oracle.py", line 6, in 
__bootstrap__
ImportError: 
dlopen(/Users/christian/.python-eggs/cx_Oracle-5.0.3-py2.6-macosx-10.4-x86_64.egg-tmp/cx_Oracle.so, 
2): Symbol not found: _OCIAttrGet
   Referenced from: 
/Users/christian/.python-eggs/cx_Oracle-5.0.3-py2.6-macosx-10.4-x86_64.egg-tmp/cx_Oracle.so
   Expected in: dynamic lookup

 >>>



> On 7/7/10 6:44 AM, Christian Klinger wrote:
>> Hi,
>>
>> i try to install cx_oracle on my new mac-book-pro.
>
> what version of OSX?
>
>>
>> I have installed instantclient_10_2/   basic + sdk
>
> I was able to get everything built quite easily if I
> installed the instantclient into /usr/{include,lib,bin}
>
> I documented the steps here:
>
> http://stackoverflow.com/questions/684352/installing-oracle-instantclient-on-mac-os-x-without-setting-environment-variables
>
> HTH!
>
> ------------------------------------------------------------------------------
> This SF.net email is sponsored by Sprint
> What will you do first with EVO, the first 4G phone?
> Visit sprint.com/first -- http://p.sf.net/sfu/sprint-com-first

[cx-oracle-users] Performance problems with stored procedure calls

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

Hi there,
I am using cx_Oracle 5.0.3 on Python 2.4 with Oracle Instant client 11.0.7
speaking to an Oracle 11gR2 RAC system.

The code:
import cx_Oracle
conn = cx_Oracle.connect(user='********',password='********',dsn='********')
cur = conn.cursor()
cur.arraysize = 50

for i in range(100):
  params =
('/tdls-1/i99/data/2010/rogfile1-%d'%(i),12345678,'tdls-1','container','i99','mx123-4',0)

  print params
  cur.callproc('PKG_STORAGED.add_file',parameters=params)

Performance of this code has been highly variable, from 300 seconds down to
1.6 seconds. A similar simple loop run via SQLPlus finishes in about 1.5
seconds reliably. I have tried changing cur.arraysize with no real change
observed.

When the code is running slowly, it stays that way for quite some time; yet
I find that this morning it is running quickly. Before you state the
obvious, during a period of slowness, SQLPlus is still able to execute the
loop in under 2 seconds.

I have also noticed that when it is slow if I try to change the code to use
executemany() instead then that too is slow, seeming to hang (strace shows
though that it is still active).

My question is, therefore, what factors could there be specific to cx_Oracle
that would be affecting performance so dramatically? The machine is
relatively high spec with 4 cores and 4G of RAM and is otherwise idle.

Hoping you can help!

Roger

Re: [cx-oracle-users] AIX Oracle 11gR2 Patchlevel 11.2.0.1.1

[BOCKHOLD, L. \(MEAIS\) <Lar...@ai...>]

> Hi,
> I am using 
> cx_Oracle 5.0.2 und python 2.6.4 to access Oracle 10g (Client HPUX B.11.11 U 9000/785 4042425557)
> works fine.
> 
> We will upgrade our Database to AIX Oracle 11gR2 Patchlevel 11.2.0.1.1with load balance.
> Do I have to change something ?
> How do Iconnect now ?
> 
> Example:
>  JAVA-App
> ===============
> JDBC-Thin-Clients (JAVA 1.5.x) folgender oder ähnlicher Ansatz funktioniert (=> reine Konfigurations- u. keine Codeänderung):
> Ansatz:
> jdbc:oracle:thin:@<HOST>:1521:<SID> 
> must be now: 
> jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=on)
> (ADDRESS=(PROTOCOL=TCP)(HOST=host1) (PORT=1521))
> (ADDRESS=(PROTOCOL=TCP)(HOST=host2) (PORT=1521))
> (CONNECT_DATA=(SERVICE_NAME=service)))
> 
> How do I realize this in cx_Oracle Connect ?
> Old is now and works fine:
> """
> export ORACLE_HOME=/do/pro/P/oracle/102
> export NLS_CHARACTERSET=WE8ISO8859p9
> export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
> export TNS_ADMIN=$MEAIS/tools/DXM-methods/etc/network/admin
> dbConnection = cx_Oracle.connect(<user>/<passwd>@odexn.world)
> """
> But now I want the Load Balancing like in java explained above.
> 
> Possible ?
> Do I need to built cx_Oracle 5.0.3 ?
> 
> Thanks in advance
> Lars
> 
> 
> 
> 
> Lars Bockhold
> on behalf of Misdorf Engineering and IT-Solutions GmbH & Co. KG
> Subcontractor for Functional and Data Support Germany - IDOB2 
> (AIRBUS Operations GmbH) 
> 
> Phone: +49 (0) 40 743 67837
> Phone: +49 (0) 421 538 6121
> Mobile: +49 (0) 151 58246174
> Mailto:Lar...@ai... 
> 
> 
> 

The information in this e-mail is confidential. The contents may not be disclosed or used by anyone other than the addressee. Access to this e-mail by anyone else is unauthorised.
If you are not the intended recipient, please notify Airbus immediately and delete this e-mail.
Airbus cannot accept any responsibility for the accuracy or completeness of this e-mail as it has been sent over public networks. If you have any concerns over the content of this message or its Accuracy or Integrity, please contact Airbus immediately.
All outgoing e-mails from Airbus are checked using regularly updated virus scanning software but you should take whatever measures you deem to be appropriate to ensure that this message and any attachments are virus free.

Re: [cx-oracle-users] ValueError: string data too large

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

Hi,

I have done this for you but you could have done it yourself.

http://cx-oracle.sourceforge.net

has a link to the mailing list and so does every e-mail sent to the
list (at the very bottom). Follow that link and then follow the
instructions on that page.

Anthony

On Thu, Jul 8, 2010 at 1:02 AM,  <yoa...@or...> wrote:
>
> Hello,
>
> Could you drop me of the email list please.
>
> Thanks.
>
> Best Regards,
>
> Yoann Royer
>
>
> -----Message d'origine-----
> De : Mark Harrison [mailto:mh...@pi...]
> Envoyé : jeudi 8 juillet 2010 00:45
> À : cx-...@li...
> Objet : Re: [cx-oracle-users] ValueError: string data too large
>
> On 7/7/10 3:39 PM, Anthony Tuininga wrote:
>> Hi Mark,
>>
>> This error can occur if you bind a string shorter than 4000 characters
>> in one iteration and then bind a string larger than 4000 characters in
>> another iteration. Or if you use setinputsizes() to specify a
>> character string and then try to bind more than 4000 characters. The
>> 4000 characters (bytes really) can be different if you use a multibyte
>> character set on the client/server, of course. If you pass a string
>> longer than 4000 characters on the first iteration then cx_Oracle will
>> use a "long" variable -- and Oracle has a number of restrictions on
>> those so I would use CLOB personally.
>>
>> You can use cursor.var() if you like, or you can simply use
>> setinputsizes() to specify that you want to use a CLOB in a particular
>> position and then simply pass the string directly.
>>
>> HTH,
>> Anthony
>
> Thanks Anthony, it does!
>
>
>
>
>>
>> On Wed, Jul 7, 2010 at 2:43 PM, Mark Harrison<mh...@pi...>  wrote:
>>> A coworker has asked me this...  he's occasionally geting a
>>> "ValueError: string data too large" when calling cursor.execute().
>>>
>>>
>>> we're going to try something like this:
>>>
>>>      clob = cursor.var(cx_Oracle.CLOB)
>>>      clob.setvalue(0, contents)
>>>
>>> and see if that works.  But I'm a bit confused, since the code below
>>> works the majority of the  time.
>>>
>>> 1.  are we on the right track to change to use clob.setvalue?
>>>
>>> 2.  should the simple string code work at all?
>>>
>>> Many TIA!!!
>>>
>>> Mark
>>>
>>> ----------------------------
>>>
>>>
>>>      Mark,
>>>        This is the "ValueError: string data too large" error message
>>>        that I brought up in our last meeting.  It only happens
>>>        sometimes, but causes grief when it does.  In this instance
>>>        the value for the CLOB column called "contents" in our
>>>        foo table is around 130,000 characters.
>>>        There are other rows in that table with longer values up to
>>>        179507, so clearly the limit (if there is a limit at all) is
>>>        not being exceeded.
>>>
>>>      This one happened a little before 3:29 today.  Could you ask
>>>      the DBAs to look into this?
>>>
>>>
>>> 370             contents = simplejson.dumps(newworkspace).encode('ascii')
>>> 371             sql = "UPDATE foo SET name=:1, owner=:2, viewers=:3, contents=:4 WHERE workspaceid=:5"
>>> 372             values = [name, owner, viewers, contents, workspaceid]
>>> 373             self._cursor.execute(sql, values)
>>>
>>>
>>>
>>>   >    File "/data/foo.py", line 373, in saveWorkspace
>>>   >      self._cursor.execute(sql, values)
>>>   >
>>>   >  ValueError: string data too large
>>>
>>> ---------------------------------------------------------------------
>>> --------- This SF.net email is sponsored by Sprint What will you do
>>> first with EVO, the first 4G phone?
>>> Visit sprint.com/first -- http://p.sf.net/sfu/sprint-com-first
>>> _______________________________________________
>>> cx-oracle-users mailing list
>>> cx-...@li...
>>> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>>>
>>
>> ----------------------------------------------------------------------
>> -------- This SF.net email is sponsored by Sprint What will you do
>> first with EVO, the first 4G phone?
>> Visit sprint.com/first -- http://p.sf.net/sfu/sprint-com-first
>> _______________________________________________
>> cx-oracle-users mailing list
>> cx-...@li...
>> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>
> ------------------------------------------------------------------------------
> This SF.net email is sponsored by Sprint What will you do first with EVO, the first 4G phone?
> Visit sprint.com/first -- http://p.sf.net/sfu/sprint-com-first
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
> *********************************
> This message and any attachments (the "message") are confidential and intended solely for the addressees.
> Any unauthorised use or dissemination is prohibited.
> Messages are susceptible to alteration.
> France Telecom Group shall not be liable for the message if altered, changed or falsified.
> If you are not the intended addressee of this message, please cancel it immediately and inform the sender.
> ********************************
>
>
> ------------------------------------------------------------------------------
> This SF.net email is sponsored by Sprint
> What will you do first with EVO, the first 4G phone?
> Visit sprint.com/first -- http://p.sf.net/sfu/sprint-com-first
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

Re: [cx-oracle-users] Problems with UTF8 and unicode version of cx_Oracle

[Stefan D. <ste...@de...>]

Hi,

swapped module again (unicode to normal version of cx_Oracle) and now 
finally I can insert special characters.
I used bind variables in other parts of my python script, but not always.
If I had known this
   > Oracle always considers SQL statements as strings (= VARCHAR),
earlier... >_<

Thank you very much!

Regards,
Stefan


> Even if the column is defined as NVARCHAR2, you pass it in the SQL
> statement as a string literal.
>
> Oracle always considers SQL statements as strings (= VARCHAR),
> so the special character will be lost if it cannot be encoded in the
> NLS_CHAR_CHARACTERSET.
>
> You should not pass the values as string literals in the query, but
> use bind variables instead:
>      cursor.execute("INSERT INTO conf (CID, title) VALUES (4, :1)", [title])
> Now the query only contains plain ascii characters; the unicode value is passed
> through a bind variable which will respect all characters.
>