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

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

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

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/

Re: [cx-oracle-users] cx_Oracle and Python 2.4.1 on Windows

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

Hmm, do you have the Oracle 10g client (or instant client) installed
on your machine? That is necessary for using cx_Oracle. You should
also mention __which__ module could not be found -- that's usually
displayed in a message box. I'm suspecting that the module that could
not be found is oci.dll but if its something else that would help in
diagnosing your problem.

On Apr 6, 2005 9:01 AM, Carl Bignell <cbi...@nr...> wrote:
> All,
> 
> I am running Python 2.4.1 on a windows XP machine
> (with service pack 2).  I installed the cx_oracle
> module for Pyhton 2.5 and Oracle 10g.
> 
> When I import the module with the command
> 
> import cx_Oracle
> 
> I receive the following error:
> 
> Traceback (most recent call last):
>    File "<pyshell#0>", line 1, in -toplevel-
>      import cx_Oracle
> ImportError: DLL load failed: The specified module could not be found.
> 
> The cx_Oracle.pyd has been installed in the
> c:\python24\lib\site-packages directory and that
> directory is in the sys.path directory.
> 
> If this were an incorrectly named file I would have
> received the error:
> 
> ImportError: No module named cx_oracle
> 
> Could anyone suggest a fix for this problem?
> 
> Many thanks in advance,
> Carl
> 
> Carl Bignell
> cbi...@nr...
> 
> -------------------------------------------------------
> 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
>

[cx-oracle-users] cx_Oracle and Python 2.4.1 on Windows

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

All,

I am running Python 2.4.1 on a windows XP machine
(with service pack 2).  I installed the cx_oracle
module for Pyhton 2.5 and Oracle 10g.

When I import the module with the command

import cx_Oracle

I receive the following error:

Traceback (most recent call last):
   File "<pyshell#0>", line 1, in -toplevel-
     import cx_Oracle
ImportError: DLL load failed: The specified module could not be found.

The cx_Oracle.pyd has been installed in the
c:\python24\lib\site-packages directory and that
directory is in the sys.path directory.

If this were an incorrectly named file I would have
received the error:

ImportError: No module named cx_oracle

Could anyone suggest a fix for this problem?

Many thanks in advance,
Carl

Carl Bignell
cbi...@nr...

Re: [cx-oracle-users] Bug: CLOB fields and fetchall()

[Paul J. <pa...@we...>]

Hi Anthony,

Thanks for your explanation. That alternative syntax is much neater too.

Take care,

Paul


>This is a known issue with cx_Oracle 4.0.1 which is resolved in 4.1
>but probably not the way you expect. :-) A programming exception is
>raised when this situation is detected rather than simply providing
>incorrect information. This is necessary because of the way that
>Oracle works. BTW, you can more effectively do the following:
>
>for row in cursor:
>    print "Do something with the row."
>
>which iterates over the rows safely. Hope this helps.
>  
>
-- 
Paul Johnston, GSEC
Internet Security Specialist
Westpoint Limited
Albion Wharf, 19 Albion Street,
Manchester, M1 5LN
England
Tel: +44 (0)161 237 1028
Fax: +44 (0)161 237 1031
email: pa...@we...
web: www.westpoint.ltd.uk

Re: [cx-oracle-users] Bug: CLOB fields and fetchall()

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

Done.


On Thu, 31 Mar 2005 17:59:40 +0200, ama...@ub...
<ama...@ub...> wrote:
> 
> Hello,
> 
> Anthony Tuininga wrote:
> 
> > This is a known issue with cx_Oracle 4.0.1 which is resolved in 4.1
> > but probably not the way you expect. :-) A programming exception is
> > raised when this situation is detected rather than simply providing
> > incorrect information. This is necessary because of the way that
> > Oracle works.
> 
> By the way, a run under Purify noticed that the variable
> var->internalFetchNum (used to check that the LOB variable is in sync
> with the cursor) is not initialized.
> This cannot produce any bug - this number is only incremented and
> compared with previous values, so the initial value does not matter.
> 
> But I suggest that someone add the following line to the function
> Variable_New in Variable.c
> 
>     var->internalFetchNum=0;
> 
> --
> Amaury Forgeot d'Arc
> 
> -------------------------------------------------------
> This SF.net email is sponsored by Demarc:
> A global provider of Threat Management Solutions.
> Download our HomeAdmin security software for free today!
> http://www.demarc.com/info/Sentarus/hamr30
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

Re: [cx-oracle-users] Bug: CLOB fields and fetchall()

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

Hello,

Anthony Tuininga wrote:

> This is a known issue with cx_Oracle 4.0.1 which is resolved in 4.1
> but probably not the way you expect. :-) A programming exception is
> raised when this situation is detected rather than simply providing
> incorrect information. This is necessary because of the way that
> Oracle works.

By the way, a run under Purify noticed that the variable
var->internalFetchNum (used to check that the LOB variable is in sync
with the cursor) is not initialized.
This cannot produce any bug - this number is only incremented and
compared with previous values, so the initial value does not matter.

But I suggest that someone add the following line to the function
Variable_New in Variable.c

    var->internalFetchNum=0;

--
Amaury Forgeot d'Arc

Re: [cx-oracle-users] Bug: CLOB fields and fetchall()

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

On Thu, 31 Mar 2005 15:06:30 +0100, Paul Johnston <pa...@we...> wrote:
> Hi,
> 
> I'm using cx_Oracle 4.0.1 on SuSE 9.0 with Oracle 10gR1. I've found that
> fetchall does not work properly for CLOB fields - all the rows come back
> with the CLOB field having the same value (the other fields are fine).
> It works find if I use fetchone().
> 
> This causes a problem:
> 
>   for row in self.cur.fetchall():
>     ....
> 
> But this is fine:
> 
>     row = self.cur.fetchone()
>     while row:
>        ...
>       row = self.cur.fetchone()
> 
> We're happy with the workaround for now, so this isn't urgent, but I
> hope this bug report is useful.

This is a known issue with cx_Oracle 4.0.1 which is resolved in 4.1
but probably not the way you expect. :-) A programming exception is
raised when this situation is detected rather than simply providing
incorrect information. This is necessary because of the way that
Oracle works. BTW, you can more effectively do the following:

for row in cursor:
    print "Do something with the row."

which iterates over the rows safely. Hope this helps.

