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

Re: [cx-oracle-users] Nulls returned as None and not as as "empty or zero lenght string"

[Chris D. <cdu...@ya...>]

Marcos,

Great idea (nvl as opposed to isnull). However cx_Oracle still returns None e.g.

cur1.execute("select nvl(subobject_name,'') from objects_big where rownum < 5")
data = cur1.fetchall()
print data
[(None,), (None,), (None,), (None,), (None,)]

I thought you'd "fixed it". Thanks for the idea anyway.

Chris

--- Marcos Sánchez Provencio <ma...@bu...> wrote:

> you may try to do it at oracle level
> 
> select isnull(col,'')...
> 
> it should be faster
> 
> El mié, 11-05-2005 a las 07:15 -0700, Chris Dunscombe escribió:
> > All,
> > 
> > Is there anyway that Null values returned from a query can be an "empty or zero length string"
> > instead of None?
> > 
> > This may seem a strange request as it's easy to use type to test if a null has been returned
> as
> > None. However I've a performance critical app where I need to output "nothing" (zero length /
> > empty string) if the value is null. Hence at present I have to perform this "type test" on ALL
> > columns that don't have a "Not Null" constraint in the database which is quite an overhead
> when in
> > some cases I need to do it millions and sometimes billions of times.
> > 
> > Thanks,
> > 
> > Chris Dunscombe
> > 
> > 
> > 		
> > __________________________________ 
> > Do you Yahoo!? 
> > Read only the mail you want - Yahoo! Mail SpamGuard. 
> > http://promotions.yahoo.com/new_mail 
> > 
> > 
> > -------------------------------------------------------
> > This SF.Net email is sponsored by Oracle Space Sweepstakes
> > Want to be the first software developer in space?
> > Enter now for the Oracle Space Sweepstakes!
> > http://ads.osdn.com/?ad_id=7393&alloc_id=16281&op=click
> > _______________________________________________
> > cx-oracle-users mailing list
> > cx-...@li...
> > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> 
> 
> 
> -------------------------------------------------------
> This SF.Net email is sponsored by Oracle Space Sweepstakes
> Want to be the first software developer in space?
> Enter now for the Oracle Space Sweepstakes!
> http://ads.osdn.com/?ad_ids93&alloc_id281&op=click
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> 



		
Discover Yahoo! 
Stay in touch with email, IM, photo sharing and more. Check it out! 
http://discover.yahoo.com/stayintouch.html

Re: [cx-oracle-users] Nulls returned as None and not as as "empty or zero lenght string"

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

you may try to do it at oracle level

select isnull(col,'')...

it should be faster

El mi=C3=A9, 11-05-2005 a las 07:15 -0700, Chris Dunscombe escribi=C3=B3:
> All,
>=20
> Is there anyway that Null values returned from a query can be an "empty=
 or zero length string"
> instead of None?
>=20
> This may seem a strange request as it's easy to use type to test if a n=
ull has been returned as
> None. However I've a performance critical app where I need to output "n=
othing" (zero length /
> empty string) if the value is null. Hence at present I have to perform =
this "type test" on ALL
> columns that don't have a "Not Null" constraint in the database which i=
s quite an overhead when in
> some cases I need to do it millions and sometimes billions of times.
>=20
> Thanks,
>=20
> Chris Dunscombe
>=20
>=20
> 	=09
> __________________________________=20
> Do you Yahoo!?=20
> Read only the mail you want - Yahoo! Mail SpamGuard.=20
> http://promotions.yahoo.com/new_mail=20
>=20
>=20
> -------------------------------------------------------
> This SF.Net email is sponsored by Oracle Space Sweepstakes
> Want to be the first software developer in space?
> Enter now for the Oracle Space Sweepstakes!
> http://ads.osdn.com/?ad_id=3D7393&alloc_id=3D16281&op=3Dclick
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users

Re: [cx-oracle-users] Nulls returned as None and not as as "empty or zero lenght string"

[Chris D. <cdu...@ya...>]

Mihai,

Thanks for that, but I still have to do A test, even if it's:

if val is None:
    val = ''

for each col returned, (many million / billion times)

Cheers,

Chris


--- Mihai Ibanescu <mi...@re...> wrote:

> On Wed, May 11, 2005 at 07:15:23AM -0700, Chris Dunscombe wrote:
> > All,
> > 
> > Is there anyway that Null values returned from a query can be an "empty or zero length string"
> > instead of None?
> > 
> > This may seem a strange request as it's easy to use type to test if a null has been returned
> as
> > None. However I've a performance critical app where I need to output "nothing" (zero length /
> > empty string) if the value is null. Hence at present I have to perform this "type test" on ALL
> > columns that don't have a "Not Null" constraint in the database which is quite an overhead
> when in
> > some cases I need to do it millions and sometimes billions of times.
> 
> You don't have to test with type.
> 
> if val is None:
>     val = ''
> 
> Or, if val is always a string or None:
> val or ''
> 
> will always return a string (empty string if object is None).
> 
> Null objects are very well represented as None. Empty strings being
> represented as Null in Oracle is the weird part here, and I don't think it's
> something cx_Oracle (or any database libarries for that matter) should try to
> fix.
> 
> Hope this helps.
> Misa
> 
> 
> -------------------------------------------------------
> This SF.Net email is sponsored by Oracle Space Sweepstakes
> Want to be the first software developer in space?
> Enter now for the Oracle Space Sweepstakes!
> http://ads.osdn.com/?ad_id=7393&alloc_id=16281&op=click
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> 


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

