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

Re: [cx-oracle-users] ORA-01461 when inserting into VARCHAR2 column

[Rodney B. <ba...@pl...>]

> I'm finding cases where inserting a string (via bind variable) with
> length > 2000 into a VARCHAR2 column fails with "ORA-01461: can bind
> a LONG value only for insert into a LONG column".
>
> In my test case, this happens when the first insert uses a unicode
> bind variable (not necessarily > 2000 in length).  Subsequent inserts
>  using either str or unicode bind variables > 2000 in length fail
> with ORA-01461.
>
> If the first insert uses a str bind variable, subsequent inserts
> succeed with either str or unicode.

After further investigation, there seem to be two things happening here.

Firstly, when binding a unicode variable, cx_Oracle seems to use
SQLCS_NCHAR, but for a str variable, it uses SQLCS_IMPLICIT.  The national
characterset for my database is AL16UTF16, so I guess this means that Oracle
sees that 2001 characters won't fit in the 4000 byte maximum for NVARCHAR2
using UTF-16 and converts to LONG instead.

Secondly, the decision to use SQLCS_NCHAR is not revisited when the previous
statement passed to execute() is the same as the current one.  This means
that str variables can end up being treated like unicode variables and vice
versa.

I'm not using NVARCHAR2, so I don't think using SQLCS_NCHAR is ever useful
for my application; however, I guess it is useful for others.

Rodney 

[cx-oracle-users] NLS_LANG, (NC)LOBs, and ORA-01403

[Glyph L. <gl...@tw...>]

Hello cx_ers,

I have two database servers.  One is for local development ("dev") and one is a remote testing database ("test").

Both work fine with an NLS_LANG environment variable unset or using ".US7ASCII".  But then, of course, I can't read or write any non-ASCII data without getting encoding errors.

So, I set NLS_LANG to ".UTF8".  Against dev, everything works fine.  But, against test, I get a strange error.  When I attempt to .read() a LOB object (specifically, one which represents a NCLOB column), I get an ORA-01403 "no data found" error.

Strangely, dev's database character set is AL16UTF16 (i.e. it requires transcoding from the wire input coming from my program) but test is UTF8; in other words, the wire and database encodings match for test, so if anything I would expect it to work _better_.

Has anyone else encountered this issue?  Is it a bug in cx_Oracle?  Is there another value i could use in NLS_LANG to work around it?  The only thing that I can find which discusses such an error is this rather oblique reference: <http://download.oracle.com/docs/cd/B25006_02/repca.1012/repca/ts.htm#BABEFBFA>.

Thanks for any and all help,

-glyph

Re: [cx-oracle-users] Does cx_Oracle support embedded sql selection?

[Gerrat R. <gri...@co...>]

Harald,

The original sql is valid Oracle SQL, and works fine with cx_Oracle.
The only time you need to provide an alias is when you directly refer to the subquery 
somewhere else in your query.
You may be thinking of another RDBMS (like MS Sql Server).


Dehong,
Perhaps you could provide the error you're getting and, as Amaury mentioned, 
the exact sql statement you're trying.


Massa, Harald Armin wrote on 2011-05-23:

> Dehong,
> 
> that is no valid Oracle SQL.
> 
> To use a query within a FROM-Clause, you have to give an alias. As in
> 
> select count(*) from
> (
> select * from employee
> ) emp
> 
> 
> ( I assume we are talking test-sql; no production system should use 
> "select *" or unrestricted count)
> 
> Harald
> 
> 
> It seems that cx_Oracle will return syntax error if the sql string has 
> the
> 
> 
> 	embedded sql selection statements.
> 
> 	such as,
> 	select count(*) from
> 	(
> 	select * from employee
> 	)
> 
> 	Is there any solution for it?
> 
> 
> 
>

Regards,
Gerrat

[cx-oracle-users] ORA-01461 when inserting into VARCHAR2 column

[Rodney B. <ba...@pl...>]

I typically use cx_Oracle via SQLAlchemy and had trouble providing Unicode
variables to an insert statement.  They redirected me here.  While preparing
a test case, I found the problem to be a bit different than I expected.

I'm finding cases where inserting a string (via bind variable) with length >
2000 into a VARCHAR2 column fails with "ORA-01461: can bind a LONG value
only for insert into a LONG column".

In my test case, this happens when the first insert uses a unicode bind
variable (not necessarily > 2000 in length).  Subsequent inserts using
either str or unicode bind variables > 2000 in length fail with ORA-01461.

If the first insert uses a str bind variable, subsequent inserts succeed
with either str or unicode.

If this is expected behavior, is there a setting that can be used to avoid
the problem?

Some system specifics:
Oracle version       : 10gXE
Database characterset: AL32UTF8
NLS_LANG variable    : AMERICAN_AMERICA.AL32UTF8
cx_Oracle version    : 5.1
python version       : 2.6

Rodney

Re: [cx-oracle-users] Does cx_Oracle support embedded sql selection?

[Massa, H. A. <ch...@gh...>]

Dehong,

that is no valid Oracle SQL.

To use a query within a FROM-Clause, you have to give an alias. As in

select count(*) from
(
select * from employee
) emp