> Regards,
> 
> Paul
> 
> --
> Paul Johnston, GSEC
> Internet Security Specialist
> Westpoint Limited
> Albion Wharf, 19 Albion Street,
> Manchester, M1 5LN
> England
> Tel: +44 (0)161 237 1028
> Fax: +44 (0)161 237 1031
> email: pa...@we...
> web: www.westpoint.ltd.uk
> 
> -------------------------------------------------------
> This SF.net email is sponsored by Demarc:
> A global provider of Threat Management Solutions.
> Download our HomeAdmin security software for free today!
> http://www.demarc.com/info/Sentarus/hamr30
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

[cx-oracle-users] Bug: CLOB fields and fetchall()

[Paul J. <pa...@we...>]

Hi,

I'm using cx_Oracle 4.0.1 on SuSE 9.0 with Oracle 10gR1. I've found that 
fetchall does not work properly for CLOB fields - all the rows come back 
with the CLOB field having the same value (the other fields are fine). 
It works find if I use fetchone().

This causes a problem:

  for row in self.cur.fetchall():
    ....

But this is fine:

    row = self.cur.fetchone()
    while row:
       ...
      row = self.cur.fetchone()

We're happy with the workaround for now, so this isn't urgent, but I 
hope this bug report is useful.

Regards,

Paul

-- 
Paul Johnston, GSEC
Internet Security Specialist
Westpoint Limited
Albion Wharf, 19 Albion Street,
Manchester, M1 5LN
England
Tel: +44 (0)161 237 1028
Fax: +44 (0)161 237 1031
email: pa...@we...
web: www.westpoint.ltd.uk

RE: [cx-oracle-users] insert using executemanyprepared

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

Steve,

Thanks for the idea but in general my code needs to work across databases, not using db links, (I
know my simple example didn't show this but I was just using a simple test case to reproduce the
error) so I don't think INSERT /*+ APPEND */ will work. 

But if you know otherwise then pls let me know as INSERT /*+ APPEND */ is definately better for
performance.

Thanks again,

Chris
--- "Orr, Steve" <so...@ri...> wrote:

> If performance is a concern how about this?
> 
> INSERT /* APPEND */ INTO ...
> 
> 
> 
> -----Original Message-----
> From: cx-...@li...
> [mailto:cx-...@li...] On Behalf Of Chris
> Dunscombe
> Sent: Monday, March 21, 2005 12:01 PM
> To: cx-...@li...
> Subject: Re: [cx-oracle-users] insert using executemanyprepared
> 
> 
> Anthony,
> 
> It works a treat.
> 
> Thanks very much,
> 
> Chris
> 
> PS I'll give executemanyprepared a miss for now but I may come back for
> help later.
> --- Chris Dunscombe <cdu...@ya...> wrote:
> 



		
__________________________________ 
Do you Yahoo!? 
Yahoo! Small Business - Try our new resources site!
http://smallbusiness.yahoo.com/resources/ 

RE: [cx-oracle-users] insert using executemanyprepared

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

If performance is a concern how about this?

INSERT /* APPEND */ INTO ...



-----Original Message-----
From: cx-...@li...
[mailto:cx-...@li...] On Behalf Of Chris
Dunscombe
Sent: Monday, March 21, 2005 12:01 PM
To: cx-...@li...
Subject: Re: [cx-oracle-users] insert using executemanyprepared


Anthony,

It works a treat.

Thanks very much,

Chris

PS I'll give executemanyprepared a miss for now but I may come back for
help later.
--- Chris Dunscombe <cdu...@ya...> wrote:

> Thanks very much for that. I'll give it a go when I get home to-night.
>=20
> Chris
> --- Anthony Tuininga <ant...@gm...> wrote:
>=20
> > First, your code demonstrates a problem with cx_Oracle that appears=20
> > to be rather subtle. I'm not sure exactly what the problem is yet=20
> > but I'll look into it a bit more yet. However, I do have a solution=20
> > for you that solves your problem. If you set your arraysize large=20
> > enough to accomodate the amount of data you are inserting, your=20
> > problem goes away. In other words
> >=20
> > cursor.arraysize =3D len(listDict)
> >=20
> > BTW, I tried this code with cx_Oracle 4.0.1 and it fails as well but

> > for a different reason (arraysize not large enough) so I'm not sure=20
> > why it works for you.
> >=20
> > For performance purposes, executemanyprepared() definitely helps but