Re: [cx-oracle-users] Nulls returned as None and not as as "empty or zero lenght string"

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

On Wed, May 11, 2005 at 07:15:23AM -0700, Chris Dunscombe wrote:
> All,
> 
> Is there anyway that Null values returned from a query can be an "empty or zero length string"
> instead of None?
> 
> This may seem a strange request as it's easy to use type to test if a null has been returned as
> None. However I've a performance critical app where I need to output "nothing" (zero length /
> empty string) if the value is null. Hence at present I have to perform this "type test" on ALL
> columns that don't have a "Not Null" constraint in the database which is quite an overhead when in
> some cases I need to do it millions and sometimes billions of times.

You don't have to test with type.

if val is None:
    val = ''

Or, if val is always a string or None:
val or ''

will always return a string (empty string if object is None).

Null objects are very well represented as None. Empty strings being
represented as Null in Oracle is the weird part here, and I don't think it's
something cx_Oracle (or any database libarries for that matter) should try to
fix.

Hope this helps.
Misa

[cx-oracle-users] Nulls returned as None and not as as "empty or zero lenght string"

[Chris D. <cdu...@ya...>]

All,

Is there anyway that Null values returned from a query can be an "empty or zero length string"
instead of None?

This may seem a strange request as it's easy to use type to test if a null has been returned as
None. However I've a performance critical app where I need to output "nothing" (zero length /
empty string) if the value is null. Hence at present I have to perform this "type test" on ALL
columns that don't have a "Not Null" constraint in the database which is quite an overhead when in
some cases I need to do it millions and sometimes billions of times.

Thanks,

Chris Dunscombe


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

[cx-oracle-users] RE: [DB-SIG] cx_Oracle and Unicode ???

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

I am not aware of any algorythm that can extract this information.
I guess this shoud work...
codPythonOracle=3D{
    'WE8ISO8859P1':'iso-8859-1',
    'WE8ISO8859P15':'iso-8859-15',
    'WE8MSWIN1252':'cp1252',
    'AL32UTF8':'utf8',
}

El jue, 05-05-2005 a las 13:08 -0400, Mark Bennett escribi=C3=B3:
> Hello Marcos,
>=20
> Thanks for the info.  I had see reference to the NLS stuff before, but =
didn't know much about it.
>=20
> I ran your basic query:
> 	select value=20
> 	from v$nls_parameters=20
> 	where parameter =3D 'NLS_CHARACTERSET'
>=20
> But what I got back was:
>=20
> VALUE
> -----
> AL32UTF8
>=20
> That doesn't map into your codPythonOracle dictionary.
>=20
> In deference to Guido's comment, since I think this is likely to apply =
to any of the Oracle database drivers (NLS is an Oracle thing), hoping it=
's OK to keep chatting here.
>=20
> Mark
>=20
> -----Original Message-----
> From: Marcos S=C3=A1nchez Provencio [mailto:ma...@bu...]
> Sent: Thursday, May 05, 2005 12:29 PM
> To: gu...@py...
> Cc: cx-...@li...; mar...@gm...
> Subject: Re: [DB-SIG] cx_Oracle and Unicode ???
>=20
>=20
> This is what I do (not tested, I had to trim out a lot of uninteresting
> code):
>=20
> import cx_Oracle as dbapi
>=20
> codPythonOracle=3D{
>     'WE8ISO8859P1':'iso-8859-1',
>     'WE8ISO8859P15':'iso-8859-15',
>     'WE8MSWIN1252':'cp1252',
> }
>=20
> con=3Ddbapi.connect(usuario, clave, dsn)
> sqlCodOracle=3D'''select value=20
>             from v$nls_parameters=20
>             where parameter =3D 'NLS_CHARACTERSET' '''
> cur=3Dcon.cursor()
> cur.execute(sqlCodOracle)
> codOracle=3Dcur.fetchone()[0]
> codificacion=3DcodPythonOracle[codOracle]
>=20
> #Then, encode the sql accordingly
> if isinstance(sql, unicode):
>     sql=3Dsql.encode(self.codificacion)
>    =20
> I hope it helps. This should be handled by cx_Oracle, I suppose...
>=20
>=20
> El jue, 05-05-2005 a las 08:43 -0700, Guido van Rossum escribi=C3=B3:
> > Maybe this is more an issue for the cx-oracle mailing list? (Google f=
or it.)
> >=20
> > I'd be interested in hearing the answer too.
> >=20
> > On 5/5/05, Mark Bennett <mbe...@id...> wrote:
> > > =20
> > > Good morning Folks,=20
> > >  =20
> > > I've looked around on Google for this a few times and really haven'=
t come up
> > > with anything definitive.=20
> > >  =20
> > > I'm trying to update values in Oracle with the .execute() method fr=
om
> > > cx_Oracle.=20
> > >  =20
> > > If I create a Python unicode string object and try to pass it in, P=
ython
> > > complains that .execute() expects a None or string.=20
> > >  =20
> > > If I convert the unicode string to a regular "byte" string beforeha=
nd with
> > > .encode() and utf-8, Python accepts it but when we pull the data ba=
ck from
> > > Oracle we get garbage characters.=20
> > >  =20
> > > Some details:=20
> > >  =20
> > > Python 2.4 with a recent version of cx_Oracle (don't have the exact=
 version
> > > here)  Running on Solaris.=20
> > >  =20
> > > And from our DBA:
> > > =20
> > > Oracle 9 on Solaris.  The database was told to support Unicode.=20
> > >  =20
> > > The character fields are currently VARCHAR2; he believes he's seen
> > > conflicting evidence online about whether or not that is supposed t=
o work.=20
> > > We've also talked about using NVARCHAR2 and NCLOB.=20
> > >  =20
> > > We have not set any environment variables on Solaris.=20
> > >  =20
> > > Thanks in advance,=20
> > > Mark=20
> > >  =20
> > > _______________________________________________
> > > DB-SIG maillist  -  DB...@py...
> > > http://mail.python.org/mailman/listinfo/db-sig
> > >=20
> > >=20
> > >=20
> >=20
> >=20
>=20
>=20

