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

Re: [cx-oracle-users] "expecting string, unicode or buffer object" on executemany

[avinash n. <avi...@or...>]

Hello Ricardo,

Can you send a sample insert statement (unicode)  similar to one you are 
using.
Also can you inform which python/ cx-oracle and oracle version you are 
using.

Best Regards,
Avinash
On 9/23/2014 5:32 PM, Oliosi Riccardo wrote:
>
> Hello everybody,
>
> I have a strange error:
>
> When I call:
>
> cursor.executemany( self.__sql, self.__buffer )
>
> where
>
> self.__sql is an insert statement (unicode) and self.__buffer is a 
> list of tuple,  the execution throws an exception with  the following 
> message:
>
> "expecting string, unicode or buffer object"
>
> But if I substitute the executemany with a cycle:
>
> for item in   self.__buffer:
>
> cursor.execute(self.__sql, item)
>
> the script works without any problems.
>
> Can anyone help me?
>
> Thanks in advance.
>
> Riccardo
>
> ______________________
>
> *Riccardo Oliosi*
>
> SIDFramework
>
> /Gruppo CAD IT/
>
> Tel. +39.045.82.11.111
>
> Fax +39.045.82.11.110
>
> ol...@ca... <mailto:ol...@ca...>__
>
> www.cadit.it <http://www.cadit.it/>__
>
> *CAD IT S.p.A.*
>
> Sede legale: Via Torricelli, 44/A - 37136 Verona
>
> C.S. EUR 4.669.600,00 i.v.
>
> C.F./P.IVA e n. iscr. R.I. di VR 01992770238
>
> R.E.A. di VR n. 210441
>
> ______________________
>
> Questo messaggio, con gli eventuali allegati, contiene informazioni 
> riservate o confidenziali. Chiunque lo ricevesse pur non essendone il 
> destinatario è pregato di segnalarlo immediatamente al mittente, di 
> cancellarlo dal proprio sistema e di non copiarlo, diffonderne il 
> contenuto o utilizzarlo in alcun modo.
>
> This e-mail and any files transmitted with it are confidential or 
> privileged. If you are not the intended recipient of this e-mail, 
> please notify the sender and delete it from your system immediately: 
> you should not copy, disclose or use either it or its attachments in 
> any way whatsoever.
>
>
>
> ------------------------------------------------------------------------------
> Meet PCI DSS 3.0 Compliance Requirements with EventLog Analyzer
> Achieve PCI DSS 3.0 Compliant Status with Out-of-the-box PCI DSS Reports
> Are you Audit-Ready for PCI DSS 3.0 Compliance? Download White paper
> Comply to PCI DSS 3.0 Requirement 10 and 11.5 with EventLog Analyzer
> http://pubads.g.doubleclick.net/gampad/clk?id=154622311&iu=/4140/ostg.clktrk
>
>
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users

[cx-oracle-users] "expecting string, unicode or buffer object" on executemany

[Oliosi R. <ol...@ca...>]

Hello everybody,
I have a strange error:

When I call:
cursor.executemany( self.__sql, self.__buffer )

where
self.__sql is an insert statement (unicode) and self.__buffer is a list of tuple,  the execution throws an exception with  the following message:
“expecting string, unicode or buffer object”

But if I substitute the executemany with a cycle:

for item in   self.__buffer:
                cursor.execute(self.__sql, item)

the script works without any problems.

Can anyone help me?
Thanks in advance.

Riccardo
______________________

Riccardo Oliosi
SIDFramework
Gruppo CAD IT
Tel. +39.045.82.11.111
Fax +39.045.82.11.110
ol...@ca...<mailto:ol...@ca...>
www.cadit.it<http://www.cadit.it/>

CAD IT S.p.A.
Sede legale: Via Torricelli, 44/A - 37136 Verona
C.S. € 4.669.600,00 i.v.
C.F./P.IVA e n. iscr. R.I. di VR 01992770238
R.E.A. di VR n. 210441
______________________

Questo messaggio, con gli eventuali allegati, contiene informazioni riservate o confidenziali. Chiunque lo ricevesse pur non essendone il destinatario è pregato di segnalarlo immediatamente al mittente, di cancellarlo dal proprio sistema e di non copiarlo, diffonderne il contenuto o utilizzarlo in alcun modo.

This e-mail and any files transmitted with it are confidential or privileged. If you are not the intended recipient of this e-mail, please notify the sender and delete it from your system immediately: you should not copy, disclose or use either it or its attachments in any way whatsoever.

Re: [cx-oracle-users] acquire() vs. connect(): DRCP cclass and purity params

[avinash n. <avi...@or...>]

Hello,

1. acquire() accepts the two params and passes it on to PyObject_Call() 
which takes connection type as parameter.
It goes ahead and creates and returns a connection object.
Thus these two parameters are valid for acquire() as well.

2. acquire() and connect() will work similarly in most cases.
connect() can also be called without the credentials  since you had 
passed in SessionPool.

If you see any inappropriate behavior post it in the forum.

Regards,
Avinash


On 8/2/2014 7:42 AM, Jim Freeman wrote:
> I see the two params cclass and purity in the documentation for 
> connect(), but not for acquire(). Acquire() accepts the two params but 
> doesn't do anything with them (I checked the C cource code).
> So should I be using be using connect() instead of acquire()?  If I 
> use connect(), do I need to pass in credentials and DSN, since I 
> already passed them when creating SessionPool?
>
>
> ------------------------------------------------------------------------------
> Want fast and easy access to all the code in your enterprise? Index and
> search up to 200,000 lines of code with a free copy of Black Duck
> Code Sight - the same software that powers the world's largest code
> search on Ohloh, the Black Duck Open Hub! Try it now.
> http://p.sf.net/sfu/bds
>
>
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users

