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

Re: [cx-oracle-users] Oracle Error :: ORA-01484 arrays can only be bound to PL/SQL statements

[Wong W. Meng-R. <r3...@fr...>]

Yes worst case is we will have to modify this SQL statement.

However, is it a long standing issue with Oracle or cx_Oracle? This is an old code and the passing of parameters in such way has been working fine on oracledb. So is this something the cx_Oracle does not support? IF it is oracle issue I wonder whey it did not give the error when we use oracledb.

Regards,
Wah Meng
________________________________
From: Amaury Forgeot d'Arc [mailto:ama...@gm...]
Sent: Wednesday, November 30, 2011 9:06 PM
To: cx-...@li...
Subject: Re: [cx-oracle-users] Oracle Error :: ORA-01484 arrays can only be bound to PL/SQL statements

2011/11/30 Wong Wah Meng-R32813 <r3...@fr...<mailto:r3...@fr...>>
>> cmd = "select ........ from FUTURE_HOLD_CONDITIONS where STEP_NAME not in (:1) and LOT_ID = :2 and ......."

This cannot work with Oracle. A placeholder can only replace one value.
(Think of the IN operator as a sequence of OR conditions)
Yes, this is a long standing issue with Oracle.
I suggest to try with %s formatting.

--
Amaury Forgeot d'Arc

Re: [cx-oracle-users] Oracle Error :: ORA-01484 arrays can only be bound to PL/SQL statements

[Wong W. Meng-R. <r3...@fr...>]

Yes I thought of this. I will convert the empty list [] as string and store it into the column. :)

Regards,
Wah Meng
________________________________
From: Amaury Forgeot d'Arc [mailto:ama...@gm...]
Sent: Wednesday, November 30, 2011 9:08 PM
To: cx-...@li...
Subject: Re: [cx-oracle-users] Oracle Error :: ORA-01484 arrays can only be bound to PL/SQL statements

2011/11/30 Wong Wah Meng-R32813 <r3...@fr...<mailto:r3...@fr...>>
SQL> Select attr_value from mes_attribute_histories where entity_type='ALOT' and attr_name='wipRack';

........
........
........
.......
KTMTEST:KT06
[]
[]
KTMPQCIL:KPQ03
[]
KTMQA:KQ01
[]
[]
[]


So, use str() and your list will be converted into a string :-)

--
Amaury Forgeot d'Arc

Re: [cx-oracle-users] Oracle Error :: ORA-01484 arrays can only be bound to PL/SQL statements

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

2011/11/30 Wong Wah Meng-R32813 <r3...@fr...>

> SQL> Select attr_value from mes_attribute_histories where
> entity_type='ALOT' and attr_name='wipRack’;****
>
> ** **
>
> ……..****
>
> ……..****
>
> ……..****
>
> …….****
>
> KTMTEST:KT06****
>
> []****
>
> []****
>
> KTMPQCIL:KPQ03****
>
> []****
>
> KTMQA:KQ01****
>
> []****
>
> []****
>
> []****
>
> **
>

So, use str() and your list will be converted into a string :-)

-- 
Amaury Forgeot d'Arc

Re: [cx-oracle-users] Oracle Error :: ORA-01484 arrays can only be bound to PL/SQL statements

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

2011/11/30 Wong Wah Meng-R32813 <r3...@fr...>

> >> cmd = “select …….. from FUTURE_HOLD_CONDITIONS where STEP_NAME not in
> (:1) and LOT_ID = :2 and …….”


This cannot work with Oracle. A placeholder can only replace one value.
(Think of the IN operator as a sequence of OR conditions)
Yes, this is a long standing issue with Oracle.
I suggest to try with %s formatting.

-- 
Amaury Forgeot d'Arc

Re: [cx-oracle-users] Oracle Error :: ORA-01484 arrays can only be bound to PL/SQL statements

[Wong W. Meng-R. <r3...@fr...>]

My application also has another usage of passing list items as parameters though the column STEP_NAME is not meant to store data as list item.

>> cmd = "select ........ from FUTURE_HOLD_CONDITIONS where STEP_NAME not in (:1) and LOT_ID = :2 and ......."
>> arg = (['HIGH TEST-DTS'], 'XXXXX',.....)
>> execute(cmd, arg)
>> "<type 'exceptions.ValueError'>", 'ORA-01484: arrays can only be bound to PL/SQL statements\n')


Regards,
Wah Meng
________________________________
From: Wong Wah Meng-R32813
Sent: Wednesday, November 30, 2011 8:27 PM
To: 'cx-...@li...'
Subject: RE: [cx-oracle-users] Oracle Error :: ORA-01484 arrays can only be bound to PL/SQL statements

The field is a VARCHAR2 column.

SQL> desc MES_ATTRIBUTE_HISTORIES;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ENTITY_TYPE                                        VARCHAR2(4)
 ENTITY_ID                                          VARCHAR2(100)
 ATTR_NAME                                          VARCHAR2(32)
 SET_TIME                                           DATE
 SET_USER_ID                                        VARCHAR2(32)
 ATTR_VALUE                                         VARCHAR2(2000)
 COMMENTS                                           VARCHAR2(255)
 RCDE_ID                                            VARCHAR2(8)

