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

Re: [cx-oracle-users] Session Pooling

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

Exceeding the session limit should raise an exception (that is what
the code in cx_Oracle instructs Oracle to do) but there is a bug in
Oracle right now that causes that particular flag to be ignored. I'm
not sure if its fixed in Oracle 10g yet but I don't think so.

In general I have used the pool to acquire connections rather than
using the Connection class directly. In other words,

connection  =3D pool.acquire()

which is a little simpler than the code you indicated you are using.
:-) It also demonstrates why I've never run into the possibility of
using connections with different credentials than those established by
the pool. Are you certain that the credentials are actually different
on the established connection? The code in cx_Oracle automatically
calls pool.acquire() when a pool is passed in to the Connection
constructor. Try "select user from dual" on your established
connection.

Session pooling automatically implements statement caching and
cx_Oracle doesn't give you an option for turning it off at the moment.
As such no examples are needed. :-) If you are talking about caching
cursors in general then a simple dictionary suffices and in general
can save 15-20% if you are executing a lot of small singleton
query/insert/update/delete statements. You simply need to do the
following:

cursor =3D connection.cursor()
cursor.prepare(some_sql)

and then put that cursor in the cache. When you want to use it

cursor.execute(None, args)

Hope that helps.

On 8/1/05, Orr, Steve <so...@ri...> wrote:
> I'm looking to use session pooling for performance (Apache 2.0,
> mod_python, Quixote 2.0, cx_Oracle 4.x Oracle9). Here's some code for
> reference:
> import cx_Oracle
> class dbconnect(cx_Oracle.Connection):
>     def __init__(self, user=3DNone, password=3DNone, dsn=3DNone, pool=3DN=
one):
>         cx_Oracle.Connection.__init__(self, user=3Duser,
> password=3Dpassword,
>                                       dsn=3Ddsn, pool=3Dpool)
>         self.pool =3D pool
>     def close(self):
>         self.pool.release(self)
> class dbfun(object):
>     def __init__(self):
>         self.pool =3D cx_Oracle.SessionPool('user', 'pw', 'local', 1, 8,
> 1)
>         self.pool.timeout =3D 30
>     def connect(self, user, password, dsn):
>         conn =3D dbconnect(user=3Duser, password=3Dpassword,
>                          dsn=3Ddsn, pool=3Dself.pool)
>         return conn
>=20
> Basically I subclassed the cx_Oracle connection object to over-ride the
> close function to release a connection to the pool instead of closing
> it.
>=20
> Observations and Questions:
> Setting the timeout attribute doesn't seem to change anything, no
> connections are returned to the pool after 30 seconds. Is this a bug or
> am I doing something wrong?
>=20
> If I exceed the session limit from Python it just hangs... Shouldn't
> cx_Oracle.Connection return an exception? Do I really HAVE to compare
> pool.busy and pool.opened in my code and raise my own exception?
>=20
> I found that if I kill a session from SQL*Plus it does not become
> available to the pool. Well okay.
>=20
> I found that when connecting through my dbfun class I could connect with
> a different user/password than was established in the pool. What are the
> ramifications of this? If I want to pool connections from different
> users do I need to create distinct pools for each user ID?
>=20
> Are there any other suggestions or sample code on how to implement
> connection pooling? Has anyone cached cursors and is it worth it?
>=20
> TIA,
> Steve Orr
>=20
>=20
> -------------------------------------------------------
> SF.Net email is sponsored by: Discover Easy Linux Migration Strategies
> from IBM. Find simple to follow Roadmaps, straightforward articles,
> informative Webcasts and more! Get everything you need to get up to
> speed, fast. http://ads.osdn.com/?ad_idt77&alloc_id=16492&opclick
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

RE: [cx-oracle-users] Session Pooling

[Orr, S. <so...@ri...>]

So long without answers? Are my questions too hard?  :-)

I was wrong about the SessionPool.timeout attribute. It works just fine.
I can configure the number of processes which Apache spawns and even
though each process establishes a reusable pool of database connections
they level off with inactivity and scale up when needed. Pooling
connections with cx_Oracle/Apache/mod_python serves up data driven web
pages really FAST!=20

I'm still wondering about the ramifications of adding connections with
different credentials than what the session pool was created with.=20

I'd also like to get some feedback on caching cursors. It seems like I
saw some code for that in the cx utilities or whatever. I'm just
wondering how much coding effort is required and what's the benefit?=20

Steve Orr


-----Original Message-----
From: cx-...@li...
[mailto:cx-...@li...] On Behalf Of Orr,
Steve
Sent: Monday, August 01, 2005 11:28 AM
To: cx-...@li...
Subject: [cx-oracle-users] Session Pooling

I'm looking to use session pooling for performance (Apache 2.0,
mod_python, Quixote 2.0, cx_Oracle 4.x Oracle9). Here's some code for
reference:
import cx_Oracle
class dbconnect(cx_Oracle.Connection):
    def __init__(self, user=3DNone, password=3DNone, dsn=3DNone, =
pool=3DNone):
        cx_Oracle.Connection.__init__(self, user=3Duser,
password=3Dpassword,
                                      dsn=3Ddsn, pool=3Dpool)
        self.pool =3D pool
    def close(self):
        self.pool.release(self)