> > another solution will get you most of that performance while at the=20
> > same time making your code much easier to read. Your example can be=20
> > rewritten as follows:
> >=20
> > # this part is identical except that the massaging of the list is=20
> > unnecessary cur1 =3D db1.cursor() db1 =3D=20
> > cx_Oracle.connect("regt/test@sxl920d2")
> > cur1 =3D db1.cursor()
> > cur1.execute("select * from binary_source")
> > data =3D cur1.fetchall()
> >=20
> > # this is the part that differs
> > curInsert =3D db1.cursor()
> > curInsert.arraysize =3D len(data)    # this shouldn't be necessary =
but
> > at the moment it is
> > curInsert.prepare("insert into WAREHOUSES_STOCK
> > (PRODUCT_ID,WAREHOUSE_ID,QUANTITY_ON_HAND)  values (:1,:2,:3)")=20
> > curInsert.setinputsizes(cx_Oracle.NUMBER, cx_Oracle.BINARY,=20
> > cx_Oracle.NUMBER) curInsert.executemany(None, data) # None means use

> > the prepared statement # or you can do=20
> > curInsert.executemany(curInsert.statement, data), whichever you find

> > more appealing
> >=20
> > cx_Oracle 4.1 allows binding by position. Thus, you can simply=20
> > perform the fetch() on the one cursor and the executemany() on the=20
> > other cursor without having to massage it. This should provide all=20
> > of the performance you require. If you still think you need help=20
> > with
> > executemanyprepared() I can try to help you on that front as well.
> >=20
> > Hope this helps.
> >=20
> > On Sat, 19 Mar 2005 06:17:17 -0800 (PST), Chris Dunscombe=20
> > <cdu...@ya...> wrote:
> > > All,
> > >=20
> > > Upto now I've been using executemany to bulk insert data. Upto=20
> > > 4.0.1 this has been fine but
> > now
> > > with 4.1.0 it doesn't seem to work with RAW columns, see below for

> > > a simple example:
> > >=20
> > > import cx_Oracle
> > > db1 =3D cx_Oracle.connect("regt/test@sxl920d2")
> > > cur1 =3D db1.cursor()
> > > cur1.execute("select * from binary_source")
> > > data =3D cur1.fetchall()
> > > listDict =3D []
> > > for row in data:
> > >     tempDict =3D {}
> > >     x =3D 1
> > >     for item in row:
> > >         tempDict["arg" + str(x)] =3D item
> > >         x =3D x + 1
> > >     listDict.append(tempDict)
> > >=20
> > > curInsert =3D db1.cursor()
> > > curInsert.setinputsizes( arg1 =3D cx_Oracle.NUMBER, arg2 =3D=20
> > > cx_Oracle.BINARY, arg3 =3D
> > > cx_Oracle.NUMBER)
> > > insStr =3D "insert into WAREHOUSES_STOCK
(PRODUCT_ID,WAREHOUSE_ID,QUANTITY_ON_HAND)  values
> > > (:arg1,:arg2,:arg3)"
> > > curInsert.prepare(insStr)
> > > curInsert.executemany(insStr, listDict)
> > >=20
> > > This gives the following error using 4.1.0, works OK for 4.0.1=20
> > > (Oracle 9.2 on Linux):
> > >=20
> > > Traceback (most recent call last):
> > >   File "binary2.py", line 19, in ?
> > >     curInsert.executemany(insStr, listDict)
> > > cx_Oracle.DatabaseError: ORA-01465: invalid hex number
> > >=20
> > > As I'm concerned about performance I've look at using=20
> > > executemanyprepared (looked at
> > copydata.py
> > > in cx_OracleTools) but haven't managed to get it to work.
> > >=20
> > > Pls does someone have a simple step-by-step example of using=20
> > > executemanyprepared with an
> > insert
> > > that could be used in my example above to replace the executemany.
> > >=20
> > > Thanks very much,
> > >=20
> > > Chris
> > >=20
> > > __________________________________
> > > Do you Yahoo!?
> > > Yahoo! Sports - Sign up for Fantasy Baseball.=20
> > > http://baseball.fantasysports.yahoo.com/
> > >=20
> > > -------------------------------------------------------
> > > SF email is sponsored by - The IT Product Guide
> > > Read honest & candid reviews on hundreds of IT Products from real=20
> > > users. Discover which products truly live up to the hype. Start=20
> > > reading now.=20
> > > 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
> > >
> >=20
> >=20
> > -------------------------------------------------------
> > SF email is sponsored by - The IT Product Guide
> > Read honest & candid reviews on hundreds of IT Products from real=20
> > users. Discover which products truly live up to the hype. Start=20
> > 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
> >=20
>=20
>=20
> __________________________________________________
> Do You Yahoo!?
> Tired of spam?  Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com=20
>=20
>=20
> -------------------------------------------------------
> SF email is sponsored by - The IT Product Guide
> Read honest & candid reviews on hundreds of IT Products from real=20
> users. Discover which products truly live up to the hype. Start=20
> 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
>=20



	=09
__________________________________=20
Do you Yahoo!?=20
Make Yahoo! your home page=20
http://www.yahoo.com/r/hs


-------------------------------------------------------
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] insert using executemanyprepared

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

Anthony,

It works a treat.

Thanks very much,

Chris

PS I'll give executemanyprepared a miss for now but I may come back for help later.
--- Chris Dunscombe <cdu...@ya...> wrote:

> Thanks very much for that. I'll give it a go when I get home to-night.
> 
> Chris
> --- Anthony Tuininga <ant...@gm...> wrote:
> 
> > First, your code demonstrates a problem with cx_Oracle that appears to
> > be rather subtle. I'm not sure exactly what the problem is yet but
> > I'll look into it a bit more yet. However, I do have a solution for
> > you that solves your problem. If you set your arraysize large enough
> > to accomodate the amount of data you are inserting, your problem goes
> > away. In other words
> > 
> > cursor.arraysize = len(listDict)
> > 
> > BTW, I tried this code with cx_Oracle 4.0.1 and it fails as well but
> > for a different reason (arraysize not large enough) so I'm not sure
> > why it works for you.
> > 
> > For performance purposes, executemanyprepared() definitely helps but
> > another solution will get you most of that performance while at the
> > same time making your code much easier to read. Your example can be
> > rewritten as follows:
> > 
> > # this part is identical except that the massaging of the list is unnecessary
> > cur1 = db1.cursor()
> > db1 = cx_Oracle.connect("regt/test@sxl920d2")
> > cur1 = db1.cursor()
> > cur1.execute("select * from binary_source")
> > data = cur1.fetchall()
> > 
> > # this is the part that differs
> > curInsert = db1.cursor()
> > curInsert.arraysize = len(data)    # this shouldn't be necessary but
> > at the moment it is
> > curInsert.prepare("insert into WAREHOUSES_STOCK
> > (PRODUCT_ID,WAREHOUSE_ID,QUANTITY_ON_HAND)  values (:1,:2,:3)")
> > curInsert.setinputsizes(cx_Oracle.NUMBER, cx_Oracle.BINARY, cx_Oracle.NUMBER)
> > curInsert.executemany(None, data) # None means use the prepared statement
> > # or you can do curInsert.executemany(curInsert.statement, data),
> > whichever you find more appealing
> > 
> > cx_Oracle 4.1 allows binding by position. Thus, you can simply perform
> > the fetch() on the one cursor and the executemany() on the other
> > cursor without having to massage it. This should provide all of the
> > performance you require. If you still think you need help with
> > executemanyprepared() I can try to help you on that front as well.
> > 
> > Hope this helps.
> > 
> > On Sat, 19 Mar 2005 06:17:17 -0800 (PST), Chris Dunscombe
> > <cdu...@ya...> wrote:
> > > All,
> > > 
> > > Upto now I've been using executemany to bulk insert data. Upto 4.0.1 this has been fine but
> > now
> > > with 4.1.0 it doesn't seem to work with RAW columns, see below for a simple example:
> > > 
> > > import cx_Oracle
> > > db1 = cx_Oracle.connect("regt/test@sxl920d2")
> > > cur1 = db1.cursor()
> > > cur1.execute("select * from binary_source")
> > > data = cur1.fetchall()
> > > listDict = []
> > > for row in data:
> > >     tempDict = {}
> > >     x = 1
> > >     for item in row:
> > >         tempDict["arg" + str(x)] = item
> > >         x = x + 1
> > >     listDict.append(tempDict)
> > > 
> > > curInsert = db1.cursor()
> > > curInsert.setinputsizes( arg1 = cx_Oracle.NUMBER, arg2 = cx_Oracle.BINARY, arg3 =
> > > cx_Oracle.NUMBER)
> > > insStr = "insert into WAREHOUSES_STOCK (PRODUCT_ID,WAREHOUSE_ID,QUANTITY_ON_HAND)  values
> > > (:arg1,:arg2,:arg3)"
> > > curInsert.prepare(insStr)
> > > curInsert.executemany(insStr, listDict)
> > > 
> > > This gives the following error using 4.1.0, works OK for 4.0.1 (Oracle 9.2 on Linux):
> > > 
> > > Traceback (most recent call last):
> > >   File "binary2.py", line 19, in ?
> > >     curInsert.executemany(insStr, listDict)
> > > cx_Oracle.DatabaseError: ORA-01465: invalid hex number
> > > 
> > > As I'm concerned about performance I've look at using executemanyprepared (looked at
> > copydata.py
> > > in cx_OracleTools) but haven't managed to get it to work.
> > > 
> > > Pls does someone have a simple step-by-step example of using executemanyprepared with an
> > insert
> > > that could be used in my example above to replace the executemany.
> > > 
> > > Thanks very much,
> > > 
> > > Chris
> > > 
> > > __________________________________
> > > Do you Yahoo!?
> > > Yahoo! Sports - Sign up for Fantasy Baseball.
> > > http://baseball.fantasysports.yahoo.com/
> > > 
> > > -------------------------------------------------------
> > > 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
> > >
> > 
> > 
> > -------------------------------------------------------
> > 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
> > 
> 
> 
> __________________________________________________
> Do You Yahoo!?
> Tired of spam?  Yahoo! Mail has the best spam protection around 
> http://mail.yahoo.com 
> 
> 
> -------------------------------------------------------
> 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
> 



		
__________________________________ 
Do you Yahoo!? 
Make Yahoo! your home page 
http://www.yahoo.com/r/hs

Re: [cx-oracle-users] insert using executemanyprepared

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

Thanks very much for that. I'll give it a go when I get home to-night.

Chris
--- Anthony Tuininga <ant...@gm...> wrote:

> First, your code demonstrates a problem with cx_Oracle that appears to
> be rather subtle. I'm not sure exactly what the problem is yet but
> I'll look into it a bit more yet. However, I do have a solution for
> you that solves your problem. If you set your arraysize large enough
> to accomodate the amount of data you are inserting, your problem goes
> away. In other words
> 
> cursor.arraysize = len(listDict)
> 
> BTW, I tried this code with cx_Oracle 4.0.1 and it fails as well but
> for a different reason (arraysize not large enough) so I'm not sure
> why it works for you.
> 
> For performance purposes, executemanyprepared() definitely helps but
> another solution will get you most of that performance while at the
> same time making your code much easier to read. Your example can be
> rewritten as follows:
> 
> # this part is identical except that the massaging of the list is unnecessary
> cur1 = db1.cursor()
> db1 = cx_Oracle.connect("regt/test@sxl920d2")
> cur1 = db1.cursor()
> cur1.execute("select * from binary_source")
> data = cur1.fetchall()
> 
> # this is the part that differs
> curInsert = db1.cursor()
> curInsert.arraysize = len(data)    # this shouldn't be necessary but
> at the moment it is
> curInsert.prepare("insert into WAREHOUSES_STOCK
> (PRODUCT_ID,WAREHOUSE_ID,QUANTITY_ON_HAND)  values (:1,:2,:3)")
> curInsert.setinputsizes(cx_Oracle.NUMBER, cx_Oracle.BINARY, cx_Oracle.NUMBER)
> curInsert.executemany(None, data) # None means use the prepared statement
> # or you can do curInsert.executemany(curInsert.statement, data),
> whichever you find more appealing
> 
> cx_Oracle 4.1 allows binding by position. Thus, you can simply perform
> the fetch() on the one cursor and the executemany() on the other
> cursor without having to massage it. This should provide all of the
> performance you require. If you still think you need help with
> executemanyprepared() I can try to help you on that front as well.
> 
> Hope this helps.
> 
> On Sat, 19 Mar 2005 06:17:17 -0800 (PST), Chris Dunscombe
> <cdu...@ya...> wrote:
> > All,
> > 
> > Upto now I've been using executemany to bulk insert data. Upto 4.0.1 this has been fine but
> now
> > with 4.1.0 it doesn't seem to work with RAW columns, see below for a simple example:
> > 
> > import cx_Oracle
> > db1 = cx_Oracle.connect("regt/test@sxl920d2")
> > cur1 = db1.cursor()
> > cur1.execute("select * from binary_source")
> > data = cur1.fetchall()
> > listDict = []
> > for row in data:
> >     tempDict = {}
> >     x = 1
> >     for item in row:
> >         tempDict["arg" + str(x)] = item
> >         x = x + 1
> >     listDict.append(tempDict)
> > 
> > curInsert = db1.cursor()
> > curInsert.setinputsizes( arg1 = cx_Oracle.NUMBER, arg2 = cx_Oracle.BINARY, arg3 =
> > cx_Oracle.NUMBER)
> > insStr = "insert into WAREHOUSES_STOCK (PRODUCT_ID,WAREHOUSE_ID,QUANTITY_ON_HAND)  values
> > (:arg1,:arg2,:arg3)"
> > curInsert.prepare(insStr)
> > curInsert.executemany(insStr, listDict)
> > 
> > This gives the following error using 4.1.0, works OK for 4.0.1 (Oracle 9.2 on Linux):
> > 
> > Traceback (most recent call last):
> >   File "binary2.py", line 19, in ?
> >     curInsert.executemany(insStr, listDict)
> > cx_Oracle.DatabaseError: ORA-01465: invalid hex number
> > 
> > As I'm concerned about performance I've look at using executemanyprepared (looked at
> copydata.py
> > in cx_OracleTools) but haven't managed to get it to work.
> > 
> > Pls does someone have a simple step-by-step example of using executemanyprepared with an
> insert
> > that could be used in my example above to replace the executemany.
> > 
> > Thanks very much,
> > 
> > Chris
> > 
> > __________________________________
> > Do you Yahoo!?
> > Yahoo! Sports - Sign up for Fantasy Baseball.
> > http://baseball.fantasysports.yahoo.com/
> > 
> > -------------------------------------------------------
> > 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
> >
> 
> 
> -------------------------------------------------------
> 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
> 


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

Re: [cx-oracle-users] insert using executemanyprepared

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

First, your code demonstrates a problem with cx_Oracle that appears to
be rather subtle. I'm not sure exactly what the problem is yet but
I'll look into it a bit more yet. However, I do have a solution for
you that solves your problem. If you set your arraysize large enough
to accomodate the amount of data you are inserting, your problem goes
away. In other words

cursor.arraysize = len(listDict)

BTW, I tried this code with cx_Oracle 4.0.1 and it fails as well but
for a different reason (arraysize not large enough) so I'm not sure
why it works for you.

For performance purposes, executemanyprepared() definitely helps but
another solution will get you most of that performance while at the
same time making your code much easier to read. Your example can be
rewritten as follows:

# this part is identical except that the massaging of the list is unnecessary
cur1 = db1.cursor()
db1 = cx_Oracle.connect("regt/test@sxl920d2")
cur1 = db1.cursor()
cur1.execute("select * from binary_source")
data = cur1.fetchall()

# this is the part that differs
curInsert = db1.cursor()
curInsert.arraysize = len(data)    # this shouldn't be necessary but
at the moment it is
curInsert.prepare("insert into WAREHOUSES_STOCK
(PRODUCT_ID,WAREHOUSE_ID,QUANTITY_ON_HAND)  values (:1,:2,:3)")
curInsert.setinputsizes(cx_Oracle.NUMBER, cx_Oracle.BINARY, cx_Oracle.NUMBER)
curInsert.executemany(None, data) # None means use the prepared statement
# or you can do curInsert.executemany(curInsert.statement, data),
whichever you find more appealing

cx_Oracle 4.1 allows binding by position. Thus, you can simply perform
the fetch() on the one cursor and the executemany() on the other
cursor without having to massage it. This should provide all of the
performance you require. If you still think you need help with
executemanyprepared() I can try to help you on that front as well.

Hope this helps.

On Sat, 19 Mar 2005 06:17:17 -0800 (PST), Chris Dunscombe
<cdu...@ya...> wrote:
> All,
> 
> Upto now I've been using executemany to bulk insert data. Upto 4.0.1 this has been fine but now
> with 4.1.0 it doesn't seem to work with RAW columns, see below for a simple example:
> 
> import cx_Oracle
> db1 = cx_Oracle.connect("regt/test@sxl920d2")
> cur1 = db1.cursor()
> cur1.execute("select * from binary_source")
> data = cur1.fetchall()
> listDict = []
> for row in data:
>     tempDict = {}
>     x = 1
>     for item in row:
>         tempDict["arg" + str(x)] = item
>         x = x + 1
>     listDict.append(tempDict)
> 
> curInsert = db1.cursor()
> curInsert.setinputsizes( arg1 = cx_Oracle.NUMBER, arg2 = cx_Oracle.BINARY, arg3 =
> cx_Oracle.NUMBER)
> insStr = "insert into WAREHOUSES_STOCK (PRODUCT_ID,WAREHOUSE_ID,QUANTITY_ON_HAND)  values
> (:arg1,:arg2,:arg3)"
> curInsert.prepare(insStr)
> curInsert.executemany(insStr, listDict)
> 
> This gives the following error using 4.1.0, works OK for 4.0.1 (Oracle 9.2 on Linux):
> 
> Traceback (most recent call last):
>   File "binary2.py", line 19, in ?
>     curInsert.executemany(insStr, listDict)
> cx_Oracle.DatabaseError: ORA-01465: invalid hex number
> 
> As I'm concerned about performance I've look at using executemanyprepared (looked at copydata.py
> in cx_OracleTools) but haven't managed to get it to work.
> 
> Pls does someone have a simple step-by-step example of using executemanyprepared with an insert
> that could be used in my example above to replace the executemany.
> 
> Thanks very much,
> 
> Chris
> 
> __________________________________
> Do you Yahoo!?
> Yahoo! Sports - Sign up for Fantasy Baseball.
> http://baseball.fantasysports.yahoo.com/
> 
> -------------------------------------------------------
> 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
>

[cx-oracle-users] insert using executemanyprepared

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

All,

Upto now I've been using executemany to bulk insert data. Upto 4.0.1 this has been fine but now
with 4.1.0 it doesn't seem to work with RAW columns, see below for a simple example:

import cx_Oracle
db1 = cx_Oracle.connect("regt/test@sxl920d2")
cur1 = db1.cursor()
cur1.execute("select * from binary_source")
data = cur1.fetchall()
listDict = []
for row in data:
    tempDict = {}
    x = 1
    for item in row:
        tempDict["arg" + str(x)] = item
        x = x + 1
    listDict.append(tempDict)

curInsert = db1.cursor()
curInsert.setinputsizes( arg1 = cx_Oracle.NUMBER, arg2 = cx_Oracle.BINARY, arg3 =
cx_Oracle.NUMBER)
insStr = "insert into WAREHOUSES_STOCK (PRODUCT_ID,WAREHOUSE_ID,QUANTITY_ON_HAND)  values
(:arg1,:arg2,:arg3)"
curInsert.prepare(insStr)
curInsert.executemany(insStr, listDict)

This gives the following error using 4.1.0, works OK for 4.0.1 (Oracle 9.2 on Linux):

Traceback (most recent call last):
  File "binary2.py", line 19, in ?
    curInsert.executemany(insStr, listDict)
cx_Oracle.DatabaseError: ORA-01465: invalid hex number

As I'm concerned about performance I've look at using executemanyprepared (looked at copydata.py
in cx_OracleTools) but haven't managed to get it to work.

Pls does someone have a simple step-by-step example of using executemanyprepared with an insert
that could be used in my example above to replace the executemany.

Thanks very much,

Chris






		
__________________________________ 
Do you Yahoo!? 
Yahoo! Sports - Sign up for Fantasy Baseball. 
http://baseball.fantasysports.yahoo.com/

RE: [cx-oracle-users] Oracle functions and input/output parameter s

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

> You can use the following technique which works with both callfunc()
> and callproc():
> 
> <helpful advice snipped...>
>
> Hopefully this makes sense to you. :-)

Ah, that's great. As it happens the problem was with the Oracle side anyway,
but it's useful to know how do do things properly for future reference!

Many thanks,

Nik

Re: [cx-oracle-users] Oracle functions and input/output parameters

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

You can use the following technique which works with both callfunc()
and callproc():

# this creates a bind variable of type string of length 4000
outStringVar = cursor.var(cx_Oracle.STRING)

# bind the new variable into the function call
result = cursor.callfunc("GetStuff", cx_Oracle.NUMBER,
        (number1, number2, outStringVar))

# get the result from the bind variable after the function has succeeded
outString = outStringVar.getvalue()

Hopefully this makes sense to you. :-)

On Thu, 17 Mar 2005 09:30:51 -0000, Nik Barron
<Nik...@pe...> wrote:
> Hi,
> 
> I have an Oracle function that has an output parameter, e.g.
> 
>  GetStuff(number1, number2, outstring)
> 
> where outstring is a VARCHAR2. The funtion returns an error code, or 0 for
> success in which case the data is in outstring.
> 
> When calling this via cx_Oracle's callfunc(), I get the return code but no
> data in outstring. Looking at the docs I suspect this is expected behaviour;
> previously I've only dealt with input/output parameters with callproc()
> which works fine.
> 
> Should callfunc() be populating outstring or do I need to get our Oracle
> guys to rewrite the functions as procedures and use callproc()?
> 
> Many thanks,
> 
> Nik
> 
> -------------------------------------------------------
> 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
>

[cx-oracle-users] Oracle functions and input/output parameters

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

Hi,

I have an Oracle function that has an output parameter, e.g.

 GetStuff(number1, number2, outstring)

where outstring is a VARCHAR2. The funtion returns an error code, or 0 for
success in which case the data is in outstring.

When calling this via cx_Oracle's callfunc(), I get the return code but no
data in outstring. Looking at the docs I suspect this is expected behaviour;
previously I've only dealt with input/output parameters with callproc()
which works fine.

Should callfunc() be populating outstring or do I need to get our Oracle
guys to rewrite the functions as procedures and use callproc()?

Many thanks,

Nik

Re: [cx-oracle-users] connect from a remote box

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

Klaus Boehm wrote:
> cx_Oracle.DatabaseError:
> ORA-12545: Connect failed because target host or object does not exist

in your dsn, you have written the HOST= parameter between quotes.
If I do the same, I also get the same error.
Remove the quotes and retry!

--
Amaury Forgeot d'Arc

Re: [cx-oracle-users] connect from a remote box

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

My experience:

import cx_Oracle as cx
#This is one line
dsn=3D"(DESCRIPTION=3D(ADDRESS=3D(PROTOCOL=3DTCP)(HOST=3Drioja.proteus)(P=
ORT=3D1521))
( CONNECT_DATA=3D(SERVICE_NAME=3Dmaster9)))"
c=3Dcx.connect('system/manager@'+dsn)=20