The data we store can be either an empty list, or some values.

SQL> Select attr_value from mes_attribute_histories where entity_type='ALOT' and attr_name='wipRack';

........
........
........
.......
KTMTEST:KT06
[]
[]
KTMPQCIL:KPQ03
[]
KTMQA:KQ01
[]
[]
[]

[]
KTMTEST:KT06
[]
[]
KTMPQCIL:KPQ03
[]
KTMQA:KQ02
[]
KTMQA:KQ01
[]
[]
[]
[]
[]
TSGTEST:359
[]
[]
[]
[]
[]
[]
^C

[]

8885 rows selected.


Regards,
Wah Meng
________________________________
From: Amaury Forgeot d'Arc [mailto:ama...@gm...]
Sent: Wednesday, November 30, 2011 7:29 PM
To: cx-...@li...
Subject: Re: [cx-oracle-users] Oracle Error :: ORA-01484 arrays can only be bound to PL/SQL statements

2011/11/30 Wong Wah Meng-R32813 <r3...@fr...<mailto:r3...@fr...>>
By design of the applicaiton, the ATTR_VALUE column can store value in python list format.

But how is it stored in the database?
"python list format" is not close to any valid SQL column type.

--
Amaury Forgeot d'Arc

Re: [cx-oracle-users] Oracle Error :: ORA-01484 arrays can only be bound to PL/SQL statements

[Wong W. Meng-R. <r3...@fr...>]

The field is a VARCHAR2 column.

SQL> desc MES_ATTRIBUTE_HISTORIES;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ENTITY_TYPE                                        VARCHAR2(4)
 ENTITY_ID                                          VARCHAR2(100)
 ATTR_NAME                                          VARCHAR2(32)
 SET_TIME                                           DATE
 SET_USER_ID                                        VARCHAR2(32)
 ATTR_VALUE                                         VARCHAR2(2000)
 COMMENTS                                           VARCHAR2(255)
 RCDE_ID                                            VARCHAR2(8)

The data we store can be either an empty list, or some values.

SQL> Select attr_value from mes_attribute_histories where entity_type='ALOT' and attr_name='wipRack';

........
........
........
.......
KTMTEST:KT06
[]
[]
KTMPQCIL:KPQ03
[]
KTMQA:KQ01
[]
[]
[]

[]
KTMTEST:KT06
[]
[]
KTMPQCIL:KPQ03
[]
KTMQA:KQ02
[]
KTMQA:KQ01
[]
[]
[]
[]
[]
TSGTEST:359
[]
[]
[]
[]
[]
[]
^C

[]

8885 rows selected.


Regards,
Wah Meng
________________________________
From: Amaury Forgeot d'Arc [mailto:ama...@gm...]
Sent: Wednesday, November 30, 2011 7:29 PM
To: cx-...@li...
Subject: Re: [cx-oracle-users] Oracle Error :: ORA-01484 arrays can only be bound to PL/SQL statements

2011/11/30 Wong Wah Meng-R32813 <r3...@fr...<mailto:r3...@fr...>>
By design of the applicaiton, the ATTR_VALUE column can store value in python list format.

But how is it stored in the database?
"python list format" is not close to any valid SQL column type.

--
Amaury Forgeot d'Arc

Re: [cx-oracle-users] Oracle Error :: ORA-01484 arrays can only be bound to PL/SQL statements

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

2011/11/30 Wong Wah Meng-R32813 <r3...@fr...>

> By design of the applicaiton, the ATTR_VALUE column can store value in
> python list format.


But how is it stored in the database?
"python list format" is not close to any valid SQL column type.

-- 
Amaury Forgeot d'Arc

[cx-oracle-users] Oracle Error :: ORA-01484 arrays can only be bound to PL/SQL statements

[Wong W. Meng-R. <r3...@fr...>]

Hello there,

I am continuing on my python 1.5.2 to 2.7.1, oracledb to cx_Oracle conversion project.

My application server is coded with following SQL statements and arguments in one of the API. My NLS_LANG setting caused the error description not displayable in English but the description has been stated in the subject of this email.

By design of the applicaiton, the ATTR_VALUE column can store value in python list format. I will encounter the same error no matter the value is an empty list or a list with elements. Is there a workaround to this in cx_Oracle, like overriding the inputtypehandler for the cursor object? The code used to be working fine in oracledb.

Regards,
Wah Meng

>> cmd="insert into MES_ATTRIBUTE_HISTORIES (ENTITY_TYPE, ENTITY_ID, ATTR_NAME, SET_TIME, SET_USER_ID, ATTR_VALUE, COMMENTS, RCDE_ID) values ('ALOT', :1, :2, :3, :4, :5, :6, :7)"
>> arg=('TJMEA12KAH00', 'wipRack', (2011, 11, 25, 9, 55, 30), 'R33088', [], None, None)
>>> a.execute(cmd, arg)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/home/r32813/genesis/GEN_DEV_271/Product/Lib/EComponent.py", line 896, in __call__
    raiseAppExc(sys.exc_info()[0].__name__, sys.exc_info()[1].__str__())
  File "/home/r32813/genesis/GEN_DEV_271/Product/Lib/EComponent.py", line 191, in raiseAppExc
    raise excClass(value)