class dbfun(object):
    def __init__(self):
        self.pool =3D cx_Oracle.SessionPool('user', 'pw', 'local', 1, 8,
1)
        self.pool.timeout =3D 30
    def connect(self, user, password, dsn):
        conn =3D dbconnect(user=3Duser, password=3Dpassword,=20
                         dsn=3Ddsn, pool=3Dself.pool)
        return conn

Basically I subclassed the cx_Oracle connection object to over-ride the
close function to release a connection to the pool instead of closing
it.=20

Observations and Questions:
Setting the timeout attribute doesn't seem to change anything, no
connections are returned to the pool after 30 seconds. Is this a bug or
am I doing something wrong?=20

If I exceed the session limit from Python it just hangs... Shouldn't
cx_Oracle.Connection return an exception? Do I really HAVE to compare
pool.busy and pool.opened in my code and raise my own exception?=20

I found that if I kill a session from SQL*Plus it does not become
available to the pool. Well okay.

I found that when connecting through my dbfun class I could connect with
a different user/password than was established in the pool. What are the
ramifications of this? If I want to pool connections from different
users do I need to create distinct pools for each user ID?

Are there any other suggestions or sample code on how to implement
connection pooling? Has anyone cached cursors and is it worth it?=20

TIA,
Steve Orr


-------------------------------------------------------
SF.Net email is sponsored by: Discover Easy Linux Migration Strategies
from IBM. Find simple to follow Roadmaps, straightforward articles,
informative Webcasts and more! Get everything you need to get up to
speed, fast. http://ads.osdn.com/?ad_idt77&alloc_id=16492&op=3Dick
_______________________________________________
cx-oracle-users mailing list
cx-...@li...
https://lists.sourceforge.net/lists/listinfo/cx-oracle-users

[cx-oracle-users] Session Pooling

[Orr, S. <so...@ri...>]

I'm looking to use session pooling for performance (Apache 2.0,
mod_python, Quixote 2.0, cx_Oracle 4.x Oracle9). Here's some code for
reference:
import cx_Oracle
class dbconnect(cx_Oracle.Connection):
    def __init__(self, user=3DNone, password=3DNone, dsn=3DNone, =
pool=3DNone):
        cx_Oracle.Connection.__init__(self, user=3Duser,
password=3Dpassword,
                                      dsn=3Ddsn, pool=3Dpool)
        self.pool =3D pool
    def close(self):
        self.pool.release(self)
class dbfun(object):
    def __init__(self):
        self.pool =3D cx_Oracle.SessionPool('user', 'pw', 'local', 1, 8,
1)
        self.pool.timeout =3D 30
    def connect(self, user, password, dsn):
        conn =3D dbconnect(user=3Duser, password=3Dpassword,=20
                         dsn=3Ddsn, pool=3Dself.pool)
        return conn

Basically I subclassed the cx_Oracle connection object to over-ride the
close function to release a connection to the pool instead of closing
it.=20

Observations and Questions:
Setting the timeout attribute doesn't seem to change anything, no
connections are returned to the pool after 30 seconds. Is this a bug or
am I doing something wrong?=20

If I exceed the session limit from Python it just hangs... Shouldn't
cx_Oracle.Connection return an exception? Do I really HAVE to compare
pool.busy and pool.opened in my code and raise my own exception?=20

I found that if I kill a session from SQL*Plus it does not become
available to the pool. Well okay.

I found that when connecting through my dbfun class I could connect with
a different user/password than was established in the pool. What are the
ramifications of this? If I want to pool connections from different
users do I need to create distinct pools for each user ID?

Are there any other suggestions or sample code on how to implement
connection pooling? Has anyone cached cursors and is it worth it?=20

TIA,
Steve Orr

Re: [cx-oracle-users] Calling procedures with fancy arguments

[<wa...@li...>]

Anthony Tuininga wrote:

> Not currently. This is what is called a "named" type and is not
> supported by cx_Oracle at the moment. I have plans to do so but they
> have been rather long in coming, unfortunately. :-( The only arrays
> that cx_Oracle supports today are PL/SQL arrays, not SQL named types

Ah, OK, so that's what the thread "Can't figure out how to use 
Cursor.arrayvar" was talking about.

> Of course, if anyone wanted to add support and send me a patch, I'd be
> very grateful.... :-)

I guess I need a version of the procedure that doesn't have these arguments.

Bye,
    Walter Dörwald

Re: [cx-oracle-users] Calling procedures with fancy arguments

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

Not currently. This is what is called a "named" type and is not
supported by cx_Oracle at the moment. I have plans to do so but they
have been rather long in coming, unfortunately. :-( The only arrays
that cx_Oracle supports today are PL/SQL arrays, not SQL named types
Of course, if anyone wanted to add support and send me a patch, I'd be
very grateful.... :-)