Re: [cx-oracle-users] Close Cursor - Best Practise

[avinash n. <avi...@or...>]

Hello Anurag,

The Cursor object can be reused many times for different types of 
queries and DMLs.
Unless you require to concurrently execute multiple SQLs/DMLs you can 
serially use the same cursor for multiple queries.
In fact once you close a cursor object you will have to create a new one.
If you see any inconsistency please post it in the forum.

Best Regards,
Avinash

On 9/3/2014 9:49 PM, Anurag Chourasia wrote:
> I will be executing 4 SQL Queries one after the other.
>
> Each query will return a good number of records (> 50000 Records)
>
> I am planning to use the same cursor variable for all the queries to 
> fetch the records into.
>
> What is the best practise w.r.t closing of the Cursor?
>
> Shall i close the cursor after executing and fetching records for each 
> query?
> Or
> Shall i use the same cursor without closing and fetch the records and 
> close it only once after having executed all 4 queries?
>
> Regards,
> Guddu
>
>
> ------------------------------------------------------------------------------
> Slashdot TV.
> Video for Nerds.  Stuff that matters.
> http://tv.slashdot.org/
>
>
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users

[cx-oracle-users] Close Cursor - Best Practise

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

I will be executing 4 SQL Queries one after the other.

Each query will return a good number of records (> 50000 Records)

I am planning to use the same cursor variable for all the queries to fetch
the records into.

What is the best practise w.r.t closing of the Cursor?

Shall i close the cursor after executing and fetching records for each
query?
Or
Shall i use the same cursor without closing and fetch the records and close
it only once after having executed all 4 queries?

Regards,
Guddu

Re: [cx-oracle-users] cx_Oracle messes up signals

[Gaius H. <ga...@ga...>]

‎This is not cx_Oracle - it is the Oracle client libraries.


Gaius




From: CRV§ADER//KY
Sent: Wednesday, 6 August 2014 12:35
To: cx-...@li...
Reply To: cx-...@li...
Subject: [cx-oracle-users] cx_Oracle messes up signals


Hi all,
     I'm using cx_Oracle 5.1.2 on Python 2.6 on Linux.

I found out that cx_Oracles interferes heavily with signal handling.
This is BAD, because it causes mysterious failures later on in the
code. It personally took me A LOT of time to figure what the heck was
going on.

Expected behaviour would be for cx_Oracle to respect whatever signal
handling is used in the code invoking the library. If you _really_
need to touch signals, that should be done in a subprocess.

Thanks


proof-of-concept code:

#!/usr/bin/env python

import cx_Oracle
import subprocess
import signal
import time
import os
from threading import Thread

def signalme():
    time.sleep(2)
    os.kill(os.getpid(), signal.SIGWINCH)

# Completely ignore SIGWINCH
signal.signal(signal.SIGWINCH, signal.SIG_IGN)
signal.siginterrupt(signal.
SIGWINCH, False)

print "do a syscall"
Thread(target=signalme).start()
subprocess.check_call("sleep 5", shell=True)

print "run oracle"
connection = cx_Oracle.connect('myuser', 'mypass', 'mytns')
connection.close()

print "do a syscall"
Thread(target=signalme).start()
subprocess.check_call("sleep 5", shell=True)

print "all done!"



Output:
do a syscall
run oracle
do a syscall
Traceback (most recent call last):
  File "./oracle_test.py", line 30, in <module>
    subprocess.check_call("sleep 5", shell=True)
  File "/algo/algos2dev4/AlgoOne-EC/third-party-apps/python/lib/python2.6/subprocess.py",
line 457, in check_call
    retcode = call(*popenargs, **kwargs)
  File "/algo/algos2dev4/AlgoOne-EC/third-party-apps/python/lib/python2.6/subprocess.py",
line 444, in call
    return Popen(*popenargs, **kwargs).wait()
  File "/algo/algos2dev4/AlgoOne-EC/third-party-apps/python/lib/python2.6/subprocess.py",
line 1123, in wait
    pid, sts = os.waitpid(self.pid, 0)
OSError: [Errno 4] Interrupted system call

------------------------------------------------------------------------------
Infragistics Professional
Build stunning WinForms apps today!
Reboot your WinForms applications with our WinForms controls.
Build a bridge from your legacy apps to the future.
http://pubads.g.doubleclick.net/gampad/clk?id=153845071&iu=/4140/ostg.clktrk
_______________________________________________
cx-oracle-users mailing list
cx-...@li...
https://lists.sourceforge.net/lists/listinfo/cx-oracle-users

[cx-oracle-users] cx_Oracle messes up signals