EComponent.DatabaseError: ORA-01484: ?????? PL/SQL ????


Regards,
Wah Meng

[cx-oracle-users] Copying tables between two schemas? SQ*PLUS (Mark Harrison)

[Robert L. <rlu...@pi...>]

>windows, you can use "sqlplus @foo"

You can also do the above in unix

It would be better to separate your sql*plus commands from the invocation of
SQL*PLus. Recommend that you always use sqlplus @foo for all production
code.

>sqlplus -S -L scott/tiger@myorcl

Embedding user name and password in a script file is dangerous. Recommend
that put them in a separate file and read them in. This way can change them
w/out changing your scripts.

-- Below is original email

Here's how to automate sqlplus in a shell script.  I'm not familiar with
the copy command, so it just uses a simple select command.  If you need to
do this in windows, you can use "sqlplus @foo" and put the commands in
the file foo.sql


$ cat auto-sqlplus
#!/bin/sh

sqlplus -S -L scott/tiger@myorcl <<.
select 2+2 from dual;
.

$ chmod +x auto-sqlplus
$ ./auto-sqlplus

        2+2
----------
	 4

Re: [cx-oracle-users] cx_Oracle installation problem / python

[Paul M. <p.f...@gm...>]

On 28 November 2011 18:52, Humberto Ferreira da Luz Junior
<hum...@gm...> wrote:
> The prefix is used by the python framework web2py, I tried removing it, but
> I got the same output.
> The SQL Plus isn't installed in my OS, but I can access this conection
> through SQL Developer with SID = "dese" and I can connect without problems.
> Could this be a python issue?

If you don't have SQLPlus, it sounds like you don't have an Oracle
client installed. SQL Developer is Java-based and uses JDBC, which
does not need a full Oracle client. For Python, you need either an
Oracle client install, or Oracle Instant Client. Both of these have
sqlplus (OK, a minimal instant client setup might not, but it can be
added), so you should first check that you can connect via sqlplus -
if that works, Python should also.

Try the command sqlplus user/password@tnsname (or sqlplus
user/password@//host:port/service if you haven't got tnsnames set up).

If these don't work, get them sorted (this is a purely Oracle issue,
get your local DBA to help if you need to). Once this works, whatever
you used in the sqlplus command should also work in Python.

Paul.

Re: [cx-oracle-users] cx_Oracle installation problem / python

[Humberto F. da L. J. <hum...@gm...>]

Yes, but I got the same error.

Humberto Ferreira da Luz Junior
Mestrado - Ciência da Computação / UEL
Analista de Informática - ATI / UEL

Re: [cx-oracle-users] cx_Oracle installation problem / python

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

On 11/28/11 10:19 AM, Humberto Ferreira da Luz Junior wrote:
> I think I'll need your help once more.
> I installed oracle_client and cx_oracle.
>
> When I type "import cx_Oracle" in python shell everything works ok. But when I try to connect to the database I get the following error:
>
>  >>> conn = cx_Oracle.connect('oracle://user/password@ip:port/dese')
>
> Traceback (most recent call last):
>    File "<pyshell#2>", line 1, in <module>
>      conn = cx_Oracle.connect('oracle://user/password@ip:port/dese')
> *DatabaseError: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor*
>
> By what I understood I'd need to create a tnsnames.ora file in ORACLE_HOME/network/admin/ (oracle client path). But after doing this I keep getting the same error.
>
> Does somebody know where or how I should configure the connection?

have you tried one of these forms:

     name/pass@tnsname

     name/pass@//host:1521/instance

Re: [cx-oracle-users] cx_Oracle installation problem / python

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

2011/11/28 Humberto Ferreira da Luz Junior <hum...@gm...>

> The prefix is used by the python framework web2py, I tried removing it,
> but I got the same output.
> The SQL Plus isn't installed in my OS, but I can access this conection
> through SQL Developer with SID = "dese" and I can connect without problems.
>

SID="dese" probably refers to an entry in the file tnsnames.ora.
You should copy this file from the Oracle client used by SQL Developer to
the one used by cx_Oracle.
Then don't specify the server name:  user/password@dese


-- 
Amaury Forgeot d'Arc

Re: [cx-oracle-users] cx_Oracle installation problem / python

[Humberto F. da L. J. <hum...@gm...>]

The prefix is used by the python framework web2py, I tried removing it, but
I got the same output.
The SQL Plus isn't installed in my OS, but I can access this conection
through SQL Developer with SID = "dese" and I can connect without problems.
Could this be a python issue?

Humberto Ferreira da Luz Junior
Mestrado - Ciência da Computação / UEL
Analista de Informática - ATI / UEL

Re: [cx-oracle-users] cx_Oracle installation problem / python

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

2011/11/28 Humberto Ferreira da Luz Junior <hum...@gm...>