[cx-oracle-users] Re: [DB-SIG] cx_Oracle and Unicode ???

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

This is what I do (not tested, I had to trim out a lot of uninteresting
code):

import cx_Oracle as dbapi

codPythonOracle=3D{
    'WE8ISO8859P1':'iso-8859-1',
    'WE8ISO8859P15':'iso-8859-15',
    'WE8MSWIN1252':'cp1252',
}

con=3Ddbapi.connect(usuario, clave, dsn)
sqlCodOracle=3D'''select value=20
            from v$nls_parameters=20
            where parameter =3D 'NLS_CHARACTERSET' '''
cur=3Dcon.cursor()
cur.execute(sqlCodOracle)
codOracle=3Dcur.fetchone()[0]
codificacion=3DcodPythonOracle[codOracle]

#Then, encode the sql accordingly
if isinstance(sql, unicode):
    sql=3Dsql.encode(self.codificacion)
   =20
I hope it helps. This should be handled by cx_Oracle, I suppose...


El jue, 05-05-2005 a las 08:43 -0700, Guido van Rossum escribi=C3=B3:
> Maybe this is more an issue for the cx-oracle mailing list? (Google for=
 it.)
>=20
> I'd be interested in hearing the answer too.
>=20
> On 5/5/05, Mark Bennett <mbe...@id...> wrote:
> > =20
> > Good morning Folks,=20
> >  =20
> > I've looked around on Google for this a few times and really haven't =
come up
> > with anything definitive.=20
> >  =20
> > I'm trying to update values in Oracle with the .execute() method from
> > cx_Oracle.=20
> >  =20
> > If I create a Python unicode string object and try to pass it in, Pyt=
hon
> > complains that .execute() expects a None or string.=20
> >  =20
> > If I convert the unicode string to a regular "byte" string beforehand=
 with
> > .encode() and utf-8, Python accepts it but when we pull the data back=
 from
> > Oracle we get garbage characters.=20
> >  =20
> > Some details:=20
> >  =20
> > Python 2.4 with a recent version of cx_Oracle (don't have the exact v=
ersion
> > here)  Running on Solaris.=20
> >  =20
> > And from our DBA:
> > =20
> > Oracle 9 on Solaris.  The database was told to support Unicode.=20
> >  =20
> > The character fields are currently VARCHAR2; he believes he's seen
> > conflicting evidence online about whether or not that is supposed to =
work.=20
> > We've also talked about using NVARCHAR2 and NCLOB.=20
> >  =20
> > We have not set any environment variables on Solaris.=20
> >  =20
> > Thanks in advance,=20
> > Mark=20
> >  =20
> > _______________________________________________
> > DB-SIG maillist  -  DB...@py...
> > http://mail.python.org/mailman/listinfo/db-sig
> >=20
> >=20
> >=20
>=20
>=20

Re: [cx-oracle-users] Strange behavior when using SessionPool and cx_Oracle

[Richard M. <ri...@we...>]