[CRV§ADER//KY <cru...@gm...>]

Hi all,
     I'm using cx_Oracle 5.1.2 on Python 2.6 on Linux.

I found out that cx_Oracles interferes heavily with signal handling.
This is BAD, because it causes mysterious failures later on in the
code. It personally took me A LOT of time to figure what the heck was
going on.

Expected behaviour would be for cx_Oracle to respect whatever signal
handling is used in the code invoking the library. If you _really_
need to touch signals, that should be done in a subprocess.

Thanks


proof-of-concept code:

#!/usr/bin/env python

import cx_Oracle
import subprocess
import signal
import time
import os
from threading import Thread

def signalme():
    time.sleep(2)
    os.kill(os.getpid(), signal.SIGWINCH)

# Completely ignore SIGWINCH
signal.signal(signal.SIGWINCH, signal.SIG_IGN)
signal.siginterrupt(signal.
SIGWINCH, False)

print "do a syscall"
Thread(target=signalme).start()
subprocess.check_call("sleep 5", shell=True)

print "run oracle"
connection = cx_Oracle.connect('myuser', 'mypass', 'mytns')
connection.close()

print "do a syscall"
Thread(target=signalme).start()
subprocess.check_call("sleep 5", shell=True)

print "all done!"



Output:
do a syscall
run oracle
do a syscall
Traceback (most recent call last):
  File "./oracle_test.py", line 30, in <module>
    subprocess.check_call("sleep 5", shell=True)
  File "/algo/algos2dev4/AlgoOne-EC/third-party-apps/python/lib/python2.6/subprocess.py",
line 457, in check_call
    retcode = call(*popenargs, **kwargs)
  File "/algo/algos2dev4/AlgoOne-EC/third-party-apps/python/lib/python2.6/subprocess.py",
line 444, in call
    return Popen(*popenargs, **kwargs).wait()
  File "/algo/algos2dev4/AlgoOne-EC/third-party-apps/python/lib/python2.6/subprocess.py",
line 1123, in wait
    pid, sts = os.waitpid(self.pid, 0)
OSError: [Errno 4] Interrupted system call

[cx-oracle-users] acquire() vs. connect(): DRCP cclass and purity params

[Jim F. <jim...@sb...>]

I see the two params cclass and purity in the documentation for connect(), but not for acquire().  Acquire() accepts the two params but doesn't do anything with them (I checked the C cource code).
 
So should I be using be using connect() instead of acquire()?  If I use connect(), do I need to pass in credentials and DSN, since I already passed them when creating SessionPool?

Re: [cx-oracle-users] Possible to make SessionPool.acquire() block until connection available?

[Jim F. <jim...@sb...>]

Oops, meant to say "Same behavior if I use SPOOL_ATTRVAL_NOWAIT..." in 3rd paragraph below.

From: Jim Freeman <jim...@sb...>
To: "cx-...@li..." <cx-...@li...> 
Sent: Friday, August 1, 2014 3:58 PM
Subject: Re: [cx-oracle-users] Possible to make SessionPool.acquire() block until connection available?



Thanks Vladimir and Krishna!
Using cx_Oracle.SPOOL_ATTRVAL_WAIT causes the behavior I need --  acquire() now blocks until a connection is free.
 
Still one minor issue: say I have 4 threads and a 2 connection pool.  In my test app the threads do a block of acquire/sleep/release in a tight loop of N iterations.  Threads 3 and 4 get completely starved until Threads 1 and 2 have finished all N iterations.  If I put a 1 second sleep after the release, then the connections are fair-shared between the 4 requesters.  
 
Same behavior if I use SPOOL_ATTRVAL_WAIT and put a sempahore(2) around the acquire/sleep/release block.  That's because there's no fair semaphore (i.e. FIFO) in Python.
 
In my real app, there is lots of other code to execute between connection aquires, so hopefully thread starvation in acquire() won't be a real issue for me.
From: Krishna Mohan IV <kri...@or...>
To: cx-...@li... 
Sent: Thursday, July 31, 2014 10:40 PM
Subject: Re: [cx-oracle-users] Possible to make SessionPool.acquire() block until connection available?

You need to use cx_Oracle.SPOOL_ATTRVAL_WAIT
in the call 
cx_Oracle.SessionPool(user, password, database, min, max, increment[, connectiontype, threaded, getmode=cx_Oracle.SPOOL_ATTRVAL_NOWAIT, homogeneous=True])

The default seems to be NOWAIT.

thanks,
krishna

On 8/1/2014 10:55 AM, Vladimir Ryabtsev wrote:

Hello, Jim, 
>
>Did you consider using language synchronization primitives, like threading.Semaphore or multiprocessing.Semaphore?
>
>
>2014-08-01 0:46 GMT+04:00 Jim Freeman <jim...@sb...>:
>
>I have an app which has more worker threads than my session pool max setting, because these threads spend most of their time doing wotk other than DB calls.
>>
>>My problem is when acquire() is called and there are no available connections, a cx_Oracle.DatabaseError is immediately thrown.  Ideally I'd like acquire() to block up to some timeout period, and if multiple threads are blocked on acquire(), they received connections in the same order they blocked.
>>
>>Maybe the way the Oracle DRCP interface works (which I know little about) prevents this?
>>------------------------------------------------------------------------------
>>Infragistics Professional
>>Build stunning WinForms apps today!
>>Reboot your WinForms applications with our WinForms controls.
>>Build a bridge from your legacy apps to the future.
>>http://pubads.g.doubleclick.net/gampad/clk?id=153845071&iu=/4140/ostg.clktrk
>>_______________________________________________
>>cx-oracle-users mailing list
>>cx-...@li...
>>https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>>
>>
>
> 
>
>------------------------------------------------------------------------------
Want fast and easy access to all the code in your enterprise? Index and
search up to 200,000 lines of code with a free copy of Black Duck
Code Sight - the same software that powers the world's largest code
search on Ohloh, the Black Duck Open Hub! Try it now. http://p.sf.net/sfu/bds
> 
>
>_______________________________________________
cx-oracle-users mailing list cx-...@li... https://lists.sourceforge.net/lists/listinfo/cx-oracle-users 


------------------------------------------------------------------------------
Want fast and easy access to all the code in your enterprise? Index and
search up to 200,000 lines of code with a free copy of Black Duck
Code Sight - the same software that powers the world's largest code
search on Ohloh, the Black Duck Open Hub! Try it now.
http://p.sf.net/sfu/bds

_______________________________________________
cx-oracle-users mailing list
cx-...@li...
https://lists.sourceforge.net/lists/listinfo/cx-oracle-users




------------------------------------------------------------------------------
Want fast and easy access to all the code in your enterprise? Index and
search up to 200,000 lines of code with a free copy of Black Duck
Code Sight - the same software that powers the world's largest code
search on Ohloh, the Black Duck Open Hub! Try it now.
http://p.sf.net/sfu/bds

_______________________________________________
cx-oracle-users mailing list
cx-...@li...
https://lists.sourceforge.net/lists/listinfo/cx-oracle-users

Re: [cx-oracle-users] Possible to make SessionPool.acquire() block until connection available?

[Jim F. <jim...@sb...>]

Thanks Vladimir and Krishna!
Using cx_Oracle.SPOOL_ATTRVAL_WAIT causes the behavior I need --  acquire() now blocks until a connection is free.
 
Still one minor issue: say I have 4 threads and a 2 connection pool.  In my test app the threads do a block of acquire/sleep/release in a tight loop of N iterations.  Threads 3 and 4 get completely starved until Threads 1 and 2 have finished all N iterations.  If I put a 1 second sleep after the release, then the connections are fair-shared between the 4 requesters.  
 
Same behavior if I use SPOOL_ATTRVAL_WAIT and put a sempahore(2) around the acquire/sleep/release block.  That's because there's no fair semaphore (i.e. FIFO) in Python.
 
In my real app, there is lots of other code to execute between connection aquires, so hopefully thread starvation in acquire() won't be a real issue for me.
From: Krishna Mohan IV <kri...@or...>
To: cx-...@li... 
Sent: Thursday, July 31, 2014 10:40 PM
Subject: Re: [cx-oracle-users] Possible to make SessionPool.acquire() block until connection available?

You need to use cx_Oracle.SPOOL_ATTRVAL_WAIT
in the call 
cx_Oracle.SessionPool(user, password, database, min, max, increment[, connectiontype, threaded, getmode=cx_Oracle.SPOOL_ATTRVAL_NOWAIT, homogeneous=True])

The default seems to be NOWAIT.

thanks,
krishna

On 8/1/2014 10:55 AM, Vladimir Ryabtsev wrote:

Hello, Jim, 
>
>Did you consider using language synchronization primitives, like threading.Semaphore or multiprocessing.Semaphore?
>
>
>2014-08-01 0:46 GMT+04:00 Jim Freeman <jim...@sb...>:
>
>I have an app which has more worker threads than my session pool max setting, because these threads spend most of their time doing wotk other than DB calls.
>>
>>My problem is when acquire() is called and there are no available connections, a cx_Oracle.DatabaseError is immediately thrown.  Ideally I'd like acquire() to block up to some timeout period, and if multiple threads are blocked on acquire(), they received connections in the same order they blocked.
>>
>>Maybe the way the Oracle DRCP interface works (which I know little about) prevents this?
>>------------------------------------------------------------------------------
>>Infragistics Professional
>>Build stunning WinForms apps today!
>>Reboot your WinForms applications with our WinForms controls.
>>Build a bridge from your legacy apps to the future.
>>http://pubads.g.doubleclick.net/gampad/clk?id=153845071&iu=/4140/ostg.clktrk
>>_______________________________________________
>>cx-oracle-users mailing list
>>cx-...@li...
>>https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>>
>>
>
> 
>
>------------------------------------------------------------------------------
Want fast and easy access to all the code in your enterprise? Index and
search up to 200,000 lines of code with a free copy of Black Duck
Code Sight - the same software that powers the world's largest code
search on Ohloh, the Black Duck Open Hub! Try it now. http://p.sf.net/sfu/bds
> 
>
>_______________________________________________
cx-oracle-users mailing list cx-...@li... https://lists.sourceforge.net/lists/listinfo/cx-oracle-users 


------------------------------------------------------------------------------
Want fast and easy access to all the code in your enterprise? Index and
search up to 200,000 lines of code with a free copy of Black Duck
Code Sight - the same software that powers the world's largest code
search on Ohloh, the Black Duck Open Hub! Try it now.
http://p.sf.net/sfu/bds

_______________________________________________
cx-oracle-users mailing list
cx-...@li...
https://lists.sourceforge.net/lists/listinfo/cx-oracle-users

Re: [cx-oracle-users] Possible to make SessionPool.acquire() block until connection available?

[Krishna M. IV <kri...@or...>]

You need to use cx_Oracle. 
<http://cx-oracle.readthedocs.org/en/latest/module.html#constants>SPOOL_ATTRVAL_WAIT 
<http://cx-oracle.readthedocs.org/en/latest/module.html#constants>
in the call
cx_Oracle.SessionPool(/user/, /password/, /database/, /min/, /max/, 
/increment/[, /connectiontype/, /threaded/, 
/getmode=cx_Oracle.SPOOL_ATTRVAL_NOWAIT/, /homogeneous=True/])

The default seems to be NOWAIT.

thanks,
krishna

On 8/1/2014 10:55 AM, Vladimir Ryabtsev wrote:
> Hello, Jim,
>
> Did you consider using language synchronization primitives, like 
> threading.Semaphore or multiprocessing.Semaphore?
>
>
> 2014-08-01 0:46 GMT+04:00 Jim Freeman <jim...@sb... 
> <mailto:jim...@sb...>>:
>
>     I have an app which has more worker threads than my session pool
>     max setting, because these threads spend most of their time doing
>     wotk other than DB calls.
>     My problem is when acquire() is called and there are no available
>     connections, a cx_Oracle.DatabaseError is immediately thrown.
>     Ideally I'd like acquire() to block up to some timeout period, and
>     if multiple threads are blocked on acquire(), they received
>     connections in the same order they blocked.
>     Maybe the way the Oracle DRCP interface works (which I know little
>     about) prevents this?
>
>     ------------------------------------------------------------------------------
>     Infragistics Professional
>     Build stunning WinForms apps today!
>     Reboot your WinForms applications with our WinForms controls.
>     Build a bridge from your legacy apps to the future.
>     http://pubads.g.doubleclick.net/gampad/clk?id=153845071&iu=/4140/ostg.clktrk
>     _______________________________________________
>     cx-oracle-users mailing list
>     cx-...@li...
>     <mailto:cx-...@li...>
>     https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>
>
>
> ------------------------------------------------------------------------------
> Want fast and easy access to all the code in your enterprise? Index and
> search up to 200,000 lines of code with a free copy of Black Duck
> Code Sight - the same software that powers the world's largest code
> search on Ohloh, the Black Duck Open Hub! Try it now.
> http://p.sf.net/sfu/bds
>
>
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users

Re: [cx-oracle-users] Possible to make SessionPool.acquire() block until connection available?

[Vladimir R. <gre...@gm...>]

Hello, Jim,

Did you consider using language synchronization primitives, like
threading.Semaphore or multiprocessing.Semaphore?


2014-08-01 0:46 GMT+04:00 Jim Freeman <jim...@sb...>:

> I have an app which has more worker threads than my session pool max
> setting, because these threads spend most of their time doing wotk other
> than DB calls.
>
> My problem is when acquire() is called and there are no available
> connections, a cx_Oracle.DatabaseError is immediately thrown.  Ideally I'd
> like acquire() to block up to some timeout period, and if multiple threads
> are blocked on acquire(), they received connections in the same order they
> blocked.
>
> Maybe the way the Oracle DRCP interface works (which I know little about)
> prevents this?
>
>
> ------------------------------------------------------------------------------
> Infragistics Professional
> Build stunning WinForms apps today!
> Reboot your WinForms applications with our WinForms controls.
> Build a bridge from your legacy apps to the future.
>
> http://pubads.g.doubleclick.net/gampad/clk?id=153845071&iu=/4140/ostg.clktrk
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>

[cx-oracle-users] Possible to make SessionPool.acquire() block until connection available?

[Jim F. <jim...@sb...>]

I have an app which has more worker threads than my session pool max setting, because these threads spend most of their time doing wotk other than DB calls.
 
My problem is when acquire() is called and there are no available connections, a cx_Oracle.DatabaseError is immediately thrown.  Ideally I'd like acquire() to block up to some timeout period, and if multiple threads are blocked on acquire(), they received connections in the same order they blocked.
 
Maybe the way the Oracle DRCP interface works (which I know little about) prevents this?

[cx-oracle-users] Still working on openvms :-)