connects fine.

Trobleshooting:
ping rioja.proteus
[shoud receive ping answer]
telnet rioja.proteus 1521
[should hang waiting for input but not refuse connection]

$ORACLE_HOME/bin/sqlplus
'system/salta@(DESCRIPTION=3D(ADDRESS=3D(PROTOCOL=3DTCP)
(HOST=3Drioja.proteus)(PORT=3D1521))(CONNECT_DATA=3D(SERVICE_NAME=3Dmaste=
r9)))'
[should connect]

Ask your dba :-)

El mi=C3=A9, 09-03-2005 a las 15:59 +0100, Klaus Boehm escribi=C3=B3:
> My client OS is Debian woody.
> Oracle Client is 9i.
> cx_Oracle-4.1-9i-py23-1.i386.rpm
>=20
> Now i have defined the ORACLE_HOME Variable.
>=20
> i have a new error message:
>=20
>=20
> cx_Oracle.DatabaseError: ORA-12545: Connect failed because target host =
or object=20
> does not exist
>=20
> i have a tcpdump running on my nic:
>=20
> tcpdump -i eth0 dst port 1521
>=20
> but i see no traffic on eth0
>=20
> it seems there is no try from the remote host to connect to
> the db-server.
>=20
> > If the suggestions already given in this thread are not enough to
> > solve the problem we'll need to know the client OS, the version of th=
e
> > Oracle client that you are using and the version of cx_Oracle that yo=
u
> > are using.
> >=20
> > On Wed, 09 Mar 2005 14:24:07 +0100, Marcos S=C3=A1nchez Provencio
> > <ma...@bu...> wrote:
> >=20
> >>Is ORACLE_HOME defined?
> >>
> >>El mi=C3=A9, 09-03-2005 a las 14:06 +0100, Klaus Boehm escribi=C3=B3:
> >>
> >>>ama...@ub... wrote:
> >>>
> >>>>Klaus Boehm wrote:
> >>>>
> >>>>
> >>>>>i try to connect to a oracle db.
> >>>>>this db runs on another server.
> >>>>>now i have problem with the connect string.
> >>>>>
> >>>>>connect([user, password, dsn, mode, handle, pool, threaded, twopha=
se])
> >>>>>
> >>>>>how looks the dsn if i try to connect from a remote box?
> >>>>
> >>>>
> >>>>If you know how to connect with sql*plus, you've already typed a
> >>>>connection string like "<user>/<password>@<dsn>". So it's easy.
> >>>>
> >>>>
> >>>>Else, you must know the name of the server, and the name of the dat=
abase
> >>>>on the server (the service name).
> >>>>
> >>>>If you installed the Oracle client version 10g, the dsn is simply
> >>>>dsn =3D "<server>/<service>"
> >>>>
> >>>>With a 8i or 9i Oracle client, it's more complicated:
> >>>>dsn =3D "(DESCRIPTION=3D(ADDRESS=3D(PROTOCOL=3DTCP)(HOST
> >>>>=3D<server>)(PORT=3D1521))(CONNECT_DATA=3D(SERVICE_NAME=3D<service>=
)))"
> >>>>(All in one string, you may add spaces if you want)
> >>>>You may want to have a (short) alias for this long string, in this =
case
> >>>>you must edit the tnsnames.ora file. But this is another story.
> >>>>
> >>>>Hope this helps,
> >>>>
> >>>>--
> >>>>Amaury Forgeot d'Arc
> >>>>
> >>>>
> >>>>
> >>>>
> >>>>-------------------------------------------------------
> >>>>SF email is sponsored by - The IT Product Guide
> >>>>Read honest & candid reviews on hundreds of IT Products from real u=
sers.
> >>>>Discover which products truly live up to the hype. Start reading no=
w.
> >>>>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
> >>>>
> >>>
> >>>Now i have the correct dsn.
> >>>
> >>>
> >>>  dsn =3D
> >>>"(DESCRIPTION=3D(ADDRESS_LIST=3D(ADDRESS=3D(PROTOCOL=3DTCP)(HOST=3D'=
db-os.dmg.ewt.de')(PORT=3D1521)))(CONNECT_DATA=3D(SID=3DOSPROD)))"
> >>>
> >>>connection =3D cx_Oracle.connect("xxxx", "yyyy", dsn)
> >>>
> >>>i get the following error:
> >>>
> >>>RuntimeError: Unable to acquire Oracle environment handle
> >>>
> >>>have anyone a idea?
> >>>
> >>>
> >>>Klaus
> >>>
> >>
> >>-------------------------------------------------------
> >>SF email is sponsored by - The IT Product Guide
> >>Read honest & candid reviews on hundreds of IT Products from real use=
rs.
> >>Discover which products truly live up to the hype. Start reading now.
> >>http://ads.osdn.com/?ad_ide95&alloc_id=14396&opclick
> >>_______________________________________________
> >>cx-oracle-users mailing list
> >>cx-...@li...
> >>https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> >>
> >=20
> >=20
> >=20
> > -------------------------------------------------------
> > SF email is sponsored by - The IT Product Guide
> > Read honest & candid reviews on hundreds of IT Products from real use=
rs.
> > Discover which products truly live up to the hype. Start reading now.
> > http://ads.osdn.com/?ad_ide95&alloc_id=14396&op=3Dclick
> > _______________________________________________
> > cx-oracle-users mailing list
> > cx-...@li...
> > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> >=20
>=20
>=20
> --=20
> ---------------------------------------------
> Klaus Boehm
> Systemadministrator
> ewt gmbh
> D-86152 Augsburg, Volkhartstr. 4-6
> Phone:  +49.(0)821.3106-319
> Fax: +49.(0)821.310660-319
> mailto:k....@ew...
> http://www.ewt.de
> http://www.surf-club.de
> ---------------------------------------------
>=20
>=20
>=20
>=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] connect from a remote box