( I assume we are talking test-sql; no production system should use "select
*" or unrestricted count)

Harald


It seems that cx_Oracle will return syntax error if the sql string has the

> embedded sql selection statements.
>
> such as,
> select count(*) from
> (
> select * from employee
> )
>
> Is there any solution for it?
>
>
-- 
GHUM GmbH
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607

Amtsgericht Stuttgart, HRB 734971
-
persuadere.
et programmare

Re: [cx-oracle-users] Does cx_Oracle support embedded sql selection?

[Amaury F. d'A. <ama...@gm...>]

Hi,

2011/5/23  <Deh...@me...>:
>
> It seems that cx_Oracle will return syntax error if the sql string has the
> embedded sql selection statements.
>
> such as,
> select count(*) from
> (
> select * from employee
> )
>
> Is there any solution for it?

cx_Oracle does not try to parse the SQL statement, it passes it
directly to the Oracle server.
Like sql*plus... except that the semicolon at the end must be removed!
Do you have an example of code that fails?

-- 
Amaury Forgeot d'Arc

[cx-oracle-users] Does cx_Oracle support embedded sql selection?

[<Deh...@me...>]

It seems that cx_Oracle will return syntax error if the sql string has the
embedded sql selection statements.

such as,
select count(*) from
(
select * from employee
)

Is there any solution for it?

This message and any attachment are confidential and may be privileged or
otherwise protected from disclosure. If you are not the intended recipient,
you must not copy this message or attachment or disclose the contents to
any other person. If you have received this transmission in error, please
notify the sender immediately and delete the message and any attachment
from your system. Merck KGaA, Darmstadt, Germany and any of its
subsidiaries do not accept liability for any omissions or errors in this
message which may arise as a result of E-Mail-transmission or for damages
resulting from any unauthorized changes of the content of this message and
any attachment thereto. Merck KGaA, Darmstadt, Germany and any of its
subsidiaries do not guarantee that this message is free of viruses and does
not accept liability for any damages caused by any virus transmitted
therewith.

Click http://disclaimer.merck.de to access the German, French, Spanish and
Portuguese versions of this disclaimer.

Re: [cx-oracle-users] cx_oracle does not close TCP/IP connections on close(); relies on Python garbage collection

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

On Tue, May 17, 2011 at 9:18 AM, Michael Bayer <mi...@zz...> wrote:
>
> On May 14, 2011, at 7:12 PM, Anthony Tuininga wrote:
>
>> On Sat, May 14, 2011 at 10:46 AM, Amaury Forgeot d'Arc
>> <ama...@gm...> wrote:
>>> Hi,
>>>
>>> 2011/5/14 Michael Bayer <mi...@zz...>:
>>>> I'm assuming this is intentional behavior, as illustrated by the script below.
>>>> Wondering what the rationale is for delaying the close of a connection until the object is garbage collected
>>>
>>> It's true that the .close() method only calls OCISessionEnd().
>>> OCIServerDetach() is only called by the destructor.
>>
>> I have just checked in changes to do exactly this. It calls
>> OCIServerDetach() on close. Let me know if this resolves the problem
>> for you. Thanks.
>
>
>  I had already worked around the issue on our end, but the new changes work great, thanks !

You're welcome. And thanks for following up. These changes will be
included in the next release -- not sure when that will be yet,
though. I have a few other things I'd like to take care of first.

Anthony

Re: [cx-oracle-users] cx_oracle does not close TCP/IP connections on close(); relies on Python garbage collection

[Michael B. <mi...@zz...>]

On May 14, 2011, at 7:12 PM, Anthony Tuininga wrote:

> On Sat, May 14, 2011 at 10:46 AM, Amaury Forgeot d'Arc
> <ama...@gm...> wrote:
>> Hi,
>> 
>> 2011/5/14 Michael Bayer <mi...@zz...>:
>>> I'm assuming this is intentional behavior, as illustrated by the script below.
>>> Wondering what the rationale is for delaying the close of a connection until the object is garbage collected
>> 
>> It's true that the .close() method only calls OCISessionEnd().
>> OCIServerDetach() is only called by the destructor.
> 
> I have just checked in changes to do exactly this. It calls
> OCIServerDetach() on close. Let me know if this resolves the problem
> for you. Thanks.


  I had already worked around the issue on our end, but the new changes work great, thanks !

Re: [cx-oracle-users] cx_oracle does not close TCP/IP connections on close(); relies on Python garbage collection

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

On Sat, May 14, 2011 at 10:46 AM, Amaury Forgeot d'Arc
<ama...@gm...> wrote:
> Hi,
>
> 2011/5/14 Michael Bayer <mi...@zz...>:
>> I'm assuming this is intentional behavior, as illustrated by the script below.
>> Wondering what the rationale is for delaying the close of a connection until the object is garbage collected
>
> It's true that the .close() method only calls OCISessionEnd().
> OCIServerDetach() is only called by the destructor.

I have just checked in changes to do exactly this. It calls
OCIServerDetach() on close. Let me know if this resolves the problem
for you. Thanks.

Anthony

Re: [cx-oracle-users] cx_oracle does not close TCP/IP connections on close(); relies on Python garbage collection

[Michael B. <mi...@zz...>]

On May 14, 2011, at 12:46 PM, Amaury Forgeot d'Arc wrote:

> Hi,
> 
> 2011/5/14 Michael Bayer <mi...@zz...>:
>> I'm assuming this is intentional behavior, as illustrated by the script below.
>> Wondering what the rationale is for delaying the close of a connection until the object is garbage collected
> 
> It's true that the .close() method only calls OCISessionEnd().
> OCIServerDetach() is only called by the destructor.
> 
> A former colleague already noticed this: after close(), the connection
> disappears from v$session,
> but is still present in v$process (or v$thread, I don't remember)
> Since a closed connection can't be reopened, does this makes sense to
> keep the connection alive?
> IMO close() should release all resources, especially on the database server...


Specifically the issue we're having running SQLAlchemy unit tests, particularly the ones that test pools and connection configuration thereby by definition circumventing the usual practice of using a single connection pool, leave a few connections piled up which are closed, but in some cases are still in memory (fixing that now to seek and destroy absolutely every connection).   What happens next is you start getting ORA-12519 errors - no more connection slots left in which to connect.   What is particularly insidious about ORA-12519 is that even if the connection is fully closed, Oracle doesn't release the memory for a *full minute longer*, since apparently no message is sent to the listener to "deallocate" on close - its based on some kind of ping/timeout mechanism.      

Re: [cx-oracle-users] cx_oracle does not close TCP/IP connections on close(); relies on Python garbage collection

[Amaury F. d'A. <ama...@gm...>]

Hi,

2011/5/14 Michael Bayer <mi...@zz...>:
> I'm assuming this is intentional behavior, as illustrated by the script below.
> Wondering what the rationale is for delaying the close of a connection until the object is garbage collected

It's true that the .close() method only calls OCISessionEnd().
OCIServerDetach() is only called by the destructor.

A former colleague already noticed this: after close(), the connection
disappears from v$session,
but is still present in v$process (or v$thread, I don't remember)
Since a closed connection can't be reopened, does this makes sense to
keep the connection alive?
IMO close() should release all resources, especially on the database server...

-- 
Amaury Forgeot d'Arc

[cx-oracle-users] cx_oracle does not close TCP/IP connections on close(); relies on Python garbage collection

[Michael B. <mi...@zz...>]

I'm assuming this is intentional behavior, as illustrated by the script below.    Wondering what the rationale is for delaying the close of a connection until the object is garbage collected - in particular, this makes it difficult for applications that are attempting to manage the total number of TCP connections opened, where the actual connection object might still be referenced somewhere.    Such an approach is even more problematic if and when cx_Oracle begins to be run on platforms such as Pypy where reference counting GC isn't used, as garbage collection can be delayed entirely for any amount of time.

This is cx_Oracle 5.1, script is tested on Mac OSX as well as Fedora 14.  The script creates one connection, closes it, illustrates what network connections are set up to port 1521.  Then del is called, network connections are illustrated again.   On both platforms, the one ESTABLISHED connection goes to TIME_WAIT only after "del c" is called.

import os
import cx_Oracle
import time

print "Version:", cx_Oracle.version

c = cx_Oracle.connect(
    dsn='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))(CONNECT_DATA=(SID=xe)))', 
    password='tiger', 
    user='scott', 
)

c.close()
time.sleep(.01)

print "-" * 20
os.system("netstat -nt | grep 1521")

del c
time.sleep(.01)

print "-" * 20
os.system("netstat -nt | grep 1521")


output on OSX:

Version: 5.1
--------------------
tcp4       0      0  127.0.0.1.1521         127.0.0.1.60111        ESTABLISHED
tcp4       0      0  127.0.0.1.60111        127.0.0.1.1521         ESTABLISHED
--------------------
tcp4       0      0  127.0.0.1.1521         127.0.0.1.60111        CLOSE_WAIT
tcp4       0      0  127.0.0.1.60111        127.0.0.1.1521         FIN_WAIT_2


output on Fedora 14 (this is the server where Oracle is running, so some 1521s are already present):

Version: 5.1
--------------------
tcp        0      0 66.228.40.238:48786         66.228.40.238:1521          ESTABLISHED 
tcp        0      0 127.0.0.1:49302             127.0.0.1:1521              ESTABLISHED 
tcp        0      0 127.0.0.1:1521              127.0.0.1:49302             ESTABLISHED 
tcp        0      0 66.228.40.238:1521          66.228.40.238:48786         ESTABLISHED 
--------------------
tcp        0      0 66.228.40.238:48786         66.228.40.238:1521          ESTABLISHED 
tcp        0      0 127.0.0.1:49302             127.0.0.1:1521              TIME_WAIT   
tcp        0      0 66.228.40.238:1521          66.228.40.238:48786         ESTABLISHED 

Re: [cx-oracle-users] maintaining multiple connections with different encodings

[Doug H. <djh...@te...>]

I use a mixed bag of tools, including SQL*Plus, SQL Developer, and
python scripts using cx_Oracle. I need to be able to correctly store and
retrieve a wide range of international characters with all of these
tools. It was very frustrating to have different results depending on
the tools and the environmental configuration. BTW, cx_Oracle always
seemed to work correctly.

I ran some experiments with SQL*Plus from the 11.1 instant client on a
Win7 box with a LAN connected linux box running a 10g server.

SQL*Plus does not recognize the BOM in an  8-bit file script file. It
gets treated as an invalid character.
As far as I could tell, it does not recognize a 16-bit Unicode file at all.

It does read 8-bit source files, and the characters are interpreted
according the setting of the NLS_LANG environment variable.

I use NLS_LANG=ENGLISH_CANADA.UTF8 in my windows batch files to get
utf-8 encoded characters properly loaded into oracle tables.

The NCHR function takes a 16-bit binary unicode code point and creates
the correct character in the database, similar to CHR which takes a
8-bit code to create an ASCII character. This is helpful for generating
the correct character when you cannot rely on the NLS_LANG setting.

One needs to be careful in interpreting the terminal output from
SQL*Plus, as it is sent to the command window, where it may be
incorrectly displayed. For this reason, I use the sql DUMP function to
display the binary content of the database column, instead of relying on
the displayed characters in the command window.

On 2011-05-10 11:47, Anthony Tuininga wrote:
> FYI, I just found out that SQL*Plus on Windows does NOT support
> Unicode at all. If you need to insert Unicode data use SQL Developer
> or i*SQLPlus -- or cx_Oracle, of course! :-)
>
> So there is a very good possibility that SQL*Plus screwed up your data for you.
>
> Glad to hear you got the problem resolved, though.
>
> Anthony
>
> <snip>

-- 
Doug Henderson, Calgary, Alberta, Canada

Re: [cx-oracle-users] maintaining multiple connections with different encodings

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

FYI, I just found out that SQL*Plus on Windows does NOT support
Unicode at all. If you need to insert Unicode data use SQL Developer
or i*SQLPlus -- or cx_Oracle, of course! :-)

So there is a very good possibility that SQL*Plus screwed up your data for you.

Glad to hear you got the problem resolved, though.

Anthony

On Tue, May 10, 2011 at 7:37 AM, Dana Pieluszczak <da...@gm...> wrote:
> Looks like this is an ID10T error. I had previously inserted some data
> into my test table, which, on the latin-1 database was neither valid
> latin-1 or valid utf-8 (I blame sqlplus). So as Anthony had said
> before, setting NLS_LANG to 'AMERICAN_AMERICA.UTF8' gives me utf-8
> encoded data from both databases.
>
> Thanks for the tip about DUMP(), I didn't know that function existed
> and wouldn't have figured this out without it.
>
> Dana P
>
> On Mon, May 9, 2011 at 6:41 PM, Doug Henderson <djh...@te...> wrote:
>> On 2011-05-09 13:28, Dana Pieluszczak wrote:
>>> <snip>
>>> The table below is defined as (NLS_LENGTH_SEMANTICS IS 'BYTE' for both
>>> databases):
>>>
>>> CREATE TABLE test (str VARCHAR2(30 BYTE));
>>>
>>> <snip>
>> What are the values of NLS_CHARACTERSET and NLS_NCHAR_CHARACTERSET from
>> the sys.nls_database_parameters view? Mine are AL32UTF8 and AL16UTF16
>> respectively.
>>
>> It may be illuminating to use this query:
>>
>> select str, dump(str) from test;
>>
>> to see what is actually stored in the table. This is what I get:
>> dätä    Typ=1 Len=6: 100,195,164,116,195,164
>>
>> --
>> Doug Henderson, Calgary, Alberta, Canada
>>
>>
>> ------------------------------------------------------------------------------
>> Achieve unprecedented app performance and reliability
>> What every C/C++ and Fortran developer should know.
>> Learn how Intel has extended the reach of its next-generation tools
>> to help boost performance applications - inlcuding clusters.
>> http://p.sf.net/sfu/intel-dev2devmay
>> _______________________________________________
>> cx-oracle-users mailing list
>> cx-...@li...
>> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>>
>
> ------------------------------------------------------------------------------
> Achieve unprecedented app performance and reliability
> What every C/C++ and Fortran developer should know.
> Learn how Intel has extended the reach of its next-generation tools
> to help boost performance applications - inlcuding clusters.
> http://p.sf.net/sfu/intel-dev2devmay
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

Re: [cx-oracle-users] maintaining multiple connections with different encodings

[Dana P. <da...@gm...>]

Looks like this is an ID10T error. I had previously inserted some data
into my test table, which, on the latin-1 database was neither valid
latin-1 or valid utf-8 (I blame sqlplus). So as Anthony had said
before, setting NLS_LANG to 'AMERICAN_AMERICA.UTF8' gives me utf-8
encoded data from both databases.

Thanks for the tip about DUMP(), I didn't know that function existed
and wouldn't have figured this out without it.

Dana P

On Mon, May 9, 2011 at 6:41 PM, Doug Henderson <djh...@te...> wrote:
> On 2011-05-09 13:28, Dana Pieluszczak wrote:
>> <snip>
>> The table below is defined as (NLS_LENGTH_SEMANTICS IS 'BYTE' for both
>> databases):
>>
>> CREATE TABLE test (str VARCHAR2(30 BYTE));
>>
>> <snip>
> What are the values of NLS_CHARACTERSET and NLS_NCHAR_CHARACTERSET from
> the sys.nls_database_parameters view? Mine are AL32UTF8 and AL16UTF16
> respectively.
>
> It may be illuminating to use this query:
>
> select str, dump(str) from test;
>
> to see what is actually stored in the table. This is what I get:
> dätä    Typ=1 Len=6: 100,195,164,116,195,164
>
> --
> Doug Henderson, Calgary, Alberta, Canada
>
>
> ------------------------------------------------------------------------------
> Achieve unprecedented app performance and reliability
> What every C/C++ and Fortran developer should know.
> Learn how Intel has extended the reach of its next-generation tools
> to help boost performance applications - inlcuding clusters.
> http://p.sf.net/sfu/intel-dev2devmay
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

Re: [cx-oracle-users] maintaining multiple connections with different encodings

[Doug H. <djh...@te...>]

On 2011-05-09 13:28, Dana Pieluszczak wrote:
> <snip>
> The table below is defined as (NLS_LENGTH_SEMANTICS IS 'BYTE' for both
> databases):
>
> CREATE TABLE test (str VARCHAR2(30 BYTE));
>
> <snip>
What are the values of NLS_CHARACTERSET and NLS_NCHAR_CHARACTERSET from
the sys.nls_database_parameters view? Mine are AL32UTF8 and AL16UTF16
respectively.

It may be illuminating to use this query:

select str, dump(str) from test;

to see what is actually stored in the table. This is what I get:
dätä    Typ=1 Len=6: 100,195,164,116,195,164

-- 
Doug Henderson, Calgary, Alberta, Canada

Re: [cx-oracle-users] maintaining multiple connections with different encodings

[Dana P. <da...@gm...>]

> Second, why do you want to use different encodings? Oracle will
> automatically translate between whatever encoding is used in the
> database and whatever encoding you are using on the client anyway. So
> you can feel quite free to use utf-8 for both databases and everything
> will work just fine since utf-8 is capable of displaying all of the
> characters in the latin-1 character set.

Ok, this is ideal, so I tried using utf-8 for both connections. This
works for my trivial example. That is, binding a variable and
selecting it back out. But not if I actually insert data into a table
and select it back out.

The table below is defined as (NLS_LENGTH_SEMANTICS IS 'BYTE' for both
databases):

CREATE TABLE test (str VARCHAR2(30 BYTE));


databases = ('utf8db', 'latin1db')
username = 'dana'
password = 'whatever'

os.environ['NLS_LANG'] = 'AMERICAN_AMERICA.UTF8'
for tns in databases:
   conn = cx_Oracle.connect(username, password, tns)
   print "connection encoding: %s" % conn.encoding

   conn.cursor().execute('INSERT INTO test (str) VALUES (:s)',
s=u'd\u00e4t\u00e4')
   s = conn.cursor().execute('SELECT str FROM
test').fetchone()[0].decode('utf8')
   print s, type(s), len(s), len(s.encode('utf8'))

   conn.close()

Which outputs:

connection encoding: UTF-8
dåta <type 'unicode'> 4 5
connection encoding: UTF-8
d¿ta <type 'unicode'> 4 5

Perhaps I'm missing something?


On Mon, May 9, 2011 at 12:14 PM, Anthony Tuininga
<ant...@gm...> wrote:
> Hi,
>
> First, Oracle does not allow redefining the default encoding with
> multiple connections. So the behavior you are seeing is expected.
>
> Second, why do you want to use different encodings? Oracle will
> automatically translate between whatever encoding is used in the
> database and whatever encoding you are using on the client anyway. So
> you can feel quite free to use utf-8 for both databases and everything
> will work just fine since utf-8 is capable of displaying all of the
> characters in the latin-1 character set.
>
> Hope that helps.
>
> Anthony
>
> On Mon, May 9, 2011 at 7:00 AM, Dana Pieluszczak <da...@gm...> wrote:
>> I have a python process which needs to connect to multiple oracle
>> databases with different NLS_CHARACTERSET values.
>>
>> I can set the appropriate character set by setting the NLS_LANG
>> environment variable, either in my shell or using os.environ before
>> connecting. For utf-8 databases, I set it to "AMERICAN_AMERICA.UTF8",
>> for Latin-1 (ISO-8859-1) I set it to "AMERICAN_AMERICA.WE8ISO8859P1".
>>
>> Here's some code that illustrates the problem:
>>
>> import os
>> import cx_Oracle
>>
>> os.environ['NLS_LANG'] = 'AMERICAN_AMERICA.UTF8'
>> utf8_conn = cx_Oracle.connect('dana', 'pw', 'utf8db')
>> print utf8_conn.encoding
>> s = utf8_conn.cursor().execute('SELECT :s AS str FROM dual',
>> s=u'd\u00e4t\u00e4').fetchone()[0]
>> print s, type(s), len(s), len(s.encode('utf8'))
>>
>> utf8_conn.close()
>>
>> os.environ['NLS_LANG'] ='AMERICAN_AMERICA.WE8ISO8859P1'
>> latin1_conn = cx_Oracle.connect('dana', 'pw', 'latin1db')
>> print latin1_conn.encoding
>> s = latin1_conn.cursor().execute('SELECT :s AS str FROM dual',
>> s='d\xe4t\xe4').fetchone()[0].decode('latin1')
>> print s, type(s), len(s), len(s.encode('latin1'))
>>
>> This outputs:
>> UTF-8
>> dätä <type 'unicode'> 4 6
>> ISO-8859-1
>> dätä <type 'unicode'> 4 4
>>
>> The problem is, if I don't close the utf8 connection before opening
>> the latin1 connection, the latin1 connection's encoding will be utf8.
>> Here's the output with the utf8_conn.close() call removed:
>>
>> UTF-8
>> dätä <type 'unicode'> 4 6
>> UTF-8
>> d¿ <type 'unicode'> 3 3
>>
>> Is there any way around this? I'd like to be able to open all my
>> database connections at startup time instead of constantly
>> opening/closing them.
>>
>> I'm using cx_Oracle 5.1 with oracle client version 10.2.0.1 on SLES
>> 10. Both databases that I'm connecting to are 10.2.0.5.
>>
>> Thanks,
>>
>> Dana P
>>
>> ------------------------------------------------------------------------------
>> WhatsUp Gold - Download Free Network Management Software
>> The most intuitive, comprehensive, and cost-effective network
>> management toolset available today.  Delivers lowest initial
>> acquisition cost and overall TCO of any competing solution.
>> http://p.sf.net/sfu/whatsupgold-sd
>> _______________________________________________
>> cx-oracle-users mailing list
>> cx-...@li...
>> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>>
>
> ------------------------------------------------------------------------------
> WhatsUp Gold - Download Free Network Management Software
> The most intuitive, comprehensive, and cost-effective network
> management toolset available today.  Delivers lowest initial
> acquisition cost and overall TCO of any competing solution.
> http://p.sf.net/sfu/whatsupgold-sd
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

Re: [cx-oracle-users] maintaining multiple connections with different encodings

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

Hi,

First, Oracle does not allow redefining the default encoding with
multiple connections. So the behavior you are seeing is expected.

Second, why do you want to use different encodings? Oracle will
automatically translate between whatever encoding is used in the
database and whatever encoding you are using on the client anyway. So
you can feel quite free to use utf-8 for both databases and everything
will work just fine since utf-8 is capable of displaying all of the
characters in the latin-1 character set.

Hope that helps.

Anthony

On Mon, May 9, 2011 at 7:00 AM, Dana Pieluszczak <da...@gm...> wrote:
> I have a python process which needs to connect to multiple oracle
> databases with different NLS_CHARACTERSET values.
>
> I can set the appropriate character set by setting the NLS_LANG
> environment variable, either in my shell or using os.environ before
> connecting. For utf-8 databases, I set it to "AMERICAN_AMERICA.UTF8",
> for Latin-1 (ISO-8859-1) I set it to "AMERICAN_AMERICA.WE8ISO8859P1".
>
> Here's some code that illustrates the problem:
>
> import os
> import cx_Oracle
>
> os.environ['NLS_LANG'] = 'AMERICAN_AMERICA.UTF8'
> utf8_conn = cx_Oracle.connect('dana', 'pw', 'utf8db')
> print utf8_conn.encoding
> s = utf8_conn.cursor().execute('SELECT :s AS str FROM dual',
> s=u'd\u00e4t\u00e4').fetchone()[0]
> print s, type(s), len(s), len(s.encode('utf8'))
>
> utf8_conn.close()
>
> os.environ['NLS_LANG'] ='AMERICAN_AMERICA.WE8ISO8859P1'
> latin1_conn = cx_Oracle.connect('dana', 'pw', 'latin1db')
> print latin1_conn.encoding
> s = latin1_conn.cursor().execute('SELECT :s AS str FROM dual',
> s='d\xe4t\xe4').fetchone()[0].decode('latin1')
> print s, type(s), len(s), len(s.encode('latin1'))
>
> This outputs:
> UTF-8
> dätä <type 'unicode'> 4 6
> ISO-8859-1
> dätä <type 'unicode'> 4 4
>
> The problem is, if I don't close the utf8 connection before opening
> the latin1 connection, the latin1 connection's encoding will be utf8.
> Here's the output with the utf8_conn.close() call removed:
>
> UTF-8
> dätä <type 'unicode'> 4 6
> UTF-8
> d¿ <type 'unicode'> 3 3
>
> Is there any way around this? I'd like to be able to open all my
> database connections at startup time instead of constantly
> opening/closing them.
>
> I'm using cx_Oracle 5.1 with oracle client version 10.2.0.1 on SLES
> 10. Both databases that I'm connecting to are 10.2.0.5.
>
> Thanks,
>
> Dana P
>
> ------------------------------------------------------------------------------
> WhatsUp Gold - Download Free Network Management Software
> The most intuitive, comprehensive, and cost-effective network
> management toolset available today.  Delivers lowest initial
> acquisition cost and overall TCO of any competing solution.
> http://p.sf.net/sfu/whatsupgold-sd
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

[cx-oracle-users] maintaining multiple connections with different encodings

[Dana P. <da...@gm...>]

I have a python process which needs to connect to multiple oracle
databases with different NLS_CHARACTERSET values.

I can set the appropriate character set by setting the NLS_LANG
environment variable, either in my shell or using os.environ before
connecting. For utf-8 databases, I set it to "AMERICAN_AMERICA.UTF8",
for Latin-1 (ISO-8859-1) I set it to "AMERICAN_AMERICA.WE8ISO8859P1".

Here's some code that illustrates the problem:

import os
import cx_Oracle

os.environ['NLS_LANG'] = 'AMERICAN_AMERICA.UTF8'
utf8_conn = cx_Oracle.connect('dana', 'pw', 'utf8db')
print utf8_conn.encoding
s = utf8_conn.cursor().execute('SELECT :s AS str FROM dual',
s=u'd\u00e4t\u00e4').fetchone()[0]
print s, type(s), len(s), len(s.encode('utf8'))

utf8_conn.close()

os.environ['NLS_LANG'] ='AMERICAN_AMERICA.WE8ISO8859P1'
latin1_conn = cx_Oracle.connect('dana', 'pw', 'latin1db')
print latin1_conn.encoding
s = latin1_conn.cursor().execute('SELECT :s AS str FROM dual',
s='d\xe4t\xe4').fetchone()[0].decode('latin1')
print s, type(s), len(s), len(s.encode('latin1'))

This outputs:
UTF-8
dätä <type 'unicode'> 4 6
ISO-8859-1
dätä <type 'unicode'> 4 4

The problem is, if I don't close the utf8 connection before opening
the latin1 connection, the latin1 connection's encoding will be utf8.
Here's the output with the utf8_conn.close() call removed:

UTF-8
dätä <type 'unicode'> 4 6
UTF-8
d¿ <type 'unicode'> 3 3

Is there any way around this? I'd like to be able to open all my
database connections at startup time instead of constantly
opening/closing them.

I'm using cx_Oracle 5.1 with oracle client version 10.2.0.1 on SLES
10. Both databases that I'm connecting to are 10.2.0.5.

Thanks,

Dana P

Re: [cx-oracle-users] Full version info of cx_Oracle installed

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

On Thu, Apr 28, 2011 at 9:47 PM, Robert <web...@gm...> wrote:
> Oracle Server: 11g
> Client@Win7 : Instant Client 10g
> Python: v  2.7
>
>>>> cx_Oracle.clientversion()
> (10, 2, 0, 5, 0)
>>>> cx_Oracle.buildtime
> 'Mar 19 2011 23:12:21'
>>>> cx_Oracle.apilevel
> '2.0'
>
> Got this error:
>
>>>> db = cx_Oracle.connect(OraUid + "/" + OraPwd + "@" + OraService)
> Traceback (most recent call last):
>  File "<stdin>", line 1, in <module>
> cx_Oracle.DatabaseError: ORA-24315: illegal attribute type

This is because you are using cx_Oracle compiled against 11g but
actually using cx_Oracle against 10g. You can't do that!

> I think it is because I installed the 11g version of cx_Oracle
> (cx_Oracle-5.1-11g.win32-py2.7.msi)

Yes.

> so 2 questions:
> 1) How to verify the exact version/edition of cx_Oracle installed ?

cx_Oracle.version

That will tell you the version of cx_Oracle. It might be useful to
also include whether it is 10g, 11g, etc. as well.

> 2)  Can I just install this : cx_Oracle-5.1-10g.win32-py2.7.msi  or
> should I uninstall the existing version ?

Uninstall the original version first. The MSI packages are independent
of each other.

Anthony

[cx-oracle-users] Full version info of cx_Oracle installed

[Robert <web...@gm...>]

Oracle Server: 11g
Client@Win7 : Instant Client 10g
Python: v  2.7

>>> cx_Oracle.clientversion()
(10, 2, 0, 5, 0)
>>> cx_Oracle.buildtime
'Mar 19 2011 23:12:21'
>>> cx_Oracle.apilevel
'2.0'

Got this error:

>>> db = cx_Oracle.connect(OraUid + "/" + OraPwd + "@" + OraService)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
cx_Oracle.DatabaseError: ORA-24315: illegal attribute type


I think it is because I installed the 11g version of cx_Oracle
(cx_Oracle-5.1-11g.win32-py2.7.msi)

so 2 questions:
1) How to verify the exact version/edition of cx_Oracle installed ?
2)  Can I just install this : cx_Oracle-5.1-10g.win32-py2.7.msi  or
should I uninstall the existing version ?