[Norman D. <den...@gm...>]

Hello cx_Oracle users,

I just successfully compiled cx_Oracle (5.1.3) on an openVMS system.
For the few who could still be interested, I uploaded the output right here:
https://bitbucket.org/ndenayer/cx_oracle/downloads

And the sources I changed a bit to compile it (OCIPing to put in comment
and patch the initxxx function):
https://bitbucket.org/ndenayer/cx_oracle

Have fun,

N.

Re: [cx-oracle-users] Versions of cx_Oracle: 11g/12c

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

On Tuesday 22 July 2014 17:32:57 Vladimir Ryabtsev wrote:
> Hi,
> 
> in some cases I have to work with multiple databases (versions 11g and 12c)
> using single Oracle client on machine (either may be, but only one). As
> both client and database have backward compatibility this works well, but
> what version of cx_Oracle is better to use? Those one wich matches client
> or database?
>

The version of cx_Oracle you install should only depend on your client 
version. The only reason you have separate versions is because they are built 
against different client libraries.

HTH,
	Shai.

[cx-oracle-users] Versions of cx_Oracle: 11g/12c

[Vladimir R. <gre...@gm...>]

Hi,

in some cases I have to work with multiple databases (versions 11g and 12c)
using single Oracle client on machine (either may be, but only one). As
both client and database have backward compatibility this works well, but
what version of cx_Oracle is better to use? Those one wich matches client
or database?