On 7/25/05, Walter D=F6rwald <wa...@li...> wrote:
> I'm trying to call a stored procedure that looks like this:
>=20
> create or replace procedure test_integers
> (
>     p_test integers
> )
> is
> begin
>     null;
> end;
>=20
> The type integers is defined like this:
>=20
> create or replace type integers as table of integer;
>=20
> I don't care about the value of the argument (NULL would be perfectly
> fine), but neither
>     cursor.callproc("test_integers", [None])
> nor
>     cursor.callproc("test_integers", [[]])
> work. Is there any way to call this procedure with cx_Oracle?
> (cx_Oracle 4.1 with "Oracle9i Enterprise Edition Release 9.2.0.1.0 -
> Production")
>=20
> Bye,
>     Walter D=F6rwald
>=20
>=20
>=20
> -------------------------------------------------------
> SF.Net email is sponsored by: Discover Easy Linux Migration Strategies
> from IBM. Find simple to follow Roadmaps, straightforward articles,
> informative Webcasts and more! Get everything you need to get up to
> speed, fast. http://ads.osdn.com/?ad_id=3D7477&alloc_id=3D16492&op=3Dclic=
k
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

[cx-oracle-users] Calling procedures with fancy arguments

[<wa...@li...>]

I'm trying to call a stored procedure that looks like this:

create or replace procedure test_integers
(
    p_test integers
)
is
begin
    null;
end;

The type integers is defined like this:

create or replace type integers as table of integer;

I don't care about the value of the argument (NULL would be perfectly 
fine), but neither
    cursor.callproc("test_integers", [None])
nor
    cursor.callproc("test_integers", [[]])
work. Is there any way to call this procedure with cx_Oracle?
(cx_Oracle 4.1 with "Oracle9i Enterprise Edition Release 9.2.0.1.0 - 
Production")

Bye,
    Walter Dörwald

Re: [cx-oracle-users] Re: Need fetchall invalidate LOB locators?

[Michael M. <mi...@ma...>]

On 15 Jul 2005, at 22:55, Anthony Tuininga wrote:

> On 7/15/05, Hamish Lawson <ham...@gm...> wrote:
>
>> Anthony Tuininga wrote:
>>
>>
>>> without looking at the code I would guess that [DCOracle2] define  
>>> new
>>> LOB locator variables for each of the rows that are fetched. That
>>> seems to me to be a lot of overhead for very little gain --  
>>> unless you
>>> can give me some valid reason for why fetchall() is a good idea when
>>> dealing with LOBs.
>>>
>>
>> The main reason I'd offer is completeness and to avoid special- 
>> casing.
>> I've been working on the contribution of an Oracle driver to the PyDO
>> 2 ORM project, and I've been obliged to reimplement one of the
>> inherited core methods just because it uses fetchall; I can envisage
>> other situations where code that is not our own might expect to be
>> able to call fetchall. You are right of course that there is a memory
>> overhead in allocating LOB locator variables for each row. But any
>> time the programmer chooses between fetchall and fetchone/iteration,
>> they are deciding on a trade-off between memory and time costs. Why
>> for the special case of the resultset's having LOBs should cx_Oracle
>> remove that choice from the programmer?
>>
>
> The main reason is because I can't tell up front which method you are
> going to use. Thus, I would have to use the "bad" method at all times.
> If you can think of a sane way of getting around this problem I'd love
> to hear about it. If any one else wants to chime in with an opinion
> I'd love to hear those, too.


I don't have a sane implementation suggestion, except perhaps an  
argument to the constructor like threads (true-false), however I do  
have use cases.

I do a lot of bioinformatics work where I use large strings, many of  
which are stored as CLOBs, now most of the sequences are actually  
shorted than 4000 char long, but a proportion of them are not, so I  
need to use CLOBs. I regularly need to get many sequences, and while  
in most cases I use fetchall() or fetchmany() I obviously can't with  
CLOBS. As I often need all the sequences in memory at once to do  
something with them saving memory by iterating over them one at a  
time saves me nothing and just costs time, although usually not  
enough to really worry about.

In other situations I need to iterate over millions of CLOBS and  
doing them a few hundred at a time would certainly save a substantial  
amount of time, at some memory cost, but not that much.

Finally I agree with the special casing point, It'd be great to be  
able to deal with all queries in essentially the same fashion, but I  
can't use the same pattern for CLOBs.

It isn't a deal breaker but if there was a clean way of being able to  
use fetchall()/fetchmany() with LOBs I'd be very happy to see it.

cheers

Michael

Re: [cx-oracle-users] Re: Need fetchall invalidate LOB locators?

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

On 7/15/05, Hamish Lawson <ham...@gm...> wrote:
> Anthony Tuininga wrote:
>=20
> > without looking at the code I would guess that [DCOracle2] define new
> > LOB locator variables for each of the rows that are fetched. That
> > seems to me to be a lot of overhead for very little gain -- unless you
> > can give me some valid reason for why fetchall() is a good idea when
> > dealing with LOBs.
>=20
> The main reason I'd offer is completeness and to avoid special-casing.
> I've been working on the contribution of an Oracle driver to the PyDO
> 2 ORM project, and I've been obliged to reimplement one of the
> inherited core methods just because it uses fetchall; I can envisage
> other situations where code that is not our own might expect to be
> able to call fetchall. You are right of course that there is a memory
> overhead in allocating LOB locator variables for each row. But any
> time the programmer chooses between fetchall and fetchone/iteration,
> they are deciding on a trade-off between memory and time costs. Why
> for the special case of the resultset's having LOBs should cx_Oracle
> remove that choice from the programmer?

The main reason is because I can't tell up front which method you are
going to use. Thus, I would have to use the "bad" method at all times.
If you can think of a sane way of getting around this problem I'd love
to hear about it. If any one else wants to chime in with an opinion
I'd love to hear those, too.

> Hamish Lawson
>=20
>=20
> -------------------------------------------------------
> SF.Net email is sponsored by: Discover Easy Linux Migration Strategies
> from IBM. Find simple to follow Roadmaps, straightforward articles,
> informative Webcasts and more! Get everything you need to get up to
> speed, fast. http://ads.osdn.com/?ad_idt77&alloc_id=16492&opclick
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

[cx-oracle-users] Re: Need fetchall invalidate LOB locators?

[Hamish L. <ham...@gm...>]

Anthony Tuininga wrote:

> without looking at the code I would guess that [DCOracle2] define new
> LOB locator variables for each of the rows that are fetched. That
> seems to me to be a lot of overhead for very little gain -- unless you
> can give me some valid reason for why fetchall() is a good idea when
> dealing with LOBs.

The main reason I'd offer is completeness and to avoid special-casing.
I've been working on the contribution of an Oracle driver to the PyDO
2 ORM project, and I've been obliged to reimplement one of the
inherited core methods just because it uses fetchall; I can envisage
other situations where code that is not our own might expect to be
able to call fetchall. You are right of course that there is a memory
overhead in allocating LOB locator variables for each row. But any
time the programmer chooses between fetchall and fetchone/iteration,
they are deciding on a trade-off between memory and time costs. Why
for the special case of the resultset's having LOBs should cx_Oracle
remove that choice from the programmer?


Hamish Lawson

Re: [cx-oracle-users] Need fetchall invalidate LOB locators?

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

Hmm, without looking at the code I would guess that they define new
LOB locator variables for each of the rows that are fetched. That
seems to me to be a lot of overhead for very little gain -- unless you
can give me some valid reason for why fetchall() is a good idea when
dealing with LOBs. :-)