[Klaus B. <k....@ew...>]

My client OS is Debian woody.
Oracle Client is 9i.
cx_Oracle-4.1-9i-py23-1.i386.rpm

Now i have defined the ORACLE_HOME Variable.

i have a new error message:


cx_Oracle.DatabaseError: ORA-12545: Connect failed because target host or object 
does not exist

i have a tcpdump running on my nic:

tcpdump -i eth0 dst port 1521

but i see no traffic on eth0

it seems there is no try from the remote host to connect to
the db-server.

> If the suggestions already given in this thread are not enough to
> solve the problem we'll need to know the client OS, the version of the
> Oracle client that you are using and the version of cx_Oracle that you
> are using.
> 
> On Wed, 09 Mar 2005 14:24:07 +0100, Marcos Sánchez Provencio
> <ma...@bu...> wrote:
> 
>>Is ORACLE_HOME defined?
>>
>>El mié, 09-03-2005 a las 14:06 +0100, Klaus Boehm escribió:
>>
>>>ama...@ub... wrote:
>>>
>>>>Klaus Boehm wrote:
>>>>
>>>>
>>>>>i try to connect to a oracle db.
>>>>>this db runs on another server.
>>>>>now i have problem with the connect string.
>>>>>
>>>>>connect([user, password, dsn, mode, handle, pool, threaded, twophase])
>>>>>
>>>>>how looks the dsn if i try to connect from a remote box?
>>>>
>>>>
>>>>If you know how to connect with sql*plus, you've already typed a
>>>>connection string like "<user>/<password>@<dsn>". So it's easy.
>>>>
>>>>
>>>>Else, you must know the name of the server, and the name of the database
>>>>on the server (the service name).
>>>>
>>>>If you installed the Oracle client version 10g, the dsn is simply
>>>>dsn = "<server>/<service>"
>>>>
>>>>With a 8i or 9i Oracle client, it's more complicated:
>>>>dsn = "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST
>>>>=<server>)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=<service>)))"
>>>>(All in one string, you may add spaces if you want)
>>>>You may want to have a (short) alias for this long string, in this case
>>>>you must edit the tnsnames.ora file. But this is another story.
>>>>
>>>>Hope this helps,
>>>>
>>>>--
>>>>Amaury Forgeot d'Arc
>>>>
>>>>
>>>>
>>>>
>>>>-------------------------------------------------------
>>>>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
>>>>
>>>
>>>Now i have the correct dsn.
>>>
>>>
>>>  dsn =
>>>"(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST='db-os.dmg.ewt.de')(PORT=1521)))(CONNECT_DATA=(SID=OSPROD)))"
>>>
>>>connection = cx_Oracle.connect("xxxx", "yyyy", dsn)
>>>
>>>i get the following error:
>>>
>>>RuntimeError: Unable to acquire Oracle environment handle
>>>
>>>have anyone a idea?
>>>
>>>
>>>Klaus
>>>
>>
>>-------------------------------------------------------
>>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_ide95&alloc_id396&opclick
>>_______________________________________________
>>cx-oracle-users mailing list
>>cx-...@li...
>>https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>>
> 
> 
> 
> -------------------------------------------------------
> 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_ide95&alloc_id396&op=click
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> 