--
VR

Re: [cx-oracle-users] : CLOB performance issue

[Vladimir R. <gre...@gm...>]

Hello avinash,

Very weird workaround but it is really solution! It works 60% faster than
my variant. Thanks.

But it seems, in general, this way has some disadvantages:
- you must be able to select just inserted records fast (i.e. you must have
indexes) otherwise performance advantage will be lost;
- extra locks on table records;
- more complex logic of program.

Although this points may not be essential, this is actually a sort of trick
trick and I believe authors should somehow fix the library to make it usage
more straighforward.


2014-07-18 13:06 GMT+04:00 avinash nandakumar <avi...@or...
>:

> There is another way to insert data into the table which does not require
> you to call setvalue().
> The way is first you insert an empty CLOB into the table. Then you fetch
> the CLOB variable for update and write data into the CLOB.
> I can see better performance in this case.
> I have demonstrated the usage below:-
>
> *#create table  table_clob ( msg_id number, message CLOB)*
>
> *import cx_Oracle*
> *import time*
> *con = cx_Oracle.connect("scott/tiger@inst1")*
>
> *num_records = 100*
> *cur = con.cursor()*
> *text = '*
> **2**20*
> *rows =[]*
>
> *start_time = time.time()*
>
> *for id in range(num_records):*
> *    rows.append([id+1])*
>
> *cur.bindarraysize = num_records*
> *cur.executemany("insert into table_clob (msg_id, message) values (:1,
> empty_clob())", rows)*
>
> *selstmt = "select message from table_clob where msg_id between 1 AND :1
> for update"*
> *cur.execute(selstmt, [num_records])*
> *for id in range(num_records):*
> *    results = cur.fetchone()*
> *    results[0].write(text)*
>
> *con.commit()*
>
> *end_time =  time.time()*
> *print end_time -start_time*
> *cur.close()*
> *con.close()*