Hancock, David (DHANCOCK) wrote:
> above. Currenly, some product report's sizes are bigger than expected
> (>4000 chars), so they are failed to get stored and the error code is: 
> 
>            ORA-01461: can bind a LONG value only for insert into a LONG
> column
> 

I hit a similar issue the other day, your limit of 4000 is the same as 
the cx_Oracle limit on a string, something along the lines of:

  cursor.setinputsizes('3'=cx_Oracle.LONG_STRING)

will get things to the DB rather than failing in the binding.

Rich.
-- 
Richard Moore, Principle Software Engineer,
Westpoint Ltd,
Albion Wharf, 19 Albion Street, Manchester, M1 5LN, England
Tel: +44 161 237 1028
Fax: +44 161 237 1031

[cx-oracle-users] Strange behavior when using SessionPool and cx_Oracle

[Hancock, D. \(DHANCOCK\) <DHA...@ar...>]

Good morning/afternoon/evening!  I've just learned of the list and
signed up.  Below is a question from a developer on our team; it's
probably not specifically a cx_Oracle issue, but we'd appreciate any
ideas list members might have.

Cheers!
--
David Hancock | dha...@ar... | 410-266-4384=20


We use cx_Oracle (4.1) as our driver to connect our Python code and
Oracle DB. The application can either get a connection object from
cx_Oracle.connect('eliu/password@adc') or from the Oracle 10g
SessionPool object's acquire() method. By default, we get a connection
from SessionPool. =20

We have experienced weird behavior of the SessionPool object recently.
Our application tries to insert records into a table repeatly (in a
loop, with the same query statement but different values).  The table
schema and query statement look like this:

	create table product_reports(id varchar2(4), product
varchar2(20), report varchar2(4000));
            insert into product_reports(id, product, report) values (:1,
:2, :3)

The application works fine and all records are saved into the table
properly as long as :1, :2, :3 lengths are within the specified range
above. Currenly, some product report's sizes are bigger than expected
(>4000 chars), so they are failed to get stored and the error code is:=20

           ORA-01461: can bind a LONG value only for insert into a LONG
column

As I mentioned the insertion is in a loop; if one of the records failed
with above reason, then all subsequent records will fail even if all 3
values are valid, with the error code:

           ORA-01003: no statement parsed.=20

I wrote a unit test and found the following:

1. The same code will fail if the connection object obtained from
SessionPool. That means the same code will pass if I use
cx_Oracle.connect('eliu/password@adc') to get the connection object.

2. If the connection object obtained from SessionPool, the query will
work for rest of inserts if failed on the first time. That means:

     2.1 If I need to insert 5 records (in a loop and the 1st record has
large data) into the same table with the same query and    the
application just cut-over (the same query never used since cut-over),
only the first record has ORA-01461 and rest of records inserted
correctly.

     2.2  If I need to insert 5 records (in a loop and the 2nd record
has large data) into the same table with the same query and then the
first record inserted OK, the 2nd will have ORA-01461, and rest of 3
records all have ORA-01003.=20

Is this making any sense to you? I guess this may have something to do
with how Oracle handles statement within the cursor object internally.
Do you have any suggestion? Thanks a lot.

Re: [cx-oracle-users] cx_Oracle and Oracle 10

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

None that I am aware of but 4.1 does have additional support for 10g
capabilities and I provided binaries for the first time with 4.1.

On 4/18/05, Rashawn L Knapp <kn...@cs...> wrote:
> We are currently running cx_Oracle 4.0.1 with Oracle 9.2.0.6.0.  Oracle
> will soon be upgraded to Oracle 10.  I would like to know if the version
> of cx_Oracle we are running has any known issues with Oracle 10.
>=20
> Thank you,
> Rashawn
>=20
> -------------------------------------------------------
> SF email is sponsored by - The IT Product Guide
> Read honest & candid reviews on hundreds of IT Products from real users.
> Discover which products truly live up to the hype. Start reading now.
> http://ads.osdn.com/?ad_id=3D6595&alloc_id=3D14396&op=3Dclick
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

[cx-oracle-users] cx_Oracle and Oracle 10

[Rashawn L K. <kn...@cs...>]

We are currently running cx_Oracle 4.0.1 with Oracle 9.2.0.6.0.  Oracle 
will soon be upgraded to Oracle 10.  I would like to know if the version 
of cx_Oracle we are running has any known issues with Oracle 10.

Thank you,
Rashawn

Re: [cx-oracle-users] Retrieving XLMType from Oracle

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

Sure. Try the following instead:

import cx_Oracle

connection =3D cx_Oracle.Connection("anthony/dev@dev")
cursor =3D connection.cursor()
cursor.execute("""
        select
          Id,
          XMLType.GetClobVal(Value)
        from XMLTable""")
for id, value in cursor:
    print "Id:", id
    print "Value (length):", value.size()
    file("test_%d.xml" % id, "w").write(value.read())