> >>> conn = cx_Oracle.connect('oracle://user/password@ip:port/dese')
>

I've never seen this "oracle://" prefix. Please try to remove it:
user/password@server:port/service


> Traceback (most recent call last):
>   File "<pyshell#2>", line 1, in <module>
>     conn = cx_Oracle.connect('oracle://user/password@ip:port/dese')
> *DatabaseError: ORA-12514: TNS:listener does not currently know of
> service requested in connect descriptor*
>

If the error did not disappear, it means that "dese" is not a service name
known by your server.
Can you connect with sqlplus?

-- 
Amaury Forgeot d'Arc

Re: [cx-oracle-users] cx_Oracle installation problem / python

[Humberto F. da L. J. <hum...@gm...>]

I think I'll need your help once more.
I installed oracle_client and cx_oracle.

When I type "import cx_Oracle" in python shell everything works ok. But
when I try to connect to the database I get the following error:

>>> conn = cx_Oracle.connect('oracle://user/password@ip:port/dese')

Traceback (most recent call last):
  File "<pyshell#2>", line 1, in <module>
    conn = cx_Oracle.connect('oracle://user/password@ip:port/dese')
*DatabaseError: ORA-12514: TNS:listener does not currently know of service
requested in connect descriptor*

By what I understood I'd need to create a tnsnames.ora file in
ORACLE_HOME/network/admin/ (oracle client path). But after doing this I
keep getting the same error.

Does somebody know where or how I should configure the connection?

Humberto Ferreira da Luz Junior
Mestrado - Ciência da Computação / UEL
Analista de Informática - ATI / UEL

Re: [cx-oracle-users] cx_Oracle installation problem / python

[Humberto F. da L. J. <hum...@gm...>]

I guess that's the problem then. I was using SQL Developer as my client. I
didn't know Oracle Client installation was required.
I'll installed it and set the environment variables and now it's everything
working fine.

Thanks. ^^

Humberto Ferreira da Luz Junior
Mestrado - Ciência da Computação / UEL
Analista de Informática - ATI / UEL

Re: [cx-oracle-users] cx_Oracle installation problem / python

[Marco De P. <dep...@gm...>]

what about oracle client?
is it win32 or amd64

remember you must have all the stack consistent: win32 or amd64

Marco

2011/11/28 Humberto Ferreira da Luz Junior <hum...@gm...>

> Hi, I need to access an oracle database with python.
> In order to install cx_oracle I followed thoses steps:
> 1. I installed python-2.7.2.amd64.msi.
> 2. I installed cx_Oracle-5.1.1-10g.win-amd64-
> py2.7.msi
> 3. I opened the IDEL (Python GUI) to test cx_oracle:
> >>> import cx_Oracle
>
> Traceback (most recent call last):
>   File "<pyshell#1>", line 1, in <module>
>     import cx_Oracle
> ImportError: DLL load failed: %1 não é um aplicativo Win32 válido. (%1 is
> not a valid Win32 app).
>
> I asked for help in the web2app google group but they said that this is
> probably an cx_oracle issue. Could somebody give me a tip to solve this
> problem?
>
> Obs. cx_Oracle.pyd is located in C:\Python27\Lib\site-packages.
>
> Thanks.
>
> Humberto Ferreira da Luz Junior
> Mestrado - Ciência da Computação / UEL
> Analista de Informática - ATI / UEL
>
>
>
> ------------------------------------------------------------------------------
> All the data continuously generated in your IT infrastructure
> contains a definitive record of customers, application performance,
> security threats, fraudulent activity, and more. Splunk takes this
> data and makes sense of it. IT sense. And common sense.
> http://p.sf.net/sfu/splunk-novd2d
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>

Re: [cx-oracle-users] cx_Oracle installation problem / python

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

2011/11/28 Humberto Ferreira da Luz Junior <hum...@gm...>

> Hi, I need to access an oracle database with python.
> In order to install cx_oracle I followed thoses steps:
> 1. I installed python-2.7.2.amd64.msi.
> 2. I installed cx_Oracle-5.1.1-10g.win-amd64-
> py2.7.msi
> 3. I opened the IDEL (Python GUI) to test cx_oracle:
> >>> import cx_Oracle
>
> Traceback (most recent call last):
>   File "<pyshell#1>", line 1, in <module>
>     import cx_Oracle
> ImportError: DLL load failed: %1 não é um aplicativo Win32 válido. (%1 is
> not a valid Win32 app).
>
> I asked for help in the web2app google group but they said that this is
> probably an cx_oracle issue. Could somebody give me a tip to solve this
> problem?
>
> Obs. cx_Oracle.pyd is located in C:\Python27\Lib\site-packages.
>

It's certainly an incompatibility with the Oracle client found in the PATH.
Are you sure to use a 64bit version of the Oracle client?
If you downloaded the Instant Client, it's probably a 32bit version,
which can only work in a 32bit program

-- 
Amaury Forgeot d'Arc

[cx-oracle-users] cx_Oracle installation problem / python

[Humberto F. da L. J. <hum...@gm...>]