Re: [cx-oracle-users] : CLOB performance issue

[avinash n. <avi...@or...>]

Hello Vladimir,

There is another way to insert data into the table which does not 
require you to call setvalue().
The way is first you insert an empty CLOB into the table. Then you fetch 
the CLOB variable for update and write data into the CLOB.
I can see better performance in this case.
I have demonstrated the usage below:-
/
//#create table  table_clob ( msg_id number, message CLOB)/

/import cx_Oracle//
//import time//
//con = cx_Oracle.connect("scott/tiger@inst1")//
//
//num_records = 100//
//cur = con.cursor()//
//text = '$'*2**20//
//rows =[]//
//
//start_time = time.time()//
//
//for id in range(num_records)://
//    rows.append([id+1])//
//
//cur.bindarraysize = num_records//
//cur.executemany("insert into table_clob (msg_id, message) values (:1, 
empty_clob())", rows)//
//
//selstmt = "select message from table_clob where msg_id between 1 AND 
:1 for update"//
//cur.execute(selstmt, [num_records])//
//for id in range(num_records)://
//    results = cur.fetchone()//
//    results[0].write(text)//
//
//con.commit()//
//
//end_time =  time.time()//
//print end_time -start_time//
//cur.close()//
//con.close()/

I hope this will help you solve your performance problem.

Best Regards,
Avinash
On 7/16/2014 2:30 AM, Vladimir Ryabtsev wrote:
>
> 2014-07-14 16:35 GMT+04:00 avinash nandakumar 
> <avi...@or... <mailto:avi...@or...>>:
>
>     It looks like in the current code the variable.setvalue() function
>     for LOBs uses OCILobTemporary() which is a call to the server.
>     100 MB would be transferred to the server during this call. Hence
>     the call appears to take more time at the time of filling.
>     Subsequent execute() will just be adjusting the descriptors in the
>     table in the db.
>     End to end there should not be any additional processing.
>
>     Best Regards,
>     Avinash
>
>
> Hello Avinash,
>
> is it really necessary to call OCILobTemporary() in OCI level? Do you 
> consider implementation of cx_Oracle correct?
>
> If I write my code in Java (for example) it works much faster, that's 
> why I suppose the problem is in cx_Oracle. I see the difference in 
> performance only in case I work CLOBs, performance is comparable if I 
> use simple data types.
>
>
>
> ------------------------------------------------------------------------------
> Want fast and easy access to all the code in your enterprise? Index and
> search up to 200,000 lines of code with a free copy of Black Duck
> Code Sight - the same software that powers the world's largest code
> search on Ohloh, the Black Duck Open Hub! Try it now.
> http://p.sf.net/sfu/bds
>
>
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users

Re: [cx-oracle-users] ORA-12560:TNS:protocol adapter error with cx_Freeze

[Claus G. N. <cl...@gy...>]

I'm providing a TNS entry to connect.
It doesn't work if I provide host/port. I have tried both methods for the frozen version.

Sent from my iPhone.

> Den 16/07/2014 kl. 15.19 skrev Dwayne King <dw...@kr...>:
> 
> What method are you using to connect?  i.e. are you providing host / port / SID, or are you providing a TNS entry?
> 
> 
>> On Wed, Jul 16, 2014 at 5:37 AM, Claus Gyrup Nielsen <cl...@gy...> wrote:
>> Yes, i'm running both on the same machine. Haven't tested on another machine yet.
>> 
>> Sent from my iPhone.
>> 
>>> Den 16/07/2014 kl. 02.50 skrev Dwayne King <dw...@kr...>:
>>> 
>> 
>>> Are you testing on the same machine?  If not, are you sure that the machine that you're using the frozen version on has the same environment variables for ORACLE_HOME, ORACLE_SID, TNS_ADMIN etc?
>>> 
>>> 
>>>> On Thu, Jul 10, 2014 at 8:41 AM, <cl...@gy...> wrote:
>>>> I have a python(3.4.1) script in which I use cx_Oracle(5.1.3) to connect to an oracle database (11g).
>>>> 
>>>> I'm on a windows 7 machine (64bit).
>>>> 
>>>> When I run the script from IDLE or from a command promt the program works perfectly and retrieves my data.
>>>> 
>>>> However, when i create a frozen binary using cx_Freeze(4.3.3) and try to retrieve data i get the error message  ORA-12560:TNS:protocol adapter error. All other parts of my program work perfectly.
>>>> 
>>>> Any help or suggestions are highly appreciated.
>>>> 
>>>> Best regards,
>>>> 
>>>> Claus Gyrup
>>>> 
>>>> ------------------------------------------------------------------------------
>>>> Open source business process management suite built on Java and Eclipse
>>>> Turn processes into business applications with Bonita BPM Community Edition
>>>> Quickly connect people, data, and systems into organized workflows
>>>> Winner of BOSSIE, CODIE, OW2 and Gartner awards
>>>> http://p.sf.net/sfu/Bonitasoft
>>>> _______________________________________________
>>>> cx-oracle-users mailing list
>>>> cx-...@li...
>>>> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>>>> 
>>> 
> 