-- 
---------------------------------------------
Klaus Boehm
Systemadministrator
ewt gmbh
D-86152 Augsburg, Volkhartstr. 4-6
Phone:  +49.(0)821.3106-319
Fax: +49.(0)821.310660-319
mailto:k....@ew...
http://www.ewt.de
http://www.surf-club.de
---------------------------------------------

Re: [cx-oracle-users] connect from a remote box

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

If the suggestions already given in this thread are not enough to
solve the problem we'll need to know the client OS, the version of the
Oracle client that you are using and the version of cx_Oracle that you
are using.

On Wed, 09 Mar 2005 14:24:07 +0100, Marcos S=E1nchez Provencio
<ma...@bu...> wrote:
> Is ORACLE_HOME defined?
>=20
> El mi=E9, 09-03-2005 a las 14:06 +0100, Klaus Boehm escribi=F3:
> > ama...@ub... wrote:
> > > Klaus Boehm wrote:
> > >
> > >>i try to connect to a oracle db.
> > >>this db runs on another server.
> > >>now i have problem with the connect string.
> > >>
> > >>connect([user, password, dsn, mode, handle, pool, threaded, twophase]=
)
> > >>
> > >>how looks the dsn if i try to connect from a remote box?
> > >
> > >
> > > If you know how to connect with sql*plus, you've already typed a
> > > connection string like "<user>/<password>@<dsn>". So it's easy.
> > >
> > >
> > > Else, you must know the name of the server, and the name of the datab=
ase
> > > on the server (the service name).
> > >
> > > If you installed the Oracle client version 10g, the dsn is simply
> > > dsn =3D "<server>/<service>"
> > >
> > > With a 8i or 9i Oracle client, it's more complicated:
> > > dsn =3D "(DESCRIPTION=3D(ADDRESS=3D(PROTOCOL=3DTCP)(HOST
> > > =3D<server>)(PORT=3D1521))(CONNECT_DATA=3D(SERVICE_NAME=3D<service>))=
)"
> > > (All in one string, you may add spaces if you want)
> > > You may want to have a (short) alias for this long string, in this ca=
se
> > > you must edit the tnsnames.ora file. But this is another story.
> > >
> > > Hope this helps,
> > >
> > > --
> > > Amaury Forgeot d'Arc
> > >
> > >
> > >
> > >
> > > -------------------------------------------------------
> > > SF email is sponsored by - The IT Product Guide
> > > Read honest & candid reviews on hundreds of IT Products from real use=
rs.
> > > 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
> > >
> > Now i have the correct dsn.
> >
> >
> >   dsn =3D
> > "(DESCRIPTION=3D(ADDRESS_LIST=3D(ADDRESS=3D(PROTOCOL=3DTCP)(HOST=3D'db-=
os.dmg.ewt.de')(PORT=3D1521)))(CONNECT_DATA=3D(SID=3DOSPROD)))"
> >
> > connection =3D cx_Oracle.connect("xxxx", "yyyy", dsn)
> >
> > i get the following error:
> >
> > RuntimeError: Unable to acquire Oracle environment handle
> >
> > have anyone a idea?
> >
> >
> > Klaus
> >
>=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_ide95&alloc_id=14396&opclick
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