In general, I would suggest the following idiom:

for value_1, value_2, lob in cursor:
    DoStuff(value_1, value_2, lob.read())

or some such. In other words, use the iterator which ensures that
you're using as little memory as possible and doesn't have the issue
with the LOB locators.

On 7/15/05, Hamish Lawson <ham...@gm...> wrote:
> Hello Anthony
>=20
> The cx_Oracle manual explains that calling fetchall when the resultset
> contains LOBs will raise an exception because the LOB locators would
> get invalidated by the next fetch. However DCOracle2 lets you call
> fetchall and still read the LOBs in each of the returned rows. I don't
> know what approach DCOracle2 uses to achieve this, but is it something
> you could borrow?
>=20
>=20
> Hamish Lawson
>=20
>=20
> -------------------------------------------------------
> SF.Net email is sponsored by: Discover Easy Linux Migration Strategies
> from IBM. Find simple to follow Roadmaps, straightforward articles,
> informative Webcasts and more! Get everything you need to get up to
> speed, fast. http://ads.osdn.com/?ad_idt77&alloc_id=16492&opclick
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

[cx-oracle-users] Need fetchall invalidate LOB locators?

[Hamish L. <ham...@gm...>]

Hello Anthony

The cx_Oracle manual explains that calling fetchall when the resultset
contains LOBs will raise an exception because the LOB locators would
get invalidated by the next fetch. However DCOracle2 lets you call
fetchall and still read the LOBs in each of the returned rows. I don't
know what approach DCOracle2 uses to achieve this, but is it something
you could borrow?


Hamish Lawson

Re: [cx-oracle-users] Re: SDO_GEOMETRY data type

[Mikel L. <mla...@co...>]

Jani Tiainen wrote:
> 
> Sure it works that way, at least for points. But WKT works for almost 
> anykind of shapes - points, shapes, linestrings, geometry collections.

Ok. For the moment I just have to handle with points, but I will take 
into account your comment about WKT.

> 
> With CLOB's you have to remember to read var in temporary structure in 
> python if you need to work with multiple values, otherwise that 
> externallobvar is not valid after next fetchone().

Yes, I realized about this some months ago, when I had to fetch photos 
from a table in the same database.

Thanks,

-- 
Mikel Larreategi
mla...@co...

CodeSyntax
Azitaingo Industrialdea 3 K
E-20600 Eibar
Tel: (+34) 943 82 17 80

[cx-oracle-users] Re: SDO_GEOMETRY data type

[Jani T. <re...@lu...>]