Hi, I need to access an oracle database with python.
In order to install cx_oracle I followed thoses steps:
1. I installed python-2.7.2.amd64.msi.
2. I installed cx_Oracle-5.1.1-10g.win-amd64-
py2.7.msi
3. I opened the IDEL (Python GUI) to test cx_oracle:
>>> import cx_Oracle

Traceback (most recent call last):
  File "<pyshell#1>", line 1, in <module>
    import cx_Oracle
ImportError: DLL load failed: %1 não é um aplicativo Win32 válido. (%1 is
not a valid Win32 app).

I asked for help in the web2app google group but they said that this is
probably an cx_oracle issue. Could somebody give me a tip to solve this
problem?

Obs. cx_Oracle.pyd is located in C:\Python27\Lib\site-packages.

Thanks.

Humberto Ferreira da Luz Junior
Mestrado - Ciência da Computação / UEL
Analista de Informática - ATI / UEL

[cx-oracle-users] Using cx_Oracle in OpenBSD 5.0

[Juan P. D. P. <tcp...@gm...>]

Hi,

I just compiled and installed cx_Oracle 5.1.1 in OpenBSD 5.0 without
any problem,
but when I try to do the import an error rises (using python 2.7.1) :

>>> import cx_Oracle
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "build/bdist.openbsd-5.0-amd64/egg/cx_Oracle.py", line 7, in <module>
  File "build/bdist.openbsd-5.0-amd64/egg/cx_Oracle.py", line 6, in
__bootstrap__
ImportError: Cannot load specified object

I didn't find any documentation about this or if this is viable option
but decided to try it.

I just followed the Build.txt file, and everything compiled and
installed but the import
does not work.

Thanks for your help.

-- 
JP

Re: [cx-oracle-users] Copying tables between two schemas? SQ*PLUS

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

On 11/6/11 5:29 PM, Robert Lucente wrote:
> Recommend using the Oracle built in export/pump functionality. Also,
> depending on size of the data you might consider using Oracle transportable
> tablespaces.
>
> I guess trying to get you to consider the built in functionality present in
> Oracle and operating system. Why add complexity by adding Python and
> cx-oracle?

Here's how to automate sqlplus in a shell script.  I'm not familiar with
the copy command, so it just uses a simple select command.  If you need to
do this in windows, you can use "sqlplus @foo" and put the commands in
the file foo.sql


$ cat auto-sqlplus
#!/bin/sh

sqlplus -S -L scott/tiger@myorcl <<.
select 2+2 from dual;
.

$ chmod +x auto-sqlplus
$ ./auto-sqlplus

        2+2
----------
	 4



BTW, don't forget to trim list digests...



> Message: 4
> Date: Wed, 2 Nov 2011 14:22:27 +1100
> From: Victor Hooi<vic...@ya...>
> Subject: [cx-oracle-users] Copying tables between two schemas? SQ*PLUS
> 	COPY?
> To: cx-...@li...
> Message-ID:
> 	<CAM...@ma...>
> Content-Type: text/plain; charset="iso-8859-1"
>
> Hi,
>
> We have two separate schemas on different Oracle database instances, and we
> have a need to copy the records from certain tables from one to the other.
>
> Due to internal regulations, creating a database link between them isn't an
> option - so I suppose that rules our using INSERT INTO...SELECT, right?
>
> Previously, we were using SQL*PLUS's COPY extension:
>
> http://www.oracleutilities.com/SQLPLus/copy.html
>
> and this has worked really well.
>
> However, now we'd like to automate this, and so I figured cx_Oracle would
> be the way to go. However, I don't think I can use COPY from cx_Oracle
> since it's SQL*PLUS specific (
> http://stackoverflow.com/questions/7963441/using-sqlplus-copy-from-inside-cx
> -oracle
> ).
>
> Is there any way at all to get COPY to work from cx_Oracle? Or any other
> way to achieve the same thing just as elegantly, and not iterate through
> records?
>
> Cheers,
> Victor

[cx-oracle-users] Copying tables between two schemas? SQ*PLUS

[Robert L. <rlu...@pi...>]

Recommend using the Oracle built in export/pump functionality. Also,
depending on size of the data you might consider using Oracle transportable
tablespaces.

I guess trying to get you to consider the built in functionality present in
Oracle and operating system. Why add complexity by adding Python and
cx-oracle?

-----Original Message-----
From: cx-...@li...
[mailto:cx-...@li...]
Sent: Tuesday, November 01, 2011 10:23 PM
To: cx-...@li...
Subject: cx-oracle-users Digest, Vol 65, Issue 1


Send cx-oracle-users mailing list submissions to
	cx-...@li...

To subscribe or unsubscribe via the World Wide Web, visit
	https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
or, via email, send a message with subject or body 'help' to
	cx-...@li...

You can reach the person managing the list at
	cx-...@li...

When replying, please edit your Subject line so it is more specific
than "Re: Contents of cx-oracle-users digest..."


Today's Topics:

   1. Re: expecting None or a string (Anurag Chourasia)
   2. Re: expecting None or a string (Anthony Tuininga)
   3. Python users: Oracle Unstructured Data with	Multimeda SIG
      (Christopher Jones)
   4. Copying tables between two schemas? SQ*PLUS COPY? (Victor Hooi)