Re: [cx-oracle-users] connect from a remote box

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

Is ORACLE_HOME defined?

El mi=C3=A9, 09-03-2005 a las 14:06 +0100, Klaus Boehm escribi=C3=B3:
> ama...@ub... wrote:
> > Klaus Boehm wrote:
> >=20
> >>i try to connect to a oracle db.
> >>this db runs on another server.
> >>now i have problem with the connect string.
> >>
> >>connect([user, password, dsn, mode, handle, pool, threaded, twophase]=
)
> >>
> >>how looks the dsn if i try to connect from a remote box?
> >=20
> >=20
> > If you know how to connect with sql*plus, you've already typed a
> > connection string like "<user>/<password>@<dsn>". So it's easy.
> >=20
> >=20
> > Else, you must know the name of the server, and the name of the datab=
ase
> > on the server (the service name).
> >=20
> > If you installed the Oracle client version 10g, the dsn is simply
> > dsn =3D "<server>/<service>"
> >=20
> > With a 8i or 9i Oracle client, it's more complicated:
> > dsn =3D "(DESCRIPTION=3D(ADDRESS=3D(PROTOCOL=3DTCP)(HOST
> > =3D<server>)(PORT=3D1521))(CONNECT_DATA=3D(SERVICE_NAME=3D<service>))=
)"
> > (All in one string, you may add spaces if you want)
> > You may want to have a (short) alias for this long string, in this ca=
se
> > you must edit the tnsnames.ora file. But this is another story.
> >=20
> > Hope this helps,
> >=20
> > --
> > Amaury Forgeot d'Arc
> >=20
> >=20
> >=20
> >=20
> > -------------------------------------------------------
> > SF email is sponsored by - The IT Product Guide
> > Read honest & candid reviews on hundreds of IT Products from real use=
rs.
> > 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
> >=20
> Now i have the correct dsn.
>=20
>=20
>   dsn =3D=20
> "(DESCRIPTION=3D(ADDRESS_LIST=3D(ADDRESS=3D(PROTOCOL=3DTCP)(HOST=3D'db-=
os.dmg.ewt.de')(PORT=3D1521)))(CONNECT_DATA=3D(SID=3DOSPROD)))"
>=20
> connection =3D cx_Oracle.connect("xxxx", "yyyy", dsn)
>=20
> i get the following error:
>=20
> RuntimeError: Unable to acquire Oracle environment handle
>=20
> have anyone a idea?
>=20
>=20
> Klaus
>=20

Re: [cx-oracle-users] connect from a remote box

[Klaus B. <k....@ew...>]

ama...@ub... wrote:
> Klaus Boehm wrote:
> 
>>i try to connect to a oracle db.
>>this db runs on another server.
>>now i have problem with the connect string.
>>
>>connect([user, password, dsn, mode, handle, pool, threaded, twophase])
>>
>>how looks the dsn if i try to connect from a remote box?
> 
> 
> If you know how to connect with sql*plus, you've already typed a
> connection string like "<user>/<password>@<dsn>". So it's easy.
> 
> 
> Else, you must know the name of the server, and the name of the database
> on the server (the service name).
> 
> If you installed the Oracle client version 10g, the dsn is simply
> dsn = "<server>/<service>"
> 
> With a 8i or 9i Oracle client, it's more complicated:
> dsn = "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST
> =<server>)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=<service>)))"
> (All in one string, you may add spaces if you want)
> You may want to have a (short) alias for this long string, in this case
> you must edit the tnsnames.ora file. But this is another story.
> 
> Hope this helps,
> 
> --
> Amaury Forgeot d'Arc
> 
> 
> 
> 
> -------------------------------------------------------
> 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
> 
Now i have the correct dsn.


  dsn = 
"(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST='db-os.dmg.ewt.de')(PORT=1521)))(CONNECT_DATA=(SID=OSPROD)))"

connection = cx_Oracle.connect("xxxx", "yyyy", dsn)

i get the following error:

RuntimeError: Unable to acquire Oracle environment handle

have anyone a idea?


Klaus

-- 
---------------------------------------------
Klaus Boehm
Systemadministrator
ewt gmbh
D-86152 Augsburg, Volkhartstr. 4-6
Phone:  +49.(0)821.3106-319
Fax: +49.(0)821.310660-319
mailto:k....@ew...
http://www.ewt.de
http://www.surf-club.de
---------------------------------------------

Re: [cx-oracle-users] connect from a remote box

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

Klaus Boehm wrote:
> i try to connect to a oracle db.
> this db runs on another server.
> now i have problem with the connect string.
>
> connect([user, password, dsn, mode, handle, pool, threaded, twophase])
>
> how looks the dsn if i try to connect from a remote box?

If you know how to connect with sql*plus, you've already typed a
connection string like "<user>/<password>@<dsn>". So it's easy.


Else, you must know the name of the server, and the name of the database
on the server (the service name).

If you installed the Oracle client version 10g, the dsn is simply
dsn = "<server>/<service>"

With a 8i or 9i Oracle client, it's more complicated:
dsn = "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST
=<server>)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=<service>)))"
(All in one string, you may add spaces if you want)
You may want to have a (short) alias for this long string, in this case
you must edit the tnsnames.ora file. But this is another story.

Hope this helps,

--
Amaury Forgeot d'Arc

[cx-oracle-users] connect from a remote box

[Klaus B. <k....@ew...>]

Hei,

i try to connect to a oracle db.
this db runs on another server.
now i have problem with the connect string.

connect([user, password, dsn, mode, handle, pool, threaded, twophase])

how looks the dsn if i try to connect from a remote box?

thanks in advance.

Klaus
-- 
---------------------------------------------
Klaus Boehm
Systemadministrator
ewt gmbh
D-86152 Augsburg, Volkhartstr. 4-6
Phone:  +49.(0)821.3106-319
Fax: +49.(0)821.310660-319
mailto:k....@ew...
http://www.ewt.de
http://www.surf-club.de
---------------------------------------------