Note the use of XMLType.GetClobVal() since cx_Oracle still does not
know how to handle Oracle named objects -- its on my todo list but too
many other things keep coming up.... :-)

Hope that helps.

On 4/15/05, Carl Bignell <cbi...@nr...> wrote:
> All,
>=20
> I have been trying to retrieve xml data type from
> an oracle database.  The server uses Oracle 10g and
> I am using python 2.4.1, cx_Oracle 4.1, Oracle client for 10g
> all on a windows XP machine (with sp 2).
>=20
> The database has two fields a string and an xmltype. If I
> retrieve with the SQL statment
>=20
> select str from proposal
>=20
> where specifying the string field only all works fine.
>=20
> If I use
>=20
> (a) select * from proposals
>=20
> I recieve the error
>=20
> NotSupportedError: Variable_TypeByOracleDataType: unhandled data type 108
>=20
> (b) setinputsize(value=3Dcx_Oracle.CLOB) with the same select statment
> I recieve the error:
>=20
>      cursor.execute( "select * from proposals" )
> DatabaseError: ORA-01036: illegal variable name/number
>=20
> Any suggestions?  Many thanks in advance.
>=20
> Carl Bignell
> cbi...@nr...
>=20
> -------------------------------------------------------
> SF email is sponsored by - The IT Product Guide
> Read honest & candid reviews on hundreds of IT Products from real users.
> Discover which products truly live up to the hype. Start reading now.
> http://ads.osdn.com/?ad_id=3D6595&alloc_id=3D14396&op=3Dclick
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

Re: [cx-oracle-users] Supporting multiple Oracle Client versions on Win32

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

You are correct. That only works for Windows platforms and in this
case only because Oracle kept the API completely backwards compatible.
It is not, however, forward compatible so attempts to use the Oracle
9i DLL in place of the Oracle 8i DLL will not work. I wouldn't count
on this working in general as well -- this certainly wouldn't have
worked moving from Oracle 7 to Oracle 8.