Thanks
robert

[cx-oracle-users] HP-UX build options

[Dmitry P. <mcy...@gm...>]

Please add the next lines into the setup.py:
'''
....
elif sys.platform == "hp-ux11":
    extraLinkArgs.append("-lttsh"+oracleVersion[:-1])
''

-- 
Regards,

Dmytro

Re: [cx-oracle-users] cx_Oracle 5.1 on OSX 10.6.7 with Instantclient 10.2 64bit

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

On 4/25/11 12:49 PM, Chris Gould wrote:
>     Hi Chris -
>     I don't think cx_Oracle needs ORACLE_HOME set to run (just checked it and it runs fine without it set - only runtime requirement is  that instantclient directory is in DYLD_LIBRARY_PATH).  It only requires ORACLE_HOME to be set when building cx_Oracle from source code.

That doesn't have to be the case.  If you install the instantclient libs and headers
into /lib and /include you shouldn't need to set any environment variables, including
DYLD_LIBRARY_PATH.

My recipes for setting things up this way are here... if there's anything that doesn't work,
let me know and I'll update.

http://stackoverflow.com/questions/684352/installing-oracle-instantclient-on-mac-os-x-without-setting-environment-variables
http://stackoverflow.com/questions/764871/installing-oracle-instantclient-on-linux-without-setting-environment-variables

Re: [cx-oracle-users] cx_Oracle 5.1 on OSX 10.6.7 with Instantclient 10.2 64bit

[Chris G. <chr...@to...>]

Hi Chris -
I don't think cx_Oracle needs ORACLE_HOME set to run (just checked it and it
runs fine without it set - only runtime requirement is  that instantclient
directory is in DYLD_LIBRARY_PATH).  It only requires ORACLE_HOME to be set
when building cx_Oracle from source code.

Chris.


On 25 April 2011 20:39, Christopher Jones <chr...@or...>wrote:

>
> Ideally installation of cx_Oracle would not overload the use of the
> ORACLE_HOME variable.  We try and promote that Instant Client doesn't
> need ORACLE_HOME set to run :)
>
> Chris
>
> On 04/22/2011 01:50 PM, Anthony Tuininga wrote:
> > Hi,
> >
> > The line numbers you provide in your description don't match the ones
> > that I have in my 5.1 installation....but I was able to find out where
> > you were referring to, I believe. And I don't believe that your
> > changes are necessary. You'll note on line 167 this line
> >
> > libDirs = [libPath, oracleHome]
> >
> > so you can see that the library path includes whatever ORACLE_HOME is.
> > I've just checked in changes to further simplify things as earlier in
> > the code in the CheckOracleHome() method the location of the clntsh
> > library is determined.
> >
> > In any case, with the base 5.1 install and the new changes I just
> > checked in I was able to successfully build cx_Oracle on my Mac Mini.
> > So I'm not sure why you needed to make the changes you did. Can you
> > try the vanilla one again and show me your error? Thanks.
> >
> > Anthony
> >
> > On Thu, Apr 21, 2011 at 3:24 PM, Chris Gould<chr...@to...>
>  wrote:
> >> I've fixed the problem with cx_Oracle (as I described).  I vaguely
> remember
> >> having a similar problem when I installed cx_Oracle 5.0.3 with an
> earlier
> >> version of python (2.6).  The solution was the same this time.
> >> Because I'm using Instantclient and not a full Oracle installation, I'd
> set
> >> ORACLE_HOME env var to my instantclient directory.
> >> I also set DYLD_LIBRARY_PATH to point to the InstantClient directory
>  (This
> >> needs to be made a permanent setting in .bash_profile )
> >> I created the extra symlink required in the InstantClient directory :
> >> ln -s libclntsh.dylib.10.1  libclntsh.dylib
> >> I also made 2 small changes to setup.py which came with cx_Oracle5.1.
> >>   on line 73, change
> >>      elif sys.platform == "darwin":
> >>          subDirs = ["lib"]
> >> to
> >>      elif sys.platform == "darwin":
> >>          subDirs = [""]
> >> and on line 155 change
> >>      libPath = os.path.join(oracleHome, "lib")
> >> to
> >>      libPath = os.path.join(oracleHome, "")
> >> Then follow the stated instructions for building and installing
> cx_Oracle
> >> The reason these changes are necessary is that if you've set ORACLE_HOME
> to
> >> your instantclient directory, the files required are in $ORACLE_HOME not
> >> $ORACLE_HOME/lib as they would be with a full Oracle installation (which
> is
> >> what setup.py is assuming).
> >> It'd be handy if the distributed setup.py could be modified to cater for
> the
> >> InstantClient setup, since I guess more people these days will use this
> than
> >> an old-skool OracleHome installation.
> >> Chris.
> >>
> >> On 16 April 2011 01:18, Mark Harrison<mh...@pi...>  wrote:
> >>>
> >>> On 4/15/11 4:02 PM, Chris Gould wrote:
> >>>> Hi -
> >>>> I've downloaded and installed Python 2.7.1 for OSX (Intel 64 bit)
> which
> >>>> is running fine.  I've also downloaded the source code for cx_Oracle
> and
> >>>> tried to get it working.
> >>>> I've followed the instructions (python setup.py build  , python
> setup.py
> >>>> install)  and this seems to work OK.  But when I come to try importing
> >>>> cx_Oracle I'm repeatedly getting the following error :
> >>>>
> >>>>
> >>>>   >>>  import cx_Oracle
> >>>> Traceback (most recent call last):
> >>>>     File "<stdin>", line 1, in<module>
> >>>> ImportError:
> >>>>
> dlopen(/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/cx_Oracle.so,
> >>>> 2): Symbol not found: _OCIAttrGet
> >>>>     Referenced from:
> >>>>
> /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/cx_Oracle.so
> >>>>     Expected in: flat namespace
> >>>>    in
> >>>>
> /Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/cx_Oracle.so
> >>>>
> >>>> I have set up the symlink in /usr/local/instantclient_10_2_64
> >>>>
> >>>> lrwxr-xr-x  1 root   staff        20 15 Apr 23:55 libclntsh.dylib ->
> >>>> libclntsh.dylib.10.1
> >>>> -rwxr-xr-x@ 1 chris  staff  25582048 31 Mar  2009
> libclntsh.dylib.10.1
> >>>>
> >>>>
> >>>> I've got the following environment variables set (which some googling
> >>>> seems to imply might have been responsible for the problem)
> >>>>
> >>>> DYLD_LIBRARY_PATH=/usr/local/instantclient_10_2_64
> >>>> LD_LIBRARY_PATH=/usr/local/instantclient_10_2_64:
> >>>> ORACLE_HOME=/usr/local/instantclient_10_2_64
> >>>>
> >>>>
> >>>> The instantclient libraries came from Oracle's website : I've got the
> >>>> basic Instantclient 10.2.0.4 for 64bit OSX and also the SDK (though I
> don't
> >>>> know if that's strictly necessary).
> >>>>
> >>>> Anyone got any ideas what's going wrong?
> >>>
> >>> In my experience, nothing but grief comes from setting environment
> >>> variables
> >>> to get oracle running.
> >>>
> >>> Here's what worked for me:
> >>>
> >>>
> >>>
> http://stackoverflow.com/questions/684352/installing-oracle-instantclient-on-mac-os-x-without-setting-environment-variables
> >>>
> >>> HTH!
> >>> Mark
> >>>
> >>>
> >>>
> ------------------------------------------------------------------------------
> >>> Benefiting from Server Virtualization: Beyond Initial Workload
> >>> Consolidation -- Increasing the use of server virtualization is a top
> >>> priority.Virtualization can reduce costs, simplify management, and
> improve
> >>> application availability and disaster protection. Learn more about
> >>> boosting
> >>> the value of server virtualization.
> http://p.sf.net/sfu/vmware-sfdev2dev
> >>> _______________________________________________
> >>> cx-oracle-users mailing list
> >>> cx-...@li...
> >>> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> >>
> >>
> >>
> ------------------------------------------------------------------------------
> >> Fulfilling the Lean Software Promise
> >> Lean software platforms are now widely adopted and the benefits have
> been
> >> demonstrated beyond question. Learn why your peers are replacing JEE
> >> containers with lightweight application servers - and what you can gain
> >> from the move. http://p.sf.net/sfu/vmware-sfemails
> >> _______________________________________________
> >> cx-oracle-users mailing list
> >> cx-...@li...
> >> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> >>
> >>
> >
> >
> ------------------------------------------------------------------------------
> > Fulfilling the Lean Software Promise
> > Lean software platforms are now widely adopted and the benefits have been
> > demonstrated beyond question. Learn why your peers are replacing JEE
> > containers with lightweight application servers - and what you can gain
> > from the move. http://p.sf.net/sfu/vmware-sfemails
> > _______________________________________________
> > cx-oracle-users mailing list
> > cx-...@li...
> > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
> --
> Email: chr...@or...
> Tel:  +1 650 506 8630
> Blog:  http://blogs.oracle.com/opal/
>
>
> ------------------------------------------------------------------------------
> WhatsUp Gold - Download Free Network Management Software
> The most intuitive, comprehensive, and cost-effective network
> management toolset available today.  Delivers lowest initial
> acquisition cost and overall TCO of any competing solution.
> http://p.sf.net/sfu/whatsupgold-sd
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>