Re: [cx-oracle-users] ORA-12560:TNS:protocol adapter error with cx_Freeze

[Dwayne K. <dw...@kr...>]

What method are you using to connect?  i.e. are you providing host / port /
SID, or are you providing a TNS entry?


On Wed, Jul 16, 2014 at 5:37 AM, Claus Gyrup Nielsen <cl...@gy...>
wrote:

> Yes, i'm running both on the same machine. Haven't tested on another
> machine yet.
>
> Sent from my iPhone.
>
> Den 16/07/2014 kl. 02.50 skrev Dwayne King <dw...@kr...>:
>
> Are you testing on the same machine?  If not, are you sure that the
> machine that you're using the frozen version on has the same environment
> variables for ORACLE_HOME, ORACLE_SID, TNS_ADMIN etc?
>
>
> On Thu, Jul 10, 2014 at 8:41 AM, <cl...@gy...> wrote:
>
>> I have a python(3.4.1) script in which I use cx_Oracle(5.1.3) to connect
>> to an oracle database (11g).
>>
>> I'm on a windows 7 machine (64bit).
>>
>> When I run the script from IDLE or from a command promt the program works
>> perfectly and retrieves my data.
>>
>> However, when i create a frozen binary using cx_Freeze(4.3.3) and try to
>> retrieve data i get the error message  ORA-12560:TNS:protocol adapter
>> error. All other parts of my program work perfectly.
>>
>> Any help or suggestions are highly appreciated.
>>
>> Best regards,
>>
>> Claus Gyrup
>>
>>
>> ------------------------------------------------------------------------------
>> Open source business process management suite built on Java and Eclipse
>> Turn processes into business applications with Bonita BPM Community
>> Edition
>> Quickly connect people, data, and systems into organized workflows
>> Winner of BOSSIE, CODIE, OW2 and Gartner awards
>> http://p.sf.net/sfu/Bonitasoft
>> _______________________________________________
>> cx-oracle-users mailing list
>> cx-...@li...
>> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>>
>>
>

Re: [cx-oracle-users] ORA-12560:TNS:protocol adapter error with cx_Freeze

[Claus G. N. <cl...@gy...>]

Yes, i'm running both on the same machine. Haven't tested on another machine yet.

Sent from my iPhone.

> Den 16/07/2014 kl. 02.50 skrev Dwayne King <dw...@kr...>:
> 
> Are you testing on the same machine?  If not, are you sure that the machine that you're using the frozen version on has the same environment variables for ORACLE_HOME, ORACLE_SID, TNS_ADMIN etc?
> 
> 
>> On Thu, Jul 10, 2014 at 8:41 AM, <cl...@gy...> wrote:
>> I have a python(3.4.1) script in which I use cx_Oracle(5.1.3) to connect to an oracle database (11g).
>> 
>> I'm on a windows 7 machine (64bit).
>> 
>> When I run the script from IDLE or from a command promt the program works perfectly and retrieves my data.
>> 
>> However, when i create a frozen binary using cx_Freeze(4.3.3) and try to retrieve data i get the error message  ORA-12560:TNS:protocol adapter error. All other parts of my program work perfectly.
>> 
>> Any help or suggestions are highly appreciated.
>> 
>> Best regards,
>> 
>> Claus Gyrup
>> 
>> ------------------------------------------------------------------------------
>> Open source business process management suite built on Java and Eclipse
>> Turn processes into business applications with Bonita BPM Community Edition
>> Quickly connect people, data, and systems into organized workflows
>> Winner of BOSSIE, CODIE, OW2 and Gartner awards
>> http://p.sf.net/sfu/Bonitasoft
>> _______________________________________________
>> cx-oracle-users mailing list
>> cx-...@li...
>> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> 

Re: [cx-oracle-users] ORA-12560:TNS:protocol adapter error with cx_Freeze

[Dwayne K. <dw...@kr...>]

Are you testing on the same machine?  If not, are you sure that the machine
that you're using the frozen version on has the same environment variables
for ORACLE_HOME, ORACLE_SID, TNS_ADMIN etc?


On Thu, Jul 10, 2014 at 8:41 AM, <cl...@gy...> wrote:

> I have a python(3.4.1) script in which I use cx_Oracle(5.1.3) to connect
> to an oracle database (11g).
>
> I'm on a windows 7 machine (64bit).
>
> When I run the script from IDLE or from a command promt the program works
> perfectly and retrieves my data.
>
> However, when i create a frozen binary using cx_Freeze(4.3.3) and try to
> retrieve data i get the error message  ORA-12560:TNS:protocol adapter
> error. All other parts of my program work perfectly.
>
> Any help or suggestions are highly appreciated.
>
> Best regards,
>
> Claus Gyrup
>
>
> ------------------------------------------------------------------------------
> Open source business process management suite built on Java and Eclipse
> Turn processes into business applications with Bonita BPM Community Edition
> Quickly connect people, data, and systems into organized workflows
> Winner of BOSSIE, CODIE, OW2 and Gartner awards
> http://p.sf.net/sfu/Bonitasoft
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>

Re: [cx-oracle-users] : CLOB performance issue

[Vladimir R. <gre...@gm...>]

2014-07-14 16:35 GMT+04:00 avinash nandakumar <avi...@or...
>:

> It looks like in the current code the variable.setvalue() function for
> LOBs uses OCILobTemporary() which is a call to the server.
> 100 MB would be transferred to the server during this call.  Hence the
> call appears to take more time at the time of filling.
> Subsequent execute() will just be adjusting the descriptors in the table
> in the db.
> End to end there should not be any additional processing.
>
> Best Regards,
> Avinash
>

Hello Avinash,

is it really necessary to call OCILobTemporary() in OCI level? Do you
consider implementation of cx_Oracle correct?

If I write my code in Java (for example) it works much faster, that's why I
suppose the problem is in cx_Oracle. I see the difference in performance
only in case I work CLOBs, performance is comparable if I use simple data
types.

Re: [cx-oracle-users] : CLOB performance issue

[avinash n. <avi...@or...>]

Hello Vladimir,

It looks like in the current code the variable.setvalue() function for 
LOBs uses OCILobTemporary() which is a call to the server.
100 MB would be transferred to the server during this call. Hence the 
call appears to take more time at the time of filling.
Subsequent execute() will just be adjusting the descriptors in the table 
in the db.
End to end there should not be any additional processing.

Best Regards,
Avinash

-------- Original Message --------
> Subject: 	[cx-oracle-users] CLOB performance issue
> Date: 	Wed, 25 Jun 2014 17:57:58 +0400
> From: 	Vladimir Ryabtsev <gre...@gm...>
> Reply-To: 	cx-...@li...
> To: 	cx-...@li...
>
>
>
> Hi.
>
> I have encountered a performance problem when inserting values into a 
> column of CLOB datatype. For example I have a table:
>
> create table table_clob (msg_id number, message clob)
>
> Here is my code to reproduce the issue:
>
> -----
> import time
> import cx_Oracle
> import sys
>
> print('Python', sys.version, 'on', sys.platform)
> print('cx_Oracle.version:', cx_Oracle.version)
> num_records = 100
> con = cx_Oracle.connect('user/password@sid')
> print('DB version:', con.version)
> cur = con.cursor()
> cur.prepare("insert into table_clob (msg_id, message) values (:msg_id, 
> :msg)")
> cur.bindarraysize = num_records
> msg_arr = cur.var(cx_Oracle.CLOB, arraysize=num_records)
> text = '$'*2**20 Â  Â # 1 MB of text
> rows = []
>
> # Prepare data.
> start_time = time.perf_counter()
> for id in range(num_records):
> Â  Â  msg_arr.setvalue(id, text)
> Â  Â  rows.append( (id, msg_arr) )
>
> # Insert data.
> print('{} records prepared, {:.3f} s'
> Â  Â  Â  .format(num_records, time.perf_counter() - start_time))
> start_time = time.perf_counter()
> cur.executemany(None, rows)
> con.commit()
> print('{} records inserted, {:.3f} s'
> Â  Â  Â  .format(num_records, time.perf_counter() - start_time))
>
> cur.close()
> con.close()
> -----
>
> which yields the results:
>
> -----
> Python 3.3.2 (v3.3.2:d047928ae3f6, May 16 2013, 00:06:53) [MSC v.1600 
> 64 bit (AMD64)] on win32
> cx_Oracle.version: 5.1.3
> DB version: 11.2.0.1.0
> 100 records prepared, 5.384 s
> 100 records inserted, 4.536 s
> -----
>
> Versions of my DB and my client are the same.
>
> What's worrying me is the time of preparation of msg_arr structure. 5 
> seconds is abnormally much for copying of 100 MB in memory. 4.5 
> seconds seems OK for sending it over the network.
> The problematic step is msg_arr.setvalue(id, text). If I comment it, 
> script takes just milliseconds to complete (inserting null into CLOB 
> column of course).
> I also noticed a persistent disk activity during the "prepare" stage. 
> Perhaps cx_Oracle library stores some data in temporary files on disk? 
> This could be the reason of the problem...
>
> I wonder if there is a way to improve performance of "preparation" 
> part of my code.
>
> Thanks in advance for your help.
>
>

[cx-oracle-users] ORA-12560:TNS:protocol adapter error with cx_Freeze

[<cl...@gy...>]

I have a python(3.4.1) script in which I use cx_Oracle(5.1.3) to connect to an oracle database (11g).
I'm on a windows 7 machine (64bit).


When I run the script from IDLE or from a command promt the program works perfectly and retrieves my data.

However, when i create a frozen binary using cx_Freeze(4.3.3) and try to retrieve data i get the error message ORA-12560:TNS:protocol adapter error. All other parts of my program work perfectly.

Any help or suggestions are highly appreciated.

Best regards,

Claus Gyrup