----------------------------------------------------------------------

Message: 1
Date: Tue, 25 Oct 2011 00:48:11 +0530
From: Anurag Chourasia <anu...@gm...>
Subject: Re: [cx-oracle-users] expecting None or a string
To: cx-...@li..., and...@we...
Message-ID:
	<CAN...@ma...>
Content-Type: text/plain; charset="iso-8859-1"

Actually, upon reading the link sent by Andres further, I could make it to
work by changing

cursor.execute(query)
to
cursor.execute(str(query))

Regards,
Anurag

On Tue, Oct 25, 2011 at 12:40 AM, Anurag Chourasia <
anu...@gm...> wrote:

> Thanks for the response Andreas but I am already passing string type
> objects in the substitution variables.
>
> This is really strange as this was working fine before and works fine from
> most places..
>
>
> On Tue, Oct 25, 2011 at 12:30 AM, Andreas Mock <and...@we...>wrote:
>
>> Hi Anurag,****
>>
>> ** **
>>
>> searching for your error message with google brought this****
>>
>> as one of the top results. Probably it helps:****
>>
>>
http://www.velocityreviews.com/forums/t691384-problem-about-cx_oracle.html
>> ****
>>
>> ** **
>>
>> Best regards****
>>
>> Andreas Mock****
>>
>> ** **
>>
>> ** **
>>
>> *From:* Anurag Chourasia [mailto:anu...@gm...]
>> *Sent:* Monday, October 24, 2011 8:39 PM
>> *To:* cx-...@li...; lar...@gm...
>> *Subject:* Re: [cx-oracle-users] expecting None or a string****
>>
>> ** **
>>
>> The print returns this****
>>
>> ** **
>>
>> SELECT count(*) FROM T WHERE NBR = '1' and ID='1'****
>>
>> ** **
>>
>> Both NBR and ID are varchar in my case.****
>>
>> ** **
>>
>> This query works fine when executed directly using Oracle PL/SQL Plus****
>>
>> ** **
>>
>> Regards,****
>>
>> Anurag****
>>
>> ** **
>>
>> On Tue, Oct 25, 2011 at 12:05 AM, Larry Martell <lar...@gm...>
>> wrote:****
>>
>> On Mon, Oct 24, 2011 at 12:31 PM, Anurag Chourasia
>> <anu...@gm...> wrote:
>> > Hi All,
>> > I am getting an error "expecting None or a string" when executing a
>> simple
>> > SQL Query as this.
>> > query = "SELECT count(*) FROM T WHERE NBR = '%s' and ID='%s'" %(nbr,id)
>> > print query
>> > cursor.execute(query)
>> > This was working fine before and I have neither changed the code nor
>> update
>> > cx_Oracle or Oracle version etc.****
>>
>> Well, what do you get when you print out the query? nrb or id probably
>> don't have the value you think.
>>
>>
>> -------------------------------------------------------------------------
-----
>> The demand for IT networking professionals continues to grow, and the
>> demand for specialized networking skills is growing even more rapidly.
>> Take a complimentary Learning@Cisco Self-Assessment and learn
>> about Cisco certifications, training, and career opportunities.
>> http://p.sf.net/sfu/cisco-dev2dev
>> _______________________________________________
>> cx-oracle-users mailing list
>> cx-...@li...
>> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users****
>>
>> ** **
>>
>>
>> -------------------------------------------------------------------------
-----
>> The demand for IT networking professionals continues to grow, and the
>> demand for specialized networking skills is growing even more rapidly.
>> Take a complimentary Learning@Cisco Self-Assessment and learn
>> about Cisco certifications, training, and career opportunities.
>> http://p.sf.net/sfu/cisco-dev2dev
>> _______________________________________________
>> cx-oracle-users mailing list
>> cx-...@li...
>> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>>
>>
>
-------------- next part --------------
An HTML attachment was scrubbed...

------------------------------

Message: 2
Date: Mon, 24 Oct 2011 19:58:09 -0600
From: Anthony Tuininga <ant...@gm...>
Subject: Re: [cx-oracle-users] expecting None or a string
To: cx-...@li...
Message-ID:
	<CAE...@ma...>
Content-Type: text/plain; charset=ISO-8859-1

If that is the case, let me suggest that you were using Unicode
without meaning to. You can also upgrade to 5.1 which allows you to
pass Unicode as query strings.

Anthony