Mikel Larreategi kirjoitti:
> Mikel Larreategi wrote:
> 
>>  >>> import cx_Oracle as db
>>  >>> con = cb.connect('user', 'mysecretpassword')
>>  >>> cur = con.cursor()
>>  >>> cur.execute('select a.GEOM_WGS84.GET_WKT(), a.id_postal from 
>> n_edifgen_3d a where id_postal between 10000 and 10005')
>> [<CLOBVar object at 0x40167930>, <NumberVar object at 0x4016a0b0>]
>>  >>> cur.description
>> [('A.GEOM_WGS84.GET_WKT()', <type 'CLOBVar'>, -1, 4000, 0, 0, 1), 
>> ('ID_POSTAL', <type 'NumberVar'>, 127, 22, 0, -127, 1)]
>>  >>> b = cur.fetchone()
>>  >>> b
>> (<ExternalLobVar object at 0x40162060>, 10000.0)
>>  >>> b[0].read()
>> 'POINT (-2.35566829112215 43.2908549259473)'
> 
> 
> I can also get the info in this way:
> 
>  >>> cur.execute('select a.geom_wgs84.sdo_point.X, 
> a.geom_wgs84.sdo_point.Y from n_edifgen_3d a where id_postal between 
> 10000 and 10005')
> [<NumberVar object at 0x4016a140>, <NumberVar object at 0x4016a188>]
>  >>> cur.fetchone()
> (-2.35566829112215, 43.290854925947293)

Sure it works that way, at least for points. But WKT works for almost 
anykind of shapes - points, shapes, linestrings, geometry collections.

With CLOB's you have to remember to read var in temporary structure in 
python if you need to work with multiple values, otherwise that 
externallobvar is not valid after next fetchone().

And best of that is you can really insert WKT back to Oracle.

-- 

Jani Tiainen

Re: [cx-oracle-users] Re: SDO_GEOMETRY data type

[Mikel L. <mla...@co...>]

Mikel Larreategi wrote:
>  >>> import cx_Oracle as db
>  >>> con = cb.connect('user', 'mysecretpassword')
>  >>> cur = con.cursor()
>  >>> cur.execute('select a.GEOM_WGS84.GET_WKT(), a.id_postal from 
> n_edifgen_3d a where id_postal between 10000 and 10005')
> [<CLOBVar object at 0x40167930>, <NumberVar object at 0x4016a0b0>]
>  >>> cur.description
> [('A.GEOM_WGS84.GET_WKT()', <type 'CLOBVar'>, -1, 4000, 0, 0, 1), 
> ('ID_POSTAL', <type 'NumberVar'>, 127, 22, 0, -127, 1)]
>  >>> b = cur.fetchone()
>  >>> b
> (<ExternalLobVar object at 0x40162060>, 10000.0)
>  >>> b[0].read()
> 'POINT (-2.35566829112215 43.2908549259473)'

I can also get the info in this way:

 >>> cur.execute('select a.geom_wgs84.sdo_point.X, 
a.geom_wgs84.sdo_point.Y from n_edifgen_3d a where id_postal between 
10000 and 10005')
[<NumberVar object at 0x4016a140>, <NumberVar object at 0x4016a188>]
 >>> cur.fetchone()
(-2.35566829112215, 43.290854925947293)


-- 
Mikel Larreategi
mla...@co...

CodeSyntax
Azitaingo Industrialdea 3 K
E-20600 Eibar
Tel: (+34) 943 82 17 80

Re: [cx-oracle-users] Re: SDO_GEOMETRY data type

[Mikel L. <mla...@co...>]

Jani Tiainen wrote:
> Well you can get around this problem by using Well-known Text format=20
> (WKT), since Oracle 10g supports that (You're using that right?). So,=20
> instead getting SDO_GEOMETRY column use column.GET_WKT() you can also=20
> save data in WKT format. IIRC there is also support for WKB, which is=20
> bit faster but needs more parsing since it's binary. With Oracle 9i +=20
> Spatial add-on you're out of luck.
>=20
> I.e.:   SELECT ID, GEOMLOC.GET_WKT() FROM GEODATA_TABLE;
>=20

Yes, I'm using "Oracle Database 10g Enterprise Edition Release=20
10.1.0.3.0 - Production With the Partitioning, OLAP and Data Mining=20
options".

When I make a query in the way you say, I get the following (I've used=20
SQLPlus to make this test, but in Python the error is the same):

SQL> select geom_wgs84.GET_WKT(), id_postal from n_edifgen_3d where=20
id_postal between 10000 and 10005;
select geom_wgs84.GET_WKT(), id_postal from n_edifgen_3d where id_postal=20
between 10000 and 10005
        *
ERROR en l=EDnea 1:
ORA-00904: "GEOM_WGS84"."GET_WKT": identificador no v=E1lido


Nevertheless, if I query the DB in this way, I get the SDO_GEOMETRY=20
datatype as ExternalLobVar object, and I can read it as string (now in=20
Python):

 >>> import cx_Oracle as db
 >>> con =3D cb.connect('user', 'mysecretpassword')
 >>> cur =3D con.cursor()
 >>> cur.execute('select a.GEOM_WGS84.GET_WKT(), a.id_postal from=20
n_edifgen_3d a where id_postal between 10000 and 10005')
[<CLOBVar object at 0x40167930>, <NumberVar object at 0x4016a0b0>]
 >>> cur.description
[('A.GEOM_WGS84.GET_WKT()', <type 'CLOBVar'>, -1, 4000, 0, 0, 1),=20
('ID_POSTAL', <type 'NumberVar'>, 127, 22, 0, -127, 1)]
 >>> b =3D cur.fetchone()
 >>> b
(<ExternalLobVar object at 0x40162060>, 10000.0)
 >>> b[0].read()
'POINT (-2.35566829112215 43.2908549259473)'

The difference is that I 'rename' the table for the query (select ...=20
from n_edifgen_3d *a*), like I read at http://tinyurl.com/99o3v

Thanks for your help Jani.


--=20
Mikel Larreategi
mla...@co...

CodeSyntax
Azitaingo Industrialdea 3 K
E-20600 Eibar
Tel: (+34) 943 82 17 80

[cx-oracle-users] Re: SDO_GEOMETRY data type

[Jani T. <re...@lu...>]

Mikel Larreategi kirjoitti:
> Anthony Tuininga wrote:
> 
>> Yes, there are plans to support such data types but they are not very
>> firm. I haven't personally had any need for such data types yet and
>> the project I am working on right now is taking a lot of time -- so it
>> hasn't been a priority. Patches (as always) are welcome. :-)
> 
> 
> Thanks for your answer.
> 
> I've never written C extensions for Python, so I don't think I could do 
> it now... Perhaps if I had more time to do it... but now I can't do it, 
> and I just need to access those data. I'll ask DBA to change the 
> datatype or to export those data as string if it's possible.

Well you can get around this problem by using Well-known Text format 
(WKT), since Oracle 10g supports that (You're using that right?). So, 
instead getting SDO_GEOMETRY column use column.GET_WKT() you can also 
save data in WKT format. IIRC there is also support for WKB, which is 
bit faster but needs more parsing since it's binary. With Oracle 9i + 
Spatial add-on you're out of luck.

I.e.:   SELECT ID, GEOMLOC.GET_WKT() FROM GEODATA_TABLE;

-- 

Jani Tiainen

Re: [cx-oracle-users] SDO_GEOMETRY data type

[Mikel L. <mla...@co...>]

Anthony Tuininga wrote:
> Yes, there are plans to support such data types but they are not very
> firm. I haven't personally had any need for such data types yet and
> the project I am working on right now is taking a lot of time -- so it
> hasn't been a priority. Patches (as always) are welcome. :-)

Thanks for your answer.

I've never written C extensions for Python, so I don't think I could do 
it now... Perhaps if I had more time to do it... but now I can't do it, 
and I just need to access those data. I'll ask DBA to change the 
datatype or to export those data as string if it's possible.


Thank you,


-- 
Mikel Larreategi
mla...@co...

CodeSyntax
Azitaingo Industrialdea 3 K
E-20600 Eibar
Tel: (+34) 943 82 17 80

Re: [cx-oracle-users] SDO_GEOMETRY data type

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

Yes, there are plans to support such data types but they are not very
firm. I haven't personally had any need for such data types yet and
the project I am working on right now is taking a lot of time -- so it
hasn't been a priority. Patches (as always) are welcome. :-)

On 6/30/05, Mikel Larreategi <mla...@co...> wrote:
> Hi:
>=20
> I have a table with a lot of geographical data and some of those data is
> stored in a field of type SDO_GEOMETRY.
>=20
> When I launch a select on the table, I get "cx_Oracle.NotSupportedError:
> Variable_TypeByOracleDataType: unhandled data type 108", so I suposse
> that cx_Oracle cannot handle this kind of data.
>=20
> Is there any plan to support this data type?
>=20
> I'm using Python 2.3.4, cx_Oracle 4.0.1 and Oracle 10.1.0.3.0.
>=20
> Thank you,
>=20
>=20
> --
> Mikel Larreategi
> mla...@co...
>=20
> CodeSyntax
> Azitaingo Industrialdea 3 K
> E-20600 Eibar
> Tel: (+34) 943 82 17 80
>=20
>=20
> -------------------------------------------------------
> SF.Net email is sponsored by: Discover Easy Linux Migration Strategies
> from IBM. Find simple to follow Roadmaps, straightforward articles,
> informative Webcasts and more! Get everything you need to get up to
> speed, fast. http://ads.osdn.com/?ad_id=3D7477&alloc_id=3D16492&op=3Dclic=
k
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

[cx-oracle-users] SDO_GEOMETRY data type

[Mikel L. <mla...@co...>]

Hi:

I have a table with a lot of geographical data and some of those data is 
stored in a field of type SDO_GEOMETRY.

When I launch a select on the table, I get "cx_Oracle.NotSupportedError: 
Variable_TypeByOracleDataType: unhandled data type 108", so I suposse 
that cx_Oracle cannot handle this kind of data.

Is there any plan to support this data type?

I'm using Python 2.3.4, cx_Oracle 4.0.1 and Oracle 10.1.0.3.0.

Thank you,


-- 
Mikel Larreategi
mla...@co...

CodeSyntax
Azitaingo Industrialdea 3 K
E-20600 Eibar
Tel: (+34) 943 82 17 80

Re: [cx-oracle-users] Can't figure out how to use Cursor.arrayvar

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

Ah, you've been bitten by the annoying difference between PL/SQL
arrays and SQL arrays. :-) cx_Oracle currently only supports PL/SQL
arrays. I have plans to support the other variety but work has been
getting in the way recently. So, you need to define in your PL/SQL
package something of the form:

type udt_StringArray is table of varchar2(30) index by binary_integer;

And then use this type instead of the other type you defined in SQL.
At that point, the Python code you used should work just fine.

Hope that helps.

On 6/24/05, L. Daniel Burr <dan...@gp...> wrote:
> Can someone please explain how to work with arrays?  Specifically,
> how do I pass an array into a stored procedure/stored function, and
> how do I retrieve an array from a stored procedure/stored function?
>=20
> Example:
>=20
> create or replace type foo is varray(20) of varchar2(30);
>=20
> create or replace function getFoo return foo
> IS
>      l_data foo :=3D foo();
> BEGIN
>      l_data.EXTEND;
>      l_data(1) :=3D 'Some text';
>      return l_data;
> END getFoo;
>=20
>=20
> c =3D cx_Oracle.connect('foo/bar@baz')
> cursor =3D c.cursor()
>=20
> cursor.callfunc('getFoo', [cx_Oracle.STRING, 20])
>=20
> Traceback (most recent call last):
>    File "<stdin>", line 1, in ?
> cx_Oracle.DatabaseError: ORA-06550: line 1, column 13:
> PLS-00382: expression is of wrong type
> ORA-06550: line 1, column 7:
> PL/SQL: Statement ignored
>=20
> If the stored function returns STRING, or NUMBER, everything is fine,
> but I need to make this work with arrays.
>=20
> Thanks much for your help,
>=20
> L. Daniel Burr
>=20
>=20
> -------------------------------------------------------
> SF.Net email is sponsored by: Discover Easy Linux Migration Strategies
> from IBM. Find simple to follow Roadmaps, straightforward articles,
> informative Webcasts and more! Get everything you need to get up to
> speed, fast. http://ads.osdn.com/?ad_id=3D7477&alloc_id=3D16492&op=3Dclic=
k
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

[cx-oracle-users] Can't figure out how to use Cursor.arrayvar

[L. D. B. <dan...@gp...>]

Can someone please explain how to work with arrays?  Specifically,  
how do I pass an array into a stored procedure/stored function, and  
how do I retrieve an array from a stored procedure/stored function?

Example:

create or replace type foo is varray(20) of varchar2(30);

create or replace function getFoo return foo
IS
     l_data foo := foo();
BEGIN
     l_data.EXTEND;
     l_data(1) := 'Some text';
     return l_data;
END getFoo;


c = cx_Oracle.connect('foo/bar@baz')
cursor = c.cursor()

cursor.callfunc('getFoo', [cx_Oracle.STRING, 20])

Traceback (most recent call last):
   File "<stdin>", line 1, in ?
cx_Oracle.DatabaseError: ORA-06550: line 1, column 13:
PLS-00382: expression is of wrong type
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

If the stored function returns STRING, or NUMBER, everything is fine,  
but I need to make this work with arrays.

Thanks much for your help,

L. Daniel Burr

Re: [cx-oracle-users] Oracle Client Timeout

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

You can set timeouts in the sqlnet.ora file but I'm not aware of the
ability to set timeouts at the OCI level. If you can point me to the
location where such a capability is documented I'd be happy enough to
add the feature.

On 6/24/05, ACTIS GROSSO Mirko Consultant
<Mir...@co...> wrote:
> =20
> =20
>=20
>  =20
>=20
> Hi,=20
>=20
>  =20
>=20
> I have to set the timeout in my oracle client, now seems to be infinite.=
=20
>=20
>  =20
>=20
> I'm using Oracle 8 now but soon I'll migrate to 10.=20
>=20
>  =20
>=20
> Regards Mirko

[cx-oracle-users] Oracle Client Timeout

[ACTIS G. M. C. <Mir...@co...>]

=20

Hi,

=20

I have to set the timeout in my oracle client, now seems to be infinite.

=20

I'm using Oracle 8 now but soon I'll migrate to 10.

=20

Regards Mirko

Re: [cx-oracle-users] Oracle9i-RAC anomaly

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

If you are using threads to access Oracle, be aware that Oracle
doesn't really appreciate the use of the same connection in multiple
threads. :-) If you want to do that, you will need to perform locking
as necessary.

If, however, you only want to use one connection per thread than you
simply need to create your connections (or session pools) with the
"threaded =3D True" parameter. I suspect your strangeness will go away
with this option specified. :-)

On 6/23/05, Markus Edelmann <mar...@me...> wrote:
> Hi,
>=20
> we' ve observered some strange Oracle-RAC behaviour with a thread-based
> Webware4Python-application and an anonymous Oracle-connection pool based =
on
> cx_Oracle-connection against an Oracle-RAC-instance with 3 nodes.
>=20
> Test- and production-environment:
>=20
> - Oracle-RAC-instance-nodes:
>   - SuSE SLES8 with all recommeded patches by Oracle
>   - Oracle9i V. 9.2.0.6.0
>=20
> - AppServer with OCI-client:
>   - SuSE SLES9 with all recommended patches by Oracle
>   - Webware4Python V. 0.8.1
>   - Python V. 2.3.5 compiled from sources
>   - Oracle9i-OCI-client V. 9.2.0.6.0
>   - cx_Oracle V. 4.1 compiled and linked against Oracle from sources
>=20
> Even with a simple stress-script executing 3 SQL-statements with *2*
> connections we can generate the same strange result:
>=20
> -- snippet start --
>=20
>     for i in range(connectionSwitches):
>         oraConn1 =3D cx_Oracle.connect(connection1)
>         if connection2:
>             oraConn2 =3D cx_Oracle.connect(connection2)
>         else:
>             oraConn2 =3D oraConn1
>=20
>         for j in range(iterations):
>             c +=3D 1
>             error, text =3D doRettungspunktTable(oraConn1, 3, massnahmenN=
umber,
> DATA_DICT,'GPL') # Statement 1 =3D> delete 1 record and commit
>             error, text =3D doRettungspunktTable(oraConn1, 1, massnahmenN=
umber,
> DATA_DICT,'GPL') # Statement 2 =3D> insert 1 record and commit
>             doSelect(c, oraConn2, massnahmenNumber) # Statement 3 =3D> fe=
tch all
> records
>=20
>         try:
>             oraConn1.close()
>             oraConn2.close()
>         except cx_Oracle.InterfaceError:
>             pass
>=20
> -- snippet stop --
>=20
> What do you expect? Sure, statement 3 is fetching at least 1 record! So f=
ar,
> so good, if the script is running against a Oracle9i-RAC with *1* node.
> But, if the script is running against a Oracle9i-RAC with *2* or *n* node=
s
> sometimes the result set of statement 3 is empty!
>=20
> The anomaly is reproducible from different clients.
>=20
> Has anyone observered the same strange behaviour with
> Oracle-RAC-installations?
>=20
> I would be grateful for any advices and hints...
>=20
> Salute,
>=20
> Ede
>=20
> --
> Markus Edelmann
> Email: mar...@me...
>=20
>=20
> -------------------------------------------------------
> SF.Net email is sponsored by: Discover Easy Linux Migration Strategies
> from IBM. Find simple to follow Roadmaps, straightforward articles,
> informative Webcasts and more! Get everything you need to get up to
> speed, fast. http://ads.osdn.com/?ad_id=3D7477&alloc_id=3D16492&op=3Dclic=
k
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

[cx-oracle-users] Oracle9i-RAC anomaly

[Markus E. <mar...@me...>]

Hi,

we' ve observered some strange Oracle-RAC behaviour with a thread-based 
Webware4Python-application and an anonymous Oracle-connection pool based on 
cx_Oracle-connection against an Oracle-RAC-instance with 3 nodes.

Test- and production-environment:

- Oracle-RAC-instance-nodes:
  - SuSE SLES8 with all recommeded patches by Oracle
  - Oracle9i V. 9.2.0.6.0

- AppServer with OCI-client:
  - SuSE SLES9 with all recommended patches by Oracle
  - Webware4Python V. 0.8.1
  - Python V. 2.3.5 compiled from sources
  - Oracle9i-OCI-client V. 9.2.0.6.0
  - cx_Oracle V. 4.1 compiled and linked against Oracle from sources

Even with a simple stress-script executing 3 SQL-statements with *2* 
connections we can generate the same strange result:

-- snippet start --

    for i in range(connectionSwitches):
        oraConn1 = cx_Oracle.connect(connection1)
        if connection2:
            oraConn2 = cx_Oracle.connect(connection2)
        else:
            oraConn2 = oraConn1

        for j in range(iterations):
            c += 1
            error, text = doRettungspunktTable(oraConn1, 3, massnahmenNumber, 
DATA_DICT,'GPL') # Statement 1 => delete 1 record and commit
            error, text = doRettungspunktTable(oraConn1, 1, massnahmenNumber, 
DATA_DICT,'GPL') # Statement 2 => insert 1 record and commit
            doSelect(c, oraConn2, massnahmenNumber) # Statement 3 => fetch all 
records

        try:
            oraConn1.close()
            oraConn2.close()
        except cx_Oracle.InterfaceError:
            pass
    
-- snippet stop --

What do you expect? Sure, statement 3 is fetching at least 1 record! So far, 
so good, if the script is running against a Oracle9i-RAC with *1* node.
But, if the script is running against a Oracle9i-RAC with *2* or *n* nodes 
sometimes the result set of statement 3 is empty!

The anomaly is reproducible from different clients.

Has anyone observered the same strange behaviour with 
Oracle-RAC-installations?

I would be grateful for any advices and hints...

Salute,

Ede
  
-- 
Markus Edelmann
Email: mar...@me...