That said, there is another method for determining what Oracle client
you have installed (and I wish Oracle would make it possible to
determine this in a much more friendly fashion but they don't). You
can (on Windows) look for oraclient8.dll or oraclient9.dll or
oraclient10.dll and on Unix platforms for libclient8.a or libclient9.a
or libclient10.a and take the appropriate action. I have done this for
one of my own projects (cx_OracleDBATools) and it works quite well.
You do have to have the cx_Oracle extension renamed to something like
cx_Oracle_8i.pyd, cx_Oracle 9i.pyd and cx_Oracle10g.pyd and then use
"import cx_Oracle_8i as cx_Oracle" or something like that. Make sense?

On 4/15/05, ama...@ub...
<ama...@ub...> wrote:
>=20
> Hello,
>=20
> > Hoping for a bit of advice and apologies up front if this is a dumb
> > question (it may be).
> >
> > Have an executable Python app built with py2exe that uses cx_Oracle
> > (currently one of the binary versions built for Oracle 8.x). Now in a
> > situation where I need to support both the Oracle 8.x client and the
> > 9.x client (some users have 8.x, others have 9.x).
> >
> > Is there a way I can do this at runtime i.e. detect the Oracle client
> > version and load the correct cx_Oracle version? Guess that implies
> > having multiple versions of the Oracle client and cx_Oracle installed
> > on the PC where I build the app with py2exe. What I'm hoping to
> > provide a single executable that will work for all Oracle client
> > versions.
>=20
> The cx_Oracle built for Oracle 8 should work as well on a 9.x client.
> There is nothing to do except to ensure that OCI.DLL can be located
> through the PATH environment variable.
>=20
> I guess this is only true for Windows platforms...
>=20
> --
> Amaury Forgeot d'Arc
> Ubix Development
> www.ubitrade.com
>=20
>=20
> -------------------------------------------------------
> SF email is sponsored by - The IT Product Guide
> Read honest & candid reviews on hundreds of IT Products from real users.
> Discover which products truly live up to the hype. Start reading now.
> http://ads.osdn.com/?ad_id=3D6595&alloc_id=3D14396&op=3Dclick
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

RE: [cx-oracle-users] Determinining version of cx_Oracle

[Nik B. <Nik...@pe...>]

Hi,

> I would like to know how to check the version of cx_Oracle.  I am a 
> rather new user to cx_Oracle and Python, so I am having a 
> little trouble understanding some of the documentation that is out there
on 
> cx_Oracle.

Nice and easy: 

>>> import cx_Oracle
>>> cx_Oracle.version
'4.0'
>>>

You can also get the version of the database you've connected to:

>>> connectionObj = cx_Oracle.connect("user/password@database")
>>> connectionObj.version
'9.2.0.1.0'
>>> 

Hope this helps!

Nik

[cx-oracle-users] Determinining version of cx_Oracle

[Rashawn L K. <kn...@cs...>]

I would like to know how to check the version of cx_Oracle.  I am a 
rather new user to cx_Oracle and Python, so I am having a little trouble 
understanding some of the documentation that is out there on cx_Oracle.

Thank you,
Rashawn

[cx-oracle-users] Retrieving XLMType from Oracle

[Carl B. <cbi...@nr...>]

All,

I have been trying to retrieve xml data type from
an oracle database.  The server uses Oracle 10g and
I am using python 2.4.1, cx_Oracle 4.1, Oracle client for 10g
all on a windows XP machine (with sp 2).

The database has two fields a string and an xmltype. If I
retrieve with the SQL statment

select str from proposal

where specifying the string field only all works fine.

If I use

(a) select * from proposals

I recieve the error

NotSupportedError: Variable_TypeByOracleDataType: unhandled data type 108

(b) setinputsize(value=cx_Oracle.CLOB) with the same select statment
I recieve the error:

     cursor.execute( "select * from proposals" )
DatabaseError: ORA-01036: illegal variable name/number

Any suggestions?  Many thanks in advance.

Carl Bignell
cbi...@nr...

Re: [cx-oracle-users] Supporting multiple Oracle Client versions on Win32

[<ama...@ub...>]

Hello,

> Hoping for a bit of advice and apologies up front if this is a dumb
> question (it may be).
>
> Have an executable Python app built with py2exe that uses cx_Oracle
> (currently one of the binary versions built for Oracle 8.x). Now in a
> situation where I need to support both the Oracle 8.x client and the
> 9.x client (some users have 8.x, others have 9.x).
>
> Is there a way I can do this at runtime i.e. detect the Oracle client
> version and load the correct cx_Oracle version? Guess that implies
> having multiple versions of the Oracle client and cx_Oracle installed
> on the PC where I build the app with py2exe. What I'm hoping to
> provide a single executable that will work for all Oracle client
> versions.

The cx_Oracle built for Oracle 8 should work as well on a 9.x client.
There is nothing to do except to ensure that OCI.DLL can be located
through the PATH environment variable.

I guess this is only true for Windows platforms...

--
Amaury Forgeot d'Arc
Ubix Development
www.ubitrade.com

[cx-oracle-users] Supporting multiple Oracle Client versions on Win32

[Harry F. <hf...@gm...>]

Hoping for a bit of advice and apologies up front if this is a dumb
question (it may be).

Have an executable Python app built with py2exe that uses cx_Oracle
(currently one of the binary versions built for Oracle 8.x). Now in a
situation where I need to support both the Oracle 8.x client and the
9.x client (some users have 8.x, others have 9.x).

Is there a way I can do this at runtime i.e. detect the Oracle client
version and load the correct cx_Oracle version? Guess that implies
having multiple versions of the Oracle client and cx_Oracle installed
on the PC where I build the app with py2exe. What I'm hoping to
provide a single executable that will work for all Oracle client
versions.

Many thanks,

Harry

Re: [cx-oracle-users] trouble with insert

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

Andrew Replogle wrote:

>         cursor.execute("""
>             INSERT INTO CONTENT_STYLE (CONTENT_STYLE_ID, NAME, DESCRIPTION, THEME, CLIENT, DELETED)
>             VALUES (:content_id, ':vizstylename', ':themedesc', ':themename', ':vizstylename', 0)"
> "",
>             content_id = nextval, themename = sys.argv[4], vizstylename = sys.argv[5], themedesc = sys.argv[6])

Replacing

  VALUES (:content_id, ':vizstylename', ':themedesc', ':themename', 
':vizstylename', 0)

with

  VALUES (:content_id, :vizstylename, :themedesc, :themename, 
:vizstylename, 0)

should fix your problem.

HTH,
    Walter Dörwald

RE: [cx-oracle-users] trouble with insert

[Guido v. R. <gu...@el...>]

I think the problem is this bit of SQL:
=20
VALUES (:content_id, ':vizstylename', ':themedesc', ':themename',
':vizstylename', 0)
=20
Note how you put 'single quotes' around some of the variable names. Try
without those.
=20
=20
=20

--Guido van Rossum (home page: http://www.python.org/~guido)


=20


________________________________

	From: cx-...@li...
[mailto:cx-...@li...] On Behalf Of Andrew
Replogle
	Sent: Thursday, April 14, 2005 12:54 PM
	To: cx-...@li...
	Subject: [cx-oracle-users] trouble with insert
=09
=09
=09
	---
	#!/usr/bin/python
	=20
	import sys
	import cx_Oracle
	=20
	=20
	dbinst =3D sys.argv
	[1]
	dbuser =3D sys.argv[2]
	dbpass =3D sys.argv[3]
	=20
	try:
	    myconn =3D cx_Oracle.connect(dbuser,dbpass,dbinst)
	except cx_Oracle.DatabaseError, x:
	   =20
	print ""
	    print "Unable to connect to Database."
	    print
	 x
	=20
	=20
	=20
	cursor =3D myconn.cursor()
	cursor.arraysize =3D 50
	cursor.execute("""
	        SELECT content_style_id_seq.nextval
	        FROM dual"
	"")
	=20
	=20
	def add_theme():
	    for content_style_id in cursor.fetchall():
	        nextval =3D str(content_style_id[0])
=09
	        cursor.execute("""
	            INSERT INTO CONTENT_STYLE (CONTENT_STYLE_ID, NAME,
DESCRIPTION, THEME, CLIENT, DELETED)
	            VALUES (:content_id, ':vizstylename', ':themedesc',
':themename', ':vizstylename', 0)"
	"",
	            content_id =3D nextval, themename =3D sys.argv[4],
vizstylename =3D sys.argv[5], themedesc =3D sys.argv[6])
	        myResults =3D cursor.fetchall()
	       =20
	if cursor.rowcount =3D=3D 0:
	            print "The theme could not be added for an unknown
reason."
	        else:
=09
	            cursor.execute("""COMMIT""")
	            print=20
	"Theme ",sys.argv[4],"added with content style",sys.argv[5],"and
description",sys.argv[6]
	=20
	=20
	add_theme()
	---
=09
=09
	andrewr@zepher:~/bin$ ./add_theme.py PSG MGMTUSER01 MGMTUSER01
prenhalltheme prenhall PrenticeHall
	Traceback (most recent call last):
	  File "./add_theme.py", line 42, in ?
	    add_theme()
	  File "./add_theme.py", line 33, in add_theme
=09
	    content_id =3D nextval, themename =3D sys.argv[4], vizstylename
=3D sys.argv[5], themedesc =3D sys.argv[6])
	cx_Oracle.DatabaseError: ORA-01036: illegal variable name/number
	---
=09
	I have no idea what the illegal variable name / number is.. I've
read issues where the variable is 32 char or longer or if a :=20
=09
	missing in front of the variable assignment .. but none of these
apply here.. can anyone tell me what I'm doing wrong?
=09
	TIA,
=09
	Andrew Replogle
=09

[cx-oracle-users] trouble with insert

[Andrew R. <and...@gm...>]

---
#!/usr/bin/python
=20
import sys
import cx_Oracle
=20
=20
dbinst =3D sys.argv[1]
dbuser =3D sys.argv[2]
dbpass =3D sys.argv[3]
=20
try:
    myconn =3D cx_Oracle.connect(dbuser,dbpass,dbinst)
except cx_Oracle.DatabaseError, x:
    print ""
    print "Unable to connect to Database."
    print x
=20
=20
=20
cursor =3D myconn.cursor()
cursor.arraysize =3D 50
cursor.execute("""
        SELECT content_style_id_seq.nextval
        FROM dual""")
=20
=20
def add_theme():
    for content_style_id in cursor.fetchall():
        nextval =3D str(content_style_id[0])
        cursor.execute("""
            INSERT INTO CONTENT_STYLE (CONTENT_STYLE_ID, NAME,
DESCRIPTION, THEME, CLIENT, DELETED)
            VALUES (:content_id, ':vizstylename', ':themedesc',
':themename', ':vizstylename', 0)""",
            content_id =3D nextval, themename =3D sys.argv[4],
vizstylename =3D sys.argv[5], themedesc =3D sys.argv[6])
        myResults =3D cursor.fetchall()
        if cursor.rowcount =3D=3D 0:
            print "The theme could not be added for an unknown reason."
        else:
            cursor.execute("""COMMIT""")
            print "Theme ",sys.argv[4],"added with content
style",sys.argv[5],"and description",sys.argv[6]
=20
=20
add_theme()
---

andrewr@zepher:~/bin$ ./add_theme.py PSG MGMTUSER01 MGMTUSER01
prenhalltheme prenhall PrenticeHall
Traceback (most recent call last):
  File "./add_theme.py", line 42, in ?
    add_theme()
  File "./add_theme.py", line 33, in add_theme
    content_id =3D nextval, themename =3D sys.argv[4], vizstylename =3D
sys.argv[5], themedesc =3D sys.argv[6])
cx_Oracle.DatabaseError: ORA-01036: illegal variable name/number
---

I have no idea what the illegal variable name / number is.. I've read
issues where the variable is 32 char or longer or if a :
missing in front of the variable assignment .. but none of these apply
here.. can anyone tell me what I'm doing wrong?

TIA,

Andrew Replogle

Re: [cx-oracle-users] using bind variables to insert into xmltype colums

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

Glad to see you got it working. Apologies for dropping the
xmltype(:value) from the example I gave you. It was in my code that
was working, of course. :-)

On Apr 8, 2005 2:58 PM, Charl P. Botha <cp...@gm...> wrote:
> On Apr 8, 2005 6:39 PM, Charl P. Botha <cp...@gm...> wrote:
> > I've attached a complete and simple example that illustrates this, at
> > least on my setup.  It also creates the necessary table called
> > "xmltable".  It drops the old "xmltable", so be careful.  After
> > changing the username/password, you have to do "import simple_ex;
> > simple_ex.main()" from the python interpreter.
> 
> Changing the execute line to:
> cursor.execute("insert into xmltable values (xmltype(:text_clob))",
>                         text_clob = very_long_xml_text)
> i.e. adding the xmltype() fixed this!
> 
> Thanks very much!
> 
> --
> charl p. botha http://cpbotha.net/ http://visualisation.tudelft.nl/
> 
> -------------------------------------------------------
> SF email is sponsored by - The IT Product Guide
> Read honest & candid reviews on hundreds of IT Products from real users.
> Discover which products truly live up to the hype. Start reading now.
> http://ads.osdn.com/?ad_id=6595&alloc_id=14396&op=click
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

Re: [cx-oracle-users] using bind variables to insert into xmltype colums

[Charl P. B. <cp...@gm...>]

On Apr 8, 2005 6:39 PM, Charl P. Botha <cp...@gm...> wrote:
> I've attached a complete and simple example that illustrates this, at
> least on my setup.  It also creates the necessary table called
> "xmltable".  It drops the old "xmltable", so be careful.  After
> changing the username/password, you have to do "import simple_ex;
> simple_ex.main()" from the python interpreter.

Changing the execute line to:
cursor.execute("insert into xmltable values (xmltype(:text_clob))",
                        text_clob = very_long_xml_text)
i.e. adding the xmltype() fixed this!

Thanks very much!

-- 
charl p. botha http://cpbotha.net/ http://visualisation.tudelft.nl/

Re: [cx-oracle-users] using bind variables to insert into xmltype colums

[Charl P. B. <cp...@gm...>]

On Apr 8, 2005 6:10 PM, Anthony Tuininga <ant...@gm...> wrote:
> connection = cx_Oracle.Connection("user/pw@tns")
> cursor = connection.cursor()
> cursor.setinputsizes(value = cx_Oracle.CLOB)
> cursor.execute("insert into xmltable values (:value)",
>         value = "A very long XML string")
> 
> where in my case the xml was about 50 KB. Hope this helps! If not, can
> you post the exact code you are using so I can see if anything obvious
> shows up. Thanks.

Thank you very much for the quick answer Anthony!

It seems in my example I forgot the "setinputsizes()"; I've now added
this and I don't see the ORA-01461 message anymore, but rather a
different error message: "ORA-00932: inconsistent datatypes: expected
- got CLOB"

I've attached a complete and simple example that illustrates this, at
least on my setup.  It also creates the necessary table called
"xmltable".  It drops the old "xmltable", so be careful.  After
changing the username/password, you have to do "import simple_ex;
simple_ex.main()" from the python interpreter.

Thanks for looking into this!

Regards,
Charl

-- 
charl p. botha http://cpbotha.net/ http://visualisation.tudelft.nl/

Re: [cx-oracle-users] using bind variables to insert into xmltype colums

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

The following code works for me:

connection = cx_Oracle.Connection("user/pw@tns")
cursor = connection.cursor()
cursor.setinputsizes(value = cx_Oracle.CLOB)
cursor.execute("insert into xmltable values (:value)",
        value = "A very long XML string")

where in my case the xml was about 50 KB. Hope this helps! If not, can
you post the exact code you are using so I can see if anything obvious
shows up. Thanks.

On Apr 7, 2005 5:33 PM, Charl P. Botha <cp...@gm...> wrote:
> Dear list,
> 
> I'm trying to insert xml into xmltype columns.  My xml is longer than
> 4000 characters, so I get "ORA-01704: string literal too long" if I
> try and insert my xml as a string literal:
> cursor.execute("insert into table xmltable values(xmltype('<xml>very
> long string...</xml>'))")
> 
> As far as I could see, it seems one is supposed to use bind variables
> in cases such as these.  So I tried something similar to the
> following:
> 
> xml_clob = cx_Oracle.CLOB
> cursor.execute("insert into table xmltable values(xmltype(:xml_clob))",
>                       xml_clob = '<xml>very long string...</xml>')
> 
> This yields a: "ORA-01461: can bind a LONG value only for insert into
> a LONG column".
> 
> How else can I insert long XML strings into xmltype columns from
> cx_Oracle?  A temporary column of sorts?  Any tips on this would be
> greatly appreciated!
> 
> Thanks,
> Charl
> 
> --
> charl p. botha http://cpbotha.net/ http://visualisation.tudelft.nl/
> 
> -------------------------------------------------------
> SF email is sponsored by - The IT Product Guide
> Read honest & candid reviews on hundreds of IT Products from real users.
> Discover which products truly live up to the hype. Start reading now.
> http://ads.osdn.com/?ad_id=6595&alloc_id=14396&op=click
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>