On Mon, Oct 24, 2011 at 1:18 PM, Anurag Chourasia
<anu...@gm...> wrote:
> Actually, upon reading the link sent by Andres further, I could make it to
> work by changing
> cursor.execute(query)
> to
> cursor.execute(str(query))
>
> Regards,
> Anurag
>
> On Tue, Oct 25, 2011 at 12:40 AM, Anurag Chourasia
> <anu...@gm...> wrote:
>>
>> Thanks for the response Andreas but I am already passing string type
>> objects in the substitution variables.
>> This is really strange as this was working fine before and works fine
from
>> most places..
>>
>> On Tue, Oct 25, 2011 at 12:30 AM, Andreas Mock <and...@we...>
>> wrote:
>>>
>>> Hi Anurag,
>>>
>>>
>>>
>>> searching for your error message with google brought this
>>>
>>> as one of the top results. Probably it helps:
>>>
>>>
>>>
http://www.velocityreviews.com/forums/t691384-problem-about-cx_oracle.html
>>>
>>>
>>>
>>> Best regards
>>>
>>> Andreas Mock
>>>
>>>
>>>
>>>
>>>
>>> From: Anurag Chourasia [mailto:anu...@gm...]
>>> Sent: Monday, October 24, 2011 8:39 PM
>>> To: cx-...@li...; lar...@gm...
>>> Subject: Re: [cx-oracle-users] expecting None or a string
>>>
>>>
>>>
>>> The print returns this
>>>
>>>
>>>
>>> SELECT count(*) FROM T WHERE NBR = '1' and ID='1'
>>>
>>>
>>>
>>> Both NBR and ID are varchar in my case.
>>>
>>>
>>>
>>> This query works fine when executed directly using Oracle PL/SQL Plus
>>>
>>>
>>>
>>> Regards,
>>>
>>> Anurag
>>>
>>>
>>>
>>> On Tue, Oct 25, 2011 at 12:05 AM, Larry Martell
<lar...@gm...>
>>> wrote:
>>>
>>> On Mon, Oct 24, 2011 at 12:31 PM, Anurag Chourasia
>>> <anu...@gm...> wrote:
>>> > Hi All,
>>> > I am getting an error "expecting None or a string" when executing a
>>> > simple
>>> > SQL Query as this.
>>> > query = "SELECT count(*) FROM T WHERE NBR = '%s' and ID='%s'"
%(nbr,id)
>>> > print query
>>> > cursor.execute(query)
>>> > This was working fine before and I have neither changed the code nor
>>> > update
>>> > cx_Oracle or Oracle version etc.
>>>
>>> Well, what do you get when you print out the query? nrb or id probably
>>> don't have the value you think.
>>>
>>>
>>> ------------------------------------------------------------------------
------
>>> The demand for IT networking professionals continues to grow, and the
>>> demand for specialized networking skills is growing even more rapidly.
>>> Take a complimentary Learning@Cisco Self-Assessment and learn
>>> about Cisco certifications, training, and career opportunities.
>>> http://p.sf.net/sfu/cisco-dev2dev
>>> _______________________________________________
>>> cx-oracle-users mailing list
>>> cx-...@li...
>>> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>>>
>>>
>>>
>>>
>>> ------------------------------------------------------------------------
------
>>> The demand for IT networking professionals continues to grow, and the
>>> demand for specialized networking skills is growing even more rapidly.
>>> Take a complimentary Learning@Cisco Self-Assessment and learn
>>> about Cisco certifications, training, and career opportunities.
>>> http://p.sf.net/sfu/cisco-dev2dev
>>> _______________________________________________
>>> cx-oracle-users mailing list
>>> cx-...@li...
>>> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>>>
>>
>
>
> --------------------------------------------------------------------------
----
> The demand for IT networking professionals continues to grow, and the
> demand for specialized networking skills is growing even more rapidly.
> Take a complimentary Learning@Cisco Self-Assessment and learn
> about Cisco certifications, training, and career opportunities.
> http://p.sf.net/sfu/cisco-dev2dev
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>



------------------------------

Message: 3
Date: Tue, 25 Oct 2011 16:24:38 -0700
From: Christopher Jones <chr...@or...>
Subject: [cx-oracle-users] Python users: Oracle Unstructured Data with
	Multimeda SIG
To: cx-...@li...
Cc: Marcelle Kratochvil <mar...@pi...>
Message-ID: <4EA...@or...>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed


An Oracle Unstructured Data with Multimeda SIG has been launched by
Marcelle Kratochvil from Piction.  She is looking for Oracle-user
assistance, particularly regarding scripting languages such as Python.

Marcelle introduced the SIG in a session at the recent Oracle
OpenWorld conference in San Francisco.  She is well connected to
Oracle development teams and is in a great position to help shape
future versions of Oracle and MySQL databases with your input.

There is a placeholder site for the SIG at
https://sites.google.com/site/ommuds/

If you'd like to get involved, contact Marcelle directly at
mar...@pi...

Chris

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



------------------------------

Message: 4
Date: Wed, 2 Nov 2011 14:22:27 +1100
From: Victor Hooi <vic...@ya...>
Subject: [cx-oracle-users] Copying tables between two schemas? SQ*PLUS
	COPY?
To: cx-...@li...
Message-ID:
	<CAM...@ma...>
Content-Type: text/plain; charset="iso-8859-1"

Hi,

We have two separate schemas on different Oracle database instances, and we
have a need to copy the records from certain tables from one to the other.

Due to internal regulations, creating a database link between them isn't an
option - so I suppose that rules our using INSERT INTO...SELECT, right?

Previously, we were using SQL*PLUS's COPY extension:

http://www.oracleutilities.com/SQLPLus/copy.html

and this has worked really well.

However, now we'd like to automate this, and so I figured cx_Oracle would
be the way to go. However, I don't think I can use COPY from cx_Oracle
since it's SQL*PLUS specific (
http://stackoverflow.com/questions/7963441/using-sqlplus-copy-from-inside-cx
-oracle
).

Is there any way at all to get COPY to work from cx_Oracle? Or any other
way to achieve the same thing just as elegantly, and not iterate through
records?

Cheers,
Victor
-------------- next part --------------
An HTML attachment was scrubbed...

------------------------------

----------------------------------------------------------------------------
--
RSA&#174; Conference 2012
Save $700 by Nov 18
Register now&#33;
http://p.sf.net/sfu/rsa-sfdev2dev1

------------------------------

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


End of cx-oracle-users Digest, Vol 65, Issue 1
**********************************************

Re: [cx-oracle-users] troubleshooting cx_Oracle 64 bit gives : ImportError: DLL load failed: %1 is not a valid Win32 application.

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

You need to find out whether your Oracle client is 32-bit or 64-bit.
Everything must be 64-bit or everything must be 32-bit -- you cannot
mix and match. It looks to me like your Oracle client is 32-bit --
they are completely different. Sometimes the 64-bit client also has
32-bit libraries but they will be in an alternative directory. You can
generally find out (on Windows) by running SQL*Plus and then looking
at task manager and seeing if a little *32 exists behind the
application name. Also, on Windows do *not* set the environment
variable ORACLE_HOME. That almost always causes problems. Only set the
PATH environment variable.

Anthony

On Sat, Nov 5, 2011 at 8:01 AM, hari jayaram <ha...@gm...> wrote:
> Hi
>
> I am trying to setup cx_Oracle with 64 bit python and 32/64 bit
> instant client on a  64 bit Windows 7 machine
>
> I have the Oracle instant client installed at :
> C:\app\oracle\product\11.2.0\client_1
>
> I have the ORACLE_HOME environment variable defined and also have the
> C:\app\oracle\product\11.2.0\client_1\BIN directory in my path.
> I am running Python 2.7.2 64 bit mode
> WHen I import cx_Oracle I get the message " ImportError: DLL load
> failed: %1 is not a valid Win32 application."
>
> I dont know what bit-size my currently installed  Instant client
> application uses.
> I would prefer not switching to a 32 bit python.
>
> How do I get the 64 bit cx_Oracle msi to work. I tried getting a fresh
> 64 bit instant client from Oracle, but found many installation files
> at this location
> (http://www.oracle.com/technetwork/topics/winx64soft-089540.html)
> Which one do I need .
>
> Thanks for your help
> Hari
>
>
> #Here is what I have on the command prompt on import cx_Oracle
>
> C:\Users\harijay>cd %ORACLE_HOME%"
>
> C:\app\oracle\product\11.2.0\client_1>
>
>
> Python 2.7.2 (default, Jun 12 2011, 14:24:46) [MSC v.1500 64 bit (AMD64)] on win
> 32
> Type "help", "copyright", "credits" or "license" for more information.
>>>> import cx_Oracle
> Traceback (most recent call last):
>  File "<stdin>", line 1, in <module>
> ImportError: DLL load failed: %1 is not a valid Win32 application.
>>>>
>
> ------------------------------------------------------------------------------
> RSA(R) Conference 2012
> Save $700 by Nov 18
> Register now
> http://p.sf.net/sfu/rsa-sfdev2dev1
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

[cx-oracle-users] troubleshooting cx_Oracle 64 bit gives : ImportError: DLL load failed: %1 is not a valid Win32 application.

[hari j. <ha...@gm...>]

Hi

I am trying to setup cx_Oracle with 64 bit python and 32/64 bit
instant client on a  64 bit Windows 7 machine

I have the Oracle instant client installed at :
C:\app\oracle\product\11.2.0\client_1

I have the ORACLE_HOME environment variable defined and also have the
C:\app\oracle\product\11.2.0\client_1\BIN directory in my path.
I am running Python 2.7.2 64 bit mode
WHen I import cx_Oracle I get the message " ImportError: DLL load
failed: %1 is not a valid Win32 application."

I dont know what bit-size my currently installed  Instant client
application uses.
I would prefer not switching to a 32 bit python.

How do I get the 64 bit cx_Oracle msi to work. I tried getting a fresh
64 bit instant client from Oracle, but found many installation files
at this location
(http://www.oracle.com/technetwork/topics/winx64soft-089540.html)
Which one do I need .

Thanks for your help
Hari


#Here is what I have on the command prompt on import cx_Oracle

C:\Users\harijay>cd %ORACLE_HOME%"

C:\app\oracle\product\11.2.0\client_1>


Python 2.7.2 (default, Jun 12 2011, 14:24:46) [MSC v.1500 64 bit (AMD64)] on win
32
Type "help", "copyright", "credits" or "license" for more information.
>>> import cx_Oracle
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
ImportError: DLL load failed: %1 is not a valid Win32 application.
>>>