Re: MERGE SQL in cx_Oracle executemany
On 2020-10-18 06:35:03 -, Mladen Gogala via Python-list wrote: > On Sat, 17 Oct 2020 21:23:40 -0600, Jason Friedman wrote: > >> I'm looking to insert values into an oracle table (my_table) using the > >> query below. The insert query works when the PROJECT is not NULL/empty > >> (""). However when PROJECT is an empty string(''), the query creates a > >> new duplicate row every time the code is executed (with project value > >> populating as null). I would like to modify my query so a new row is > >> not inserted when all column values are matched (including when project > >> code is null). [...] > > Perhaps the issue is that NULL is not equal to anything. Oracle provides > > the IS NULL function to determine if a value is NULL. > > > > Note also you define "cur" but executemany with "cur3". > > > > And is "rows = [tuple(x) for x in df.values]" what you want? Print it. > > Obviously, the "PROJECT" column is causing the issue. NULL in Oracle > database is never equal to anything. Not only in Oracle. This is standard SQL behaviour. NULL means "unknown", and if you compare two unknown values, the result is of course also unknown. However, Oracle adds an additional complication because it converts '' (the empty string) to NULL on insert. > If :7 is NULL, your "not matched" > condition is satisfied and your MERGE statement will insert a new and > exciting row. That has nothing to do with Python. The only solution is > "ALTER TABLE my_table modify(project not null)" or > "ALTER TABLE my_table add constraint project_nn check(project is not null)" I Yup. In addition, because that won't accept an empty string, you'll have to use some non-empty string (e.g. '-' or '(no project)') to signify that there is no project. (One might argue that this is better design anyway (explicit rather than implicit).) > If you already have NULL values in the PROJECT column than use "add > constraint" > with NOVALIDATE option. Other than that, allowing NULL values in key columns > is > a sign of bad design. Namely, Oracle cannot index NULL columns, so PROJECT IS > NULL > predicate cannot be resolved by an index. It can with a bitmap index. However, last time I looked (admittedly long ago) this was an enterprise edition feature. hp -- _ | Peter J. Holzer| Story must make more sense than reality. |_|_) || | | | h...@hjp.at |-- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" signature.asc Description: PGP signature -- https://mail.python.org/mailman/listinfo/python-list
Re: MERGE SQL in cx_Oracle executemany
On Sat, 17 Oct 2020 21:23:40 -0600, Jason Friedman wrote: >> I'm looking to insert values into an oracle table (my_table) using the >> query below. The insert query works when the PROJECT is not NULL/empty >> (""). However when PROJECT is an empty string(''), the query creates a >> new duplicate row every time the code is executed (with project value >> populating as null). I would like to modify my query so a new row is >> not inserted when all column values are matched (including when project >> code is null). >> I'm guessing I would need to include a "when matched" statement, but >> not too sure on how to get this going. Would appreciate help with this, >> thanks. >> >> ``` >> con = cx_Oracle.connect(connstr) >> cur = con.cursor() >> rows = [tuple(x) for x in df.values] >> cur3.executemany('''merge into my_table using dual on (YEAR = :1 and >> QUARTER = :2 and CODE = :3 and AMOUNT = :4 and DATE = :5 and COMMENTS = >> :6 and PROJECT = :7) >> when not matched then insert values (:1, :2, :3, :4, :5, :6, :7) >> ''',rows) >> con.commit() >> cur.close() >> con.close() >> >> > Perhaps the issue is that NULL is not equal to anything. Oracle provides > the IS NULL function to determine if a value is NULL. > > Note also you define "cur" but executemany with "cur3". > > And is "rows = [tuple(x) for x in df.values]" what you want? Print it. Obviously, the "PROJECT" column is causing the issue. NULL in Oracle database is never equal to anything. If :7 is NULL, your "not matched" condition is satisfied and your MERGE statement will insert a new and exciting row. That has nothing to do with Python. The only solution is "ALTER TABLE my_table modify(project not null)" or "ALTER TABLE my_table add constraint project_nn check(project is not null)" I If you already have NULL values in the PROJECT column than use "add constraint" with NOVALIDATE option. Other than that, allowing NULL values in key columns is a sign of bad design. Namely, Oracle cannot index NULL columns, so PROJECT IS NULL predicate cannot be resolved by an index. -- Mladen Gogala Database Consultant http://mgogala.byethost5.com -- https://mail.python.org/mailman/listinfo/python-list
Re: MERGE SQL in cx_Oracle executemany
> > I'm looking to insert values into an oracle table (my_table) using the > query below. The insert query works when the PROJECT is not NULL/empty > (""). However when PROJECT is an empty string(''), the query creates a new > duplicate row every time the code is executed (with project value > populating as null). I would like to modify my query so a new row is not > inserted when all column values are matched (including when project code is > null). > I'm guessing I would need to include a "when matched" statement, but not > too sure on how to get this going. Would appreciate help with this, thanks. > > ``` > con = cx_Oracle.connect(connstr) > cur = con.cursor() > rows = [tuple(x) for x in df.values] > cur3.executemany('''merge into my_table > using dual > on (YEAR = :1 and QUARTER = :2 and CODE = :3 and AMOUNT = :4 and DATE = :5 > and COMMENTS = :6 and PROJECT = :7) > when not matched then insert values (:1, :2, :3, :4, :5, :6, :7) > ''',rows) > con.commit() > cur.close() > con.close() > Perhaps the issue is that NULL is not equal to anything. Oracle provides the IS NULL function to determine if a value is NULL. Note also you define "cur" but executemany with "cur3". And is "rows = [tuple(x) for x in df.values]" what you want? Print it. -- https://mail.python.org/mailman/listinfo/python-list
MERGE SQL in cx_Oracle executemany
Hi there, I'm looking to insert values into an oracle table (my_table) using the query below. The insert query works when the PROJECT is not NULL/empty (""). However when PROJECT is an empty string(''), the query creates a new duplicate row every time the code is executed (with project value populating as null). I would like to modify my query so a new row is not inserted when all column values are matched (including when project code is null). I'm guessing I would need to include a "when matched" statement, but not too sure on how to get this going. Would appreciate help with this, thanks. ``` con = cx_Oracle.connect(connstr) cur = con.cursor() rows = [tuple(x) for x in df.values] cur3.executemany('''merge into my_table using dual on (YEAR = :1 and QUARTER = :2 and CODE = :3 and AMOUNT = :4 and DATE = :5 and COMMENTS = :6 and PROJECT = :7) when not matched then insert values (:1, :2, :3, :4, :5, :6, :7) ''',rows) con.commit() cur.close() con.close() ``` -- https://mail.python.org/mailman/listinfo/python-list
Re: cx_Oracle + array parameter
I did your solution. I created a varray like this: TYPE LIST_IDS IS TABLE OF INT INDEX BY BINARY_INTEGER, but when I try to use in a sql statement SELECT appears an oracle error cannot access row in nested table. I use oracle 11g and I read that you can use a varray declare in plsql to sql statement. -- https://mail.python.org/mailman/listinfo/python-list
Re: cx_Oracle + array parameter
I did your solution. I created a varray like this: TYPE LIST_IDS IS TABLE OF INT INDEX BY BINARY_INTEGER, but when I try to use in a sql statement SELECT appears an oracle error cannot access row in nested table. I use oracle 11g and I read that you can use a varray declare in plsql to sql statement. -- https://mail.python.org/mailman/listinfo/python-list
Re: cx_Oracle, callfunc and varray
On Fri, Jan 9, 2015 at 12:24 PM, Dom wrote: > Hi > > I'm trying to return a simple array of numbers from a package using cx_oracle > (5.1.2). I believe this is possible. I've not been able to find anything that > suggest it isn't I'm afraid I don't have an answer for you, but you would probably have better luck asking on the cx-oracle-users mailing list: https://lists.sourceforge.net/lists/listinfo/cx-oracle-users -- https://mail.python.org/mailman/listinfo/python-list
Re: cx_Oracle, callfunc and varray
In Dom writes: > create or replace PACKAGE SIMPLEPACKAGE > AS > FUNCTION DoSomethingSimple( > cust_id INTEGER) > RETURN numarray; > FUNCTION DoSomethingSimpler( > cust_id INTEGER) > RETURN INTEGER; > END SIMPLEPACKAGE; > / > Any ideas what I'm doing wrong? Is RETURN INTEGER; allowed? -- John Gordon Imagine what it must be like for a real medical doctor to gor...@panix.comwatch 'House', or a real serial killer to watch 'Dexter'. -- https://mail.python.org/mailman/listinfo/python-list
cx_Oracle, callfunc and varray
Hi I'm trying to return a simple array of numbers from a package using cx_oracle (5.1.2). I believe this is possible. I've not been able to find anything that suggest it isn't create or replace TYPE NUMARRAY -- Simple VArray of numbers is VARRAY(3) OF NUMBER; / create or replace PACKAGE SIMPLEPACKAGE AS FUNCTION DoSomethingSimple( cust_id INTEGER) RETURN numarray; FUNCTION DoSomethingSimpler( cust_id INTEGER) RETURN INTEGER; END SIMPLEPACKAGE; / create or replace PACKAGE BODY SIMPLEPACKAGE AS FUNCTION DOSOMETHINGSIMPLE( cust_id INTEGER) RETURN numarray AS simple_array numarray := numarray(); BEGIN simple_array.extend; simple_array(1) := cust_id; simple_array.extend; simple_array(2) := cust_id; simple_array.extend; simple_array(3) := cust_id; RETURN SIMPLE_ARRAY; END DOSOMETHINGSIMPLE; FUNCTION DOSOMETHINGSIMPLER( cust_id INTEGER) RETURN INTEGER AS BEGIN RETURN cust_id; END DOSOMETHINGSIMPLER; END SIMPLEPACKAGE; / The python (2.7) is very simple import cx_Oracle if __name__ == '__main__': with cx_Oracle.connect('soe', 'soe', 'oracle12c2/soe') as connection: try: cursor = connection.cursor(); ArrayType = cursor.arrayvar(cx_Oracle.NUMBER,3) NumberType = cursor.var(cx_Oracle.NUMBER) cursor.callfunc("SIMPLEPACKAGE.DOSOMETHINGSIMPLER", NumberType, [99]) cursor.callfunc("SIMPLEPACKAGE.DOSOMETHINGSIMPLE", ArrayType, [99]) except cx_Oracle.DatabaseError as dberror: print dberror finally: cursor.close() The call to return works just fine. The call to return the function gives the error ORA-06550: line 1, column 13: PLS-00382: expression is of wrong type ORA-06550: line 1, column 7: PL/SQL: Statement ignored Any ideas what I'm doing wrong? Dom -- https://mail.python.org/mailman/listinfo/python-list
Problem in fetching blob or clob data with python and cx_oracle
It seems that when I attempt to download blob or clob data using fetchmany, it can not keep track of the LOB variable in subsequent fetch. The problem is over if I fetch one row at a time but it is not optimal. Can anyone give me an idea how to efficiently fetch columns with clob or blob data from oracle DB using python. Thank you, Anjan -- https://mail.python.org/mailman/listinfo/python-list
Re: cx_Oracle throws: ImportError: DLL load failed: This application has failed to start ...
On Tue, Nov 26, 2013 at 7:22 AM, Ruben van den Berg wrote: > I haven't the slightest clue why version 11 just - wouldn't - run but due to > backward compatibility it seems a stressful weekend got a happy ending anyway. Doesn't make particular sense to me either, but I don't know anything about Oracle :) Glad it's working, anyhow. If nothing else, you have a clean installation of version 12, so if your v11 was wrong in some way, this simply sidestepped it. Often that's the easiest fix. ChrisA -- https://mail.python.org/mailman/listinfo/python-list
Re: cx_Oracle throws: ImportError: DLL load failed: This application has failed to start ...
Thx for all the suggestions! I hope this doesn't come as a disappointment but it seems the final solution was to install version 12 (instead of 11) of Oracle's instantclient and to include the inner folder (holding OCI.DLL and related files) to "Path" and "ORACLE_HOME". I haven't the slightest clue why version 11 just - wouldn't - run but due to backward compatibility it seems a stressful weekend got a happy ending anyway. Op maandag 25 november 2013 11:38:39 UTC+1 schreef Albert-Jan Roskam: > > > On Sun, 11/24/13, MRAB wrote: > > > > Subject: Re: cx_Oracle throws: ImportError: DLL load failed: This > application has failed to start ... > > To: python-list@python.org > > Date: Sunday, November 24, 2013, 7:17 PM > > > > On 24/11/2013 17:12, Ruben van den > > Berg wrote: > > > I'm on Windows XP SP3, Python 2.7.1. On running > > > > > > import cx_Oracle > > > > > > I got the error > > > > > > ImportError: DLL load failed: This application has > > failed to start because the application configuration is > > incorrect. Reinstalling the application may fix this > > problem. > > > > > > I then ran Dependency Walker on cx_Oracle.pyd. Its > > first complaint was about msvcr80.dll. However, this file is > > present in > > > C:\WINDOWS\WinSxS\x86_Microsoft.VC80.CRT_1fc8b3b9a1e18e3b_8.0.50727.91_x-ww_0de56c07. > > (I believe it's part of the MS Visual Studio C++ 2008 > > package which I installed.) > > > > > > I obviously uninstalled and reinstalled the cx_Oracle a > > couple of times but so far to no avail. > > > > > > Does anybody have a clue what to try next? > > > > > > For a screenshot of Dependency Walker, please see: > https://dl.dropboxusercontent.com/u/116120595/dep_walker_orac.jpg > > > > > It looks like it's a path issue. > > > > You say that msvcr80.dll is in > > > C:\WINDOWS\WinSxS\x86_Microsoft.VC80.CRT_1fc8b3b9a1e18e3b_8.0.50727.91_x-ww_0de56c07, > > > > but is that folder listed as part of the search path? > > > > Have a look at the Windows' PATH environment variable. > > > > > > ===> Unlike in Linux with LD_LIBRARY_PATH, you can change PATH at runtime in > Windows, e.g > > import os, sys, ctypes > > if sys.platform.startswith("win"): > > os.environ["PATH"] += (os.pathsep + r"c:\your\new\path") > > ctypes.WinDLL("msvcr80.dll") -- https://mail.python.org/mailman/listinfo/python-list
Re: cx_Oracle throws: ImportError: DLL load failed: This application has failed to start ...
On Sun, 11/24/13, MRAB wrote: Subject: Re: cx_Oracle throws: ImportError: DLL load failed: This application has failed to start ... To: python-list@python.org Date: Sunday, November 24, 2013, 7:17 PM On 24/11/2013 17:12, Ruben van den Berg wrote: > I'm on Windows XP SP3, Python 2.7.1. On running > > import cx_Oracle > > I got the error > > ImportError: DLL load failed: This application has failed to start because the application configuration is incorrect. Reinstalling the application may fix this problem. > > I then ran Dependency Walker on cx_Oracle.pyd. Its first complaint was about msvcr80.dll. However, this file is present in C:\WINDOWS\WinSxS\x86_Microsoft.VC80.CRT_1fc8b3b9a1e18e3b_8.0.50727.91_x-ww_0de56c07. (I believe it's part of the MS Visual Studio C++ 2008 package which I installed.) > > I obviously uninstalled and reinstalled the cx_Oracle a couple of times but so far to no avail. > > Does anybody have a clue what to try next? > > For a screenshot of Dependency Walker, please see: > https://dl.dropboxusercontent.com/u/116120595/dep_walker_orac.jpg > It looks like it's a path issue. You say that msvcr80.dll is in C:\WINDOWS\WinSxS\x86_Microsoft.VC80.CRT_1fc8b3b9a1e18e3b_8.0.50727.91_x-ww_0de56c07, but is that folder listed as part of the search path? Have a look at the Windows' PATH environment variable. ===> Unlike in Linux with LD_LIBRARY_PATH, you can change PATH at runtime in Windows, e.g import os, sys, ctypes if sys.platform.startswith("win"): os.environ["PATH"] += (os.pathsep + r"c:\your\new\path") ctypes.WinDLL("msvcr80.dll") -- https://mail.python.org/mailman/listinfo/python-list
Re: cx_Oracle throws: ImportError: DLL load failed: This application has failed to start ...
On 24/11/2013 17:12, Ruben van den Berg wrote: I'm on Windows XP SP3, Python 2.7.1. On running import cx_Oracle I got the error ImportError: DLL load failed: This application has failed to start because the application configuration is incorrect. Reinstalling the application may fix this problem. I then ran Dependency Walker on cx_Oracle.pyd. Its first complaint was about msvcr80.dll. However, this file is present in C:\WINDOWS\WinSxS\x86_Microsoft.VC80.CRT_1fc8b3b9a1e18e3b_8.0.50727.91_x-ww_0de56c07. (I believe it's part of the MS Visual Studio C++ 2008 package which I installed.) I obviously uninstalled and reinstalled the cx_Oracle a couple of times but so far to no avail. Does anybody have a clue what to try next? For a screenshot of Dependency Walker, please see: https://dl.dropboxusercontent.com/u/116120595/dep_walker_orac.jpg It looks like it's a path issue. You say that msvcr80.dll is in C:\WINDOWS\WinSxS\x86_Microsoft.VC80.CRT_1fc8b3b9a1e18e3b_8.0.50727.91_x-ww_0de56c07, but is that folder listed as part of the search path? Have a look at the Windows' PATH environment variable. -- https://mail.python.org/mailman/listinfo/python-list
Re: cx_Oracle throws: ImportError: DLL load failed: This application has failed to start ...
On Mon, Nov 25, 2013 at 4:12 AM, Ruben van den Berg wrote: > ImportError: DLL load failed: This application has failed to start because > the application configuration is incorrect. Reinstalling the application may > fix this problem. > > I then ran Dependency Walker on cx_Oracle.pyd. Its first complaint was about > msvcr80.dll. However, this file is present in > C:\WINDOWS\WinSxS\x86_Microsoft.VC80.CRT_1fc8b3b9a1e18e3b_8.0.50727.91_x-ww_0de56c07. > (I believe it's part of the MS Visual Studio C++ 2008 package which I > installed.) Welcome to DLL hell... There is one thing I would suggest: Check to see if you've matched the word size (32-bit vs 64-bit) for Python and all the DLLs. You can't (normally) load a 64-bit DLL into a 32-bit process or vice versa. ChrisA -- https://mail.python.org/mailman/listinfo/python-list
cx_Oracle throws: ImportError: DLL load failed: This application has failed to start ...
I'm on Windows XP SP3, Python 2.7.1. On running import cx_Oracle I got the error ImportError: DLL load failed: This application has failed to start because the application configuration is incorrect. Reinstalling the application may fix this problem. I then ran Dependency Walker on cx_Oracle.pyd. Its first complaint was about msvcr80.dll. However, this file is present in C:\WINDOWS\WinSxS\x86_Microsoft.VC80.CRT_1fc8b3b9a1e18e3b_8.0.50727.91_x-ww_0de56c07. (I believe it's part of the MS Visual Studio C++ 2008 package which I installed.) I obviously uninstalled and reinstalled the cx_Oracle a couple of times but so far to no avail. Does anybody have a clue what to try next? For a screenshot of Dependency Walker, please see: https://dl.dropboxusercontent.com/u/116120595/dep_walker_orac.jpg -- https://mail.python.org/mailman/listinfo/python-list
Re: cx_Oracle clause IN using a variable
Il giorno martedì 16 ottobre 2012 19:23:22 UTC+2, Hans Mulder ha scritto: > On 16/10/12 15:41:58, Beppe wrote: > > > Hi all, > > > I don't know if it is the correct place to set this question, however, > > > I'm using cx_Oracle to query an Oracle database. > > > I've a problem to use the IN clause with a variable. > > > My statement is > > > > > > sql = "SELECT field1,field2,field3 > > > FROM my_table > > > WHERE field_3 IN (:arg_1)" > > > > > > where arg_1 is retrive by a dictionary > > > that is build so > > > > > > my_dict = {'location':"X", > > > 'oracle_user':'user', > > > 'oracle_password':'pass', > > > 'dsn':'dsn', > > > 'mailto':'some...@somewhere.org', > > > 'codes':"CNI,CNP"} > > > > > > args = (dict['codes'],) > > > > > > > > > con = cx_Oracle.connect(my_dict["oracle_user"], > > > my_dict["oracle_password"], > > > my_dict["dsn"]) > > > > > > cur = con.cursor() > > > cur.execute(sql,args) > > > rs = cur.fetchall() > > > > > > but it doesn't work in the sense that doesn't return anything > > > > > > If i use the statment without variable > > > > > > SELECT field1,field2,field3 > > > FROM my_table > > > WHERE field_3 IN ('CNI','CNP') > > > > > > the query works > > > > > > what is wrong? > > > > You only have a single placeholder variable, > > so your statement is equivalent to > > > > SELECT field1,field2,field3 > > FROM my_table > > WHERE field_3 IN ('CNI,CNP') > > > > Presumably 'CNI,CNP' is not a valid value for field_3, > > thus your query finds no records. > > > > > suggestions? > > > > To verify that you have the correct syntax, try it > > with a single value first: > > > > my_dict = {'location':"X", > > 'oracle_user':'user', > > 'oracle_password':'pass', > > 'dsn':'dsn', > > 'mailto':'some...@somewhere.org', > > 'codes':"CNI"} > > > > It that produces some of the records you want, then the > > question is really: can you somehow pass a list of values > > via a single placeholder variable? > > > > I'm, not a cx_Oracle expert, but I think the answer is "no". > > > > > > If you want to pass exactly two values, then the work-around > > would be to pass them in separate variables: > > > > my_dict = {'location':"X", > > 'oracle_user':'user', > > 'oracle_password':'pass', > > 'dsn':'dsn', > > 'mailto':'some...@somewhere.org', > > 'code1':"CNI", > > 'code2':"CNP"} > > > > sql = """SELECT field1,field2,field3 > > FROM my_table > > WHERE field_3 IN (:arg_1, :arg_2)""" > > args = (my_dict['code1'],my_dict['code2']) > > > > > > If the number of codes can vary, you'll have to generate a > > query with the correct number of placholders in it. Mabye > > something like this (untested): > > > > my_dict = {'location':"X", > > 'oracle_user':'user', > > 'oracle_password':'pass', > > 'dsn':'dsn', > > 'mailto':'some...@somewhere.org', > > 'codes':"Ornhgvshy,vf,orggre,guna,htyl"} > > > > > > args = my_dict['codes'].split(",") > > placeholders = ','.join(":x%d" % i for i,_ in enumerate(args)) > > > > sql = """SELECT field1,field2,field3 > > FROM my_table > > WHERE field_3 IN (%s)""" % placeholders > > > > con = cx_Oracle.connect(my_dict["oracle_user"], > > my_dict["oracle_password"], > > my_dict["dsn"]) > > > > cur = con.cursor() > > cur.execute(sql,args) > > rs = cur.fetchall() > > > > > > Hope this helps, > > > > -- HansM Thanks a lot of to ian and hans for your explanations that have allowed me to resolve my problem and above all to understand the why I was wrong. regards beppe -- http://mail.python.org/mailman/listinfo/python-list
Re: cx_Oracle clause IN using a variable
On 16/10/12 15:41:58, Beppe wrote: > Hi all, > I don't know if it is the correct place to set this question, however, > I'm using cx_Oracle to query an Oracle database. > I've a problem to use the IN clause with a variable. > My statement is > > sql = "SELECT field1,field2,field3 > FROM my_table > WHERE field_3 IN (:arg_1)" > > where arg_1 is retrive by a dictionary > that is build so > > my_dict = {'location':"X", > 'oracle_user':'user', > 'oracle_password':'pass', > 'dsn':'dsn', > 'mailto':'some...@somewhere.org', > 'codes':"CNI,CNP"} > > args = (dict['codes'],) > > > con = cx_Oracle.connect(my_dict["oracle_user"], > my_dict["oracle_password"], > my_dict["dsn"]) > > cur = con.cursor() > cur.execute(sql,args) > rs = cur.fetchall() > > but it doesn't work in the sense that doesn't return anything > > If i use the statment without variable > > SELECT field1,field2,field3 > FROM my_table > WHERE field_3 IN ('CNI','CNP') > > the query works > > what is wrong? You only have a single placeholder variable, so your statement is equivalent to SELECT field1,field2,field3 FROM my_table WHERE field_3 IN ('CNI,CNP') Presumably 'CNI,CNP' is not a valid value for field_3, thus your query finds no records. > suggestions? To verify that you have the correct syntax, try it with a single value first: my_dict = {'location':"X", 'oracle_user':'user', 'oracle_password':'pass', 'dsn':'dsn', 'mailto':'some...@somewhere.org', 'codes':"CNI"} It that produces some of the records you want, then the question is really: can you somehow pass a list of values via a single placeholder variable? I'm, not a cx_Oracle expert, but I think the answer is "no". If you want to pass exactly two values, then the work-around would be to pass them in separate variables: my_dict = {'location':"X", 'oracle_user':'user', 'oracle_password':'pass', 'dsn':'dsn', 'mailto':'some...@somewhere.org', 'code1':"CNI", 'code2':"CNP"} sql = """SELECT field1,field2,field3 FROM my_table WHERE field_3 IN (:arg_1, :arg_2)""" args = (my_dict['code1'],my_dict['code2']) If the number of codes can vary, you'll have to generate a query with the correct number of placholders in it. Mabye something like this (untested): my_dict = {'location':"X", 'oracle_user':'user', 'oracle_password':'pass', 'dsn':'dsn', 'mailto':'some...@somewhere.org', 'codes':"Ornhgvshy,vf,orggre,guna,htyl"} args = my_dict['codes'].split(",") placeholders = ','.join(":x%d" % i for i,_ in enumerate(args)) sql = """SELECT field1,field2,field3 FROM my_table WHERE field_3 IN (%s)""" % placeholders con = cx_Oracle.connect(my_dict["oracle_user"], my_dict["oracle_password"], my_dict["dsn"]) cur = con.cursor() cur.execute(sql,args) rs = cur.fetchall() Hope this helps, -- HansM -- http://mail.python.org/mailman/listinfo/python-list
Re: cx_Oracle clause IN using a variable
On Tue, Oct 16, 2012 at 7:41 AM, Beppe wrote: > Hi all, > I don't know if it is the correct place to set this question, however, The best place to ask questions about cx_Oracle would be the cx-oracle-users mailing list. > what is wrong? > suggestions? With the bind parameter you're only passing in a single string, so your query is effectively equivalent to: SELECT field1,field2,field3 FROM my_table WHERE field_3 IN ('CNI,CNP') You can't pass an actual list into a bind parameter the way that you would like. You need to use a separate parameter for each item in the list. This may mean constructing the query dynamically: in_vars = ','.join(':%d' % i for i in xrange(len(sequence_of_args))) sql = """ SELECT field1,field2,field3 FROM my_table WHERE field_3 IN (%s) """ % in_vars cursor.execute(sql, sequence_of_args) -- http://mail.python.org/mailman/listinfo/python-list
cx_Oracle clause IN using a variable
Hi all, I don't know if it is the correct place to set this question, however, I'm using cx_Oracle to query an Oracle database. I've a problem to use the IN clause with a variable. My statement is sql = "SELECT field1,field2,field3 FROM my_table WHERE field_3 IN (:arg_1)" where arg_1 is retrive by a dictionary that is build so my_dict = {'location':"X", 'oracle_user':'user', 'oracle_password':'pass', 'dsn':'dsn', 'mailto':'some...@somewhere.org', 'codes':"CNI,CNP"} args = (dict['codes'],) con = cx_Oracle.connect(my_dict["oracle_user"], my_dict["oracle_password"], my_dict["dsn"]) cur = con.cursor() cur.execute(sql,args) rs = cur.fetchall() but it doesn't work in the sense that doesn't return anything If i use the statment without variable SELECT field1,field2,field3 FROM my_table WHERE field_3 IN ('CNI','CNP') the query works what is wrong? suggestions? regards beppe -- http://mail.python.org/mailman/listinfo/python-list
Re: Python 3.1 cx_Oracle 5.0.2 "ImportError: DLL load failed: The specified module could not be found."
I'm having similar issue but everything seems to be installed in correct places. Loaded "CX_ORACLE.PYD" at address 0x6BD8. Successfully hooked module. Loaded "OCI.DLL" at address 0x1000. Successfully hooked module. Unloaded "CX_ORACLE.PYD" at address 0x6BD8. Unloaded "OCI.DLL" at address 0x1000. LoadLibraryExA("C:\JSTData\Python27\lib\site-packages\cx_Oracle.pyd", 0x, LOAD_WITH_ALTERED_SEARCH_PATH) returned NULL. Error: The specified procedure could not be found (127). Why is cx_Oracle not found after it has been hooked? Thanks to anyone that can shed some light on this. -- View this message in context: http://old.nabble.com/Python-3.1-cx_Oracle-5.0.2-%22ImportError%3A-DLL-load-failed%3A-The--specified-module-could-not-be-found.%22-tp26422168p30748079.html Sent from the Python - python-list mailing list archive at Nabble.com. -- http://mail.python.org/mailman/listinfo/python-list
Re: Error python + cx_Oracle :Oracle-Error-Message: ORA-01036: illegal variable name/number
On Tue, Jan 4, 2011 at 12:57 PM, Mauricio Martinez Garcia wrote: > > Hi, i need help with the next error: > > "ERR_PYTHON:Oracle-Error-Message: ORA-01036: illegal variable name/number", > i used the cx_Oracle module, and the error not is only Oracle Error. > > The error its for that python don't translate the query, with the variables > ":VARIABLE" when the VARIABLE is a number, this is th ecause of Oracle > error. You can only specify bind parameters that are used in the query. The dict you are passing in has 56 key/value pairs, but your query has only 55 variables. "TELEFONO_ICC" does not appear to be used anywhere in the query. Cheers, Ian -- http://mail.python.org/mailman/listinfo/python-list
Error python + cx_Oracle :Oracle-Error-Message: ORA-01036: illegal variable name/number
Hi, i need help with the next error: "ERR_PYTHON:Oracle-Error-Message: ORA-01036: illegal variable name/number", i used the cx_Oracle module, and the error not is only Oracle Error. The error its for that python don't translate the query, with the variables ":VARIABLE" when the VARIABLE is a number, this is th ecause of Oracle error. The template is: updateQUERYBSCS var. The code : == class HilosPrepago(object): def __init__(self,numhilos,listadoSegmentos,archivoLOG,arregloSalida): global NHilos global LSegmnts global ALog global listadoSalida global path_sal global queue global loggingRegister global updateQUERYBSCS queue = Queue() listadoSalida = arregloSalida ALog = archivoLOG LSegmnts = listadoSegmentos NHilos = numhilos loggingRegister = log_register(ALog) updateQUERYBSCS = """UPDATE CONCIL_ICC SET CONTRATO_ICC = :CONTRATO_ICC, CONTRATO_ATS = :CONTRATO_BSCS, PLAN_ICC = :PLAN_ICC, PLAN_ATS = :PLAN_BSCS, IVA_ICC = :IVA_ICC, IVA_ATS = :IVA_BSCS, IVA_MAPEO = :IVA_MAPEO, PROFILE_ICC = :PROFILE_ICC, PROFILE_ATS = :PROFILE_BSCS, LEG_SOG_ICC = :LEG_SOG_ICC, LEG_SOG_ATS = :LEG_SOG_BSCS, LIC_SOG_ICC = :LIC_SOG_ICC, LIC_SOG_ATS = :LIC_SOG_BSCS, LEGLIG_SOG_ICC = :LEGLIC_SOG_ICC, LEGLIG_SOG_ATS = :LEGLIC_SOG_BSCS, LICN_SOG_ICC = :LICN_SOG_ICC, LICN_SOG_ATS = :LICN_SOG_BSCS, LDN_SOG_ICC = :LDN_SOG_ICC, LDN_SOG_ATS = :LDN_SOG_BSCS, REFILL_SOG_ICC = :REFILL_SOG_ICC, REFILL_SOG_ATS = :REFILL_SOG_BSCS, REFILL_PROM_ICC = :REFILL_PROM_ICC, REFILL_PROM_ATS = :REFILL_PROM_BSCS, DIA_RECARGA_ICC = :DIA_RECARGA_ICC, DIA_RECARGA_PROM_ICC = :DIA_RECARGA_PROM_ICC, DIA_RECARGA_ATS = :DIA_RECARGA_BSCS, CEL_IMSI_ICC = :CEL_IMSI_ICC, CEL_IMSI_ATS = :CEL_IMSI_BSCS, STATUS_ICC = :STATUS_ICC, STATUS_ATS = :STATUS_BSCS, ERROR_CONTRATO = to_number(:ERROR_CONTRATO), ERROR_PLAN = to_number(:ERROR_PLAN), ERROR_IVA_BSCS = to_number(:ERROR_IVA_BSCS), ERROR_IVA_ICC = to_number(:ERROR_IVA_ICC), ERROR_PROFILE = to_number(:ERROR_PROFILE), ERROR_LEGSOG = to_number(:ERROR_LEGSOG), ERROR_LICSOG = to_number(:ERROR_LICSOG), ERROR_LEGLICSOG = to_number(:ERROR_LEGLICSOG), ERROR_LICNSOG = to_number(:ERROR_LICNSOG), ERROR_LDNSOG = to_number(:ERROR_LDNSOG), ERROR_REFILLSOG = to_number(:ERROR_REFILLSOG), ERROR_REFILLPROMOCION = to_number(:ERROR_REFILLPROMOCION), ERROR_DIA_RECARGA = to_number(:ERROR_DIA_RECARGA), ERROR_DIA_RECARGAPROM = to_number(:ERROR_DIA_RECARGAPROM), ERROR_IMSI = to_number(:ERROR_IMSI), ERROR_STATUS = to_number(:ERROR_STATUS), ERROR_ENALTA = to_number(:ERROR_ENALTA), ERROR_ENELIMINACION = to_number(:ERROR_ENELIMINACION), PLANACTUALPROMO = :PLANACTUALPROMO, LLEVAPROMOCION = :LLEVAPROMOCION, DUPLICIDAD_DN = to_number(:DUPLICIDAD_DN), VALIDADO_PRODUCCION = :VALIDADO_PRODUCCION, CORREGIDO_EN_ALU = :CORREGIDO_EN_ALU, MENSAJE_CORRECCION = :MENSAJE_CORRECCION WHERE TELEFONO_ATS = :TELEFONO_BSCS """ #threading.Thread.__init__(self) def ejecutaHilo(self,lista,listaRegistrosOUT,archLog,hilo): from OracleConnections import OracleConnections print "Iniciando la ejecucion para el hilo... %s" % hilo listaUnica = lista.get() loggingRegister.registrarLog('
Re: Error installing cx_Oracle
On Wed, Sep 22, 2010 at 9:35 PM, AMC wrote: > Hi, > > >From last months I am using Python for making small but useful tools > for my team members. I am doing my development in windows machine and > running the program in Solaris machine where Python 2.6.2 is > installed. > > In one of my program file I had to use the module "cx_Oracle".For that > I have installed the module in my windows machine and done the > programming. But the finished program does not run in Solaris machine, > may be because "cx_Oracle" was not installed there.Following is the > error shown. > > r...@access1:/home/amohan/python/work/broadcast_report/bin$ ./ > runsql.py > Traceback (most recent call last): > File "./runsql.py", line 3, in > import cx_Oracle > ImportError: ld.so.1: isapython2.6: fatal: /opt/webstack/python/lib/ > python2.6/site-packages/cx_Oracle.so: wrong ELF class: ELFCLASS64 > r...@access1:/home/amohan/python/work/broadcast_report/bin$ > > I tried installing "cx_Oracle" with easy_install utility (I am not > strong in Solaris). While installing I am getting some error as shown > below. > > r...@access1:/$ easy_install cx_Oracle > Searching for cx-Oracle > Reading http://pypi.python.org/simple/cx_Oracle/ > Reading http://cx-oracle.sourceforge.net > Reading http://starship.python.net/crew/atuining > Best match: cx-Oracle 5.0.4 > Downloading > http://prdownloads.sourceforge.net/cx-oracle/cx_Oracle-5.0.4.tar.gz?download > Processing cx_Oracle-5.0.4.tar.gz > Running cx_Oracle-5.0.4/setup.py -q bdist_egg --dist-dir /tmp/ > easy_install-FkDQn5/cx_Oracle-5.0.4/egg-dist-tmp-Ogg94s > "SessionPool.c", line 200: warning: integer overflow detected: op "<<" > ld: fatal: library -lpython2.6: not found > ld: fatal: File processing errors. No output written to build/ > lib.solaris-2.10-i86pc-2.6-10g/cx_Oracle.so > error: Setup script exited with error: command '/opt/webstack/python/ > lib/python2.6/pycc' failed with exit status 1 > r...@access1:/$ > > I have searched for a solution everywhere and tried many things but > didn't work. Can somebody help me in this? Install the python development libraries. cheers James -- -- James Mills -- -- "Problems are solved by method" -- http://mail.python.org/mailman/listinfo/python-list
Error installing cx_Oracle
Hi, >From last months I am using Python for making small but useful tools for my team members. I am doing my development in windows machine and running the program in Solaris machine where Python 2.6.2 is installed. In one of my program file I had to use the module "cx_Oracle".For that I have installed the module in my windows machine and done the programming. But the finished program does not run in Solaris machine, may be because "cx_Oracle" was not installed there.Following is the error shown. r...@access1:/home/amohan/python/work/broadcast_report/bin$ ./ runsql.py Traceback (most recent call last): File "./runsql.py", line 3, in import cx_Oracle ImportError: ld.so.1: isapython2.6: fatal: /opt/webstack/python/lib/ python2.6/site-packages/cx_Oracle.so: wrong ELF class: ELFCLASS64 r...@access1:/home/amohan/python/work/broadcast_report/bin$ I tried installing "cx_Oracle" with easy_install utility (I am not strong in Solaris). While installing I am getting some error as shown below. r...@access1:/$ easy_install cx_Oracle Searching for cx-Oracle Reading http://pypi.python.org/simple/cx_Oracle/ Reading http://cx-oracle.sourceforge.net Reading http://starship.python.net/crew/atuining Best match: cx-Oracle 5.0.4 Downloading http://prdownloads.sourceforge.net/cx-oracle/cx_Oracle-5.0.4.tar.gz?download Processing cx_Oracle-5.0.4.tar.gz Running cx_Oracle-5.0.4/setup.py -q bdist_egg --dist-dir /tmp/ easy_install-FkDQn5/cx_Oracle-5.0.4/egg-dist-tmp-Ogg94s "SessionPool.c", line 200: warning: integer overflow detected: op "<<" ld: fatal: library -lpython2.6: not found ld: fatal: File processing errors. No output written to build/ lib.solaris-2.10-i86pc-2.6-10g/cx_Oracle.so error: Setup script exited with error: command '/opt/webstack/python/ lib/python2.6/pycc' failed with exit status 1 r...@access1:/$ I have searched for a solution everywhere and tried many things but didn't work. Can somebody help me in this? Thanks, AMC. -- http://mail.python.org/mailman/listinfo/python-list
Re: Trouble importing cx_Oracle on HPUX
including libttsh11 fixed the problem. Thank you! Now I can get on with fixing everything that Python 3 broke... err changed. :) -- Cliff On Sat, Aug 28, 2010 at 11:20 AM, Alexander Gattin wrote: > Hello, > > On Sat, Aug 28, 2010 at 09:27:05AM -0400, Cliff > Martin wrote: > > Yes, our entire toolchain is 64 bit - a mix of > > stuff I have downloaded and built and some > > packages from HP (in the form of depot files) > > GCC was downloaded from HP, for example. > > I see. I bootstrapped from bundled cc, hence all > the problems. > > > Python -d did not generate any additional > > information, and so was not helpful (should this > > work?). > > Oops I was wrong about the python -d -- > correct option is -v of course... > > > Python -v did, however, and it came up with a > > number of unresolved symbols all seeming to be > > from libnnz11.so. I tried linking against all of > > the *.so files in ORACLE_HOME/lib, but I don't > > remember trying libttsh11 specifically. I will > > try it again on Monday. > > You're using Oracle 11 vs our v10 (we also have > v8, v9 and v11 in production, but not on this > HP-UX server), but I think the problem with the > libnnz is the same: Oracle doesn't put correct > shared library dependencies into the libnnzXX.so > dynamic section header (it should list > libttshXX.so as NEEDED but apperently doesn't). > > Probably their distribution for Solaris is better, > I didn't check (I'll ask our DBAs on Monday). > > -- > With best regards, > xrgtn > -- http://mail.python.org/mailman/listinfo/python-list
Re: rouble importing cx_Oracle on HPUX
Hello, On Sat, Aug 28, 2010 at 09:27:05AM -0400, Cliff Martin wrote: > Yes, our entire toolchain is 64 bit - a mix of > stuff I have downloaded and built and some > packages from HP (in the form of depot files) > GCC was downloaded from HP, for example. I see. I bootstrapped from bundled cc, hence all the problems. > Python -d did not generate any additional > information, and so was not helpful (should this > work?). Oops I was wrong about the python -d -- correct option is -v of course... > Python -v did, however, and it came up with a > number of unresolved symbols all seeming to be > from libnnz11.so. I tried linking against all of > the *.so files in ORACLE_HOME/lib, but I don't > remember trying libttsh11 specifically. I will > try it again on Monday. You're using Oracle 11 vs our v10 (we also have v8, v9 and v11 in production, but not on this HP-UX server), but I think the problem with the libnnz is the same: Oracle doesn't put correct shared library dependencies into the libnnzXX.so dynamic section header (it should list libttshXX.so as NEEDED but apperently doesn't). Probably their distribution for Solaris is better, I didn't check (I'll ask our DBAs on Monday). -- With best regards, xrgtn -- http://mail.python.org/mailman/listinfo/python-list
Re: Trouble importing cx_Oracle on HPUX
Hi, thank you for getting back to me. Yes, our entire toolchain is 64 bit - a mix of stuff I have downloaded and built and some packages from HP (in the form of depot files) GCC was downloaded from HP, for example. I had to manually add -mlp64 to the CC and CXX lines in the Python Makefile to get it to build 64 bit Python 3. I also had to define PATH_MAX in about 5 files, because it was not resolving it, and it was just easier to add it than to spend more time trying to make it work. I hate HP-UX, BTW. Python -d did not generate any additional information, and so was not helpful (should this work?). Python -v did, however, and it came up with a number of unresolved symbols all seeming to be from libnnz11.so. I tried linking against all of the *.so files in ORACLE_HOME/lib, but I don't remember trying libttsh11 specifically. I will try it again on Monday. -- Cliff On Sat, Aug 28, 2010 at 9:11 AM, Alexander Gattin wrote: > Hello, > > On Thu, Aug 26, 2010 at 08:08:42PM -0700, Cliff > Martin wrote: > > I have just gotten done building Python 3.1.2 on > > HPUX 11.31 Itanium (IA64) using gcc 4.4.3, and > > have tried building cx_Oracle to go with it. The > > build succeeds, but test and importing does not. > > I have tried building Python with threads and > > without. The only exotic thing I do with the > > configure for python is to supply -mlp64, which > > BTW, did you build all GNU toolchain in 64 bit > mode? I made some tries to get 64bit python etc > but stubmled over compilation errors and didn't > get enough free time to finish the effort. > > > makes it a 64 bit build. Python 3 appears to > > work just fine, and cx_Oracle has worked on this > > same architecture in the past with Python 2.6.5. > > did you try to start python -d and > >>> import cx_Oracle? > > It may reveal that some symbols in libnnz10 aren't > resolvable. If this is the case, try linking with > bith libttsh10 and libnnz10: > > .../cx_Oracle-x.y.z $ LDFLAGS="-lttsh10" python setup.py install > > Alternatively, try linking against "static" > version of libclntsh10 -- libclntst10. > > -- > With best regards, > xrgtn > -- http://mail.python.org/mailman/listinfo/python-list
Re: Trouble importing cx_Oracle on HPUX
Hello, On Thu, Aug 26, 2010 at 08:08:42PM -0700, Cliff Martin wrote: > I have just gotten done building Python 3.1.2 on > HPUX 11.31 Itanium (IA64) using gcc 4.4.3, and > have tried building cx_Oracle to go with it. The > build succeeds, but test and importing does not. > I have tried building Python with threads and > without. The only exotic thing I do with the > configure for python is to supply -mlp64, which BTW, did you build all GNU toolchain in 64 bit mode? I made some tries to get 64bit python etc but stubmled over compilation errors and didn't get enough free time to finish the effort. > makes it a 64 bit build. Python 3 appears to > work just fine, and cx_Oracle has worked on this > same architecture in the past with Python 2.6.5. did you try to start python -d and >>> import cx_Oracle? It may reveal that some symbols in libnnz10 aren't resolvable. If this is the case, try linking with bith libttsh10 and libnnz10: .../cx_Oracle-x.y.z $ LDFLAGS="-lttsh10" python setup.py install Alternatively, try linking against "static" version of libclntsh10 -- libclntst10. -- With best regards, xrgtn -- http://mail.python.org/mailman/listinfo/python-list
Trouble importing cx_Oracle on HPUX
I have just gotten done building Python 3.1.2 on HPUX 11.31 Itanium (IA64) using gcc 4.4.3, and have tried building cx_Oracle to go with it. The build succeeds, but test and importing does not. I have tried building Python with threads and without. The only exotic thing I do with the configure for python is to supply -mlp64, which makes it a 64 bit build. Python 3 appears to work just fine, and cx_Oracle has worked on this same architecture in the past with Python 2.6.5. Help! I would really like to use Python 3, but Oracle support is a requirement. Everything I've read indicates it should work, but there is not a lot of people doing this or posting notes about their install problems or successes on HP-UX. Cliff -- http://mail.python.org/mailman/listinfo/python-list
Re: cx_Oracle 5.0.4 + Python 3.1.2 + Oracle Instant Client 10.2.04; DLL Load failed on import (Win/NT)
tormod wrote: On Aug 12, 12:30 pm, Alexander Gattin wrote: Does Windows have anything like LD_LIBRARY_PATH/SHLIB_PATH? Yes and no. Windows uses PATH both for finding execuables and for finding DLLs. So if there's a DLL Windows cannot find, you need to add the folder containing that DLL to your PATH variable. No, isn't that only if I have an actual Oracle client installed (not the instant client)? Whether you use the instant client or an actual Oracle client is not the issue. You may or may not need LD_LIBRARY_PATH either way. When you import cx_Oracle on Linux, it loads a file named cx_Oracle.so which in turn loads two files named libclntsh.so and libnnz10.so. These two files are part of the Oracle client installation. The dynamic loader has a list of directories where it tries to find these files; if they aren't there, then the import of cx_Oracle will fail. In that case, you need to set LD_LIBRARY_PATH to the directory containing them (or talk your sysadmin into adding this directory to the default path. He'd do that by adding the directory to /etc/ld.so.conf and running ldconfig). Hope this helps, -- HansM -- http://mail.python.org/mailman/listinfo/python-list
Re: cx_Oracle 5.0.4 + Python 3.1.2 + Oracle Instant Client 10.2.04; DLL Load failed on import (Win/NT)
On Aug 12, 12:30 pm, Alexander Gattin wrote: > Does Windows have anything like > LD_LIBRARY_PATH/SHLIB_PATH? No, isn't that only if I have an actual Oracle client installed (not the instant client)? But great tip, wasn't exactly the solution, but your question triggered me to check the Windows environment variables*. I included env. variable name PATH and the value ;C:\Python31;C:\MinGW\bin;C:\TEMP \ORAIC10\bin. Only thing I set manually in the command prompt was: SET ORACLE_HOME=C: \TEMP\ORAIC10 , before the build. *=I checked the setup.py script which says: userOracleHome = os.environ.get("ORACLE_HOME") Checking in Visual Basic::environ("path") only included info from the Windows env. variables, and not the ones I set via the command prompt with PATH=%PATH%;... so I reckon if it applies to VB it also does for os.environ I can now import without errors! Thanks Alexander for putting me in the right direction. -- http://mail.python.org/mailman/listinfo/python-list
Re: cx_Oracle 5.0.4 + Python 3.1.2 + Oracle Instant Client 10.2.04; DLL Load failed on import (Win/NT)
Hello, On Wed, Aug 11, 2010 at 02:29:24PM -0700, tormod wrote: > I've tried countless times to build & install cx_Oracle on Python > 3.1.2, and failed every time, so I'd like to ask someone for help. ... > I've opened the cx_Oracle.pyd with Dependency Walker (http:// > www.dependencywalker.com/) and DW reports it can't find: OCI.DLL, > PYTHON31.DLL, MSVCR90.DLL (why?) Does Windows have anything like LD_LIBRARY_PATH/SHLIB_PATH? > Appreciate any help, even wildshots and 2 cents are welcome - I'll try > everything. please try starting python -d (--debug IIRC) and then loading cx_Oracle manually (>>> import cx_Oracle). Usually it succeeds (sic!) but outputs a lot of symbol errors. // Problem with Oracle libs -- With best regards, xrgtn (+380501102966/+380636177128/xr...@jabber.kiev.ua) -- http://mail.python.org/mailman/listinfo/python-list
cx_Oracle 5.0.4 + Python 3.1.2 + Oracle Instant Client 10.2.04; DLL Load failed on import (Win/NT)
Hi, I've tried countless times to build & install cx_Oracle on Python 3.1.2, and failed every time, so I'd like to ask someone for help. I've included step-by-step (literally) description of my last try, with so much detail I could, to help clarify things. I can build without errors. I can install without errors. I fail to import. I'm using an NT box (os:Win7) Downloaded Oracle Instant Client 10.2.0.4 instantclient-basic-win32-10.2.0.4.zip --> extracted to C:\TEMP \ORAIC10\bin instantclient-sdk-win32-10.2.0.4.zip --> extracted to C:\TEMP \ORAIC10\sdk Downloaded source code(cx_Oracle-5.0.4.tar.gz) from http://cx-oracle.sourceforge.net/ --> extracted to C:\TEMP\Python\cx_Oracle Installed Python 3.1.2 --> C:\Python31 Installed MinGW 5.1.6 --> C:\MinGW Startet command prompt: cd C:\TEMP\Python\cx_Oracle PATH=%PATH%;C:\Python31;C:\MinGW\bin;C:\TEMP\ORAIC10\bin SET ORACLE_HOME=C:\TEMP\ORAIC10 SET LD_LIBRARY_PATH=C:\TEMP\ORAIC10\sdk\lib python setup.py build -c mingw32 python setup.py install start python: Python 3.1.2 (r312:79149, Mar 21 2010, 00:41:52) [MSC v.1500 32 bit (Intel)] on win32 Type "help", "copyright", "credits" or "license" for more information. >>> import cx_Oracle Traceback (most recent call last): File "", line 1, in ImportError: DLL load failed: The specified module could not be found. running build running build_ext building 'cx_Oracle' extension creating build creating build\temp.win32-3.1-10g creating build\temp.win32-3.1-10g\Release C:\mingw\bin\gcc.exe -mno-cygwin -mdll -O -Wall -IC:\TEMP\ORAIC10\sdk \include -I C:\Python31\include -IC:\Python31\PC -c cx_Oracle.c -o build \temp.win32-3.1-10g\ Release\cx_oracle.o -DBUILD_VERSION=5.0.4 -DWITH_UNICODE writing build\temp.win32-3.1-10g\Release\cx_Oracle.def creating build\lib.win32-3.1-10g C:\mingw\bin\gcc.exe -mno-cygwin -shared -s build\temp.win32-3.1-10g \Release\cx_ oracle.o build\temp.win32-3.1-10g\Release\cx_Oracle.def -LC:\TEMP \ORAIC10\bin -L C:\TEMP\ORAIC10 -LC:\TEMP\ORAIC10\oci\lib\msvc -LC:\TEMP\ORAIC10\sdk \lib\msvc -L C:\Python31\libs -LC:\Python31\PCbuild -loci -lpython31 -lmsvcr90 -o build\lib.w in32-3.1-10g\cx_Oracle.pyd running install running build running build_ext running install_lib copying build\lib.win32-3.1-10g\cx_Oracle.pyd -> C:\Python31\Lib\site- packages running install_data running install_egg_info Removing C:\Python31\Lib\site-packages\cx_Oracle-5.0.4-py3.1.egg-info Writing C:\Python31\Lib\site-packages\cx_Oracle-5.0.4-py3.1.egg-info I've opened the cx_Oracle.pyd with Dependency Walker (http:// www.dependencywalker.com/) and DW reports it can't find: OCI.DLL, PYTHON31.DLL, MSVCR90.DLL (why?) Appreciate any help, even wildshots and 2 cents are welcome - I'll try everything. Cheers, Tom -- http://mail.python.org/mailman/listinfo/python-list
Re: source install of python2.7 and rpm install of cx_Oracle collision
Hi Jim, Jim Qiu wrote: I make installed python 2.7 from source, and also installed the RPM version of cx_Oracle for python 2.7. But ldd tells me : #ldd cx_Oracle.so libpython2.7.so.1.0 => not found I find out that only libpython2.7.a generated when I install python2.7, who can tell me what I need to do ? I want a libpython2.7.so.1.0 generated when [...] Due to the fact that you have compiled python from source, the python library is not in the defaul library path. Due to that the lib can't be found. As a quick workourd you can extend the LD_LIBRARY_PATH variable with the path and check if cx_Orcacle get now the lib. Lets assume you "installed" python at /opt/Python2.7a, then you need to extend the variable in this way: export LD_LIBRARY_PATH="$LD_LIBRARY_PATH:/opt/Python2.7a/lib" afterwards do the ldd again and check if the lib could be found now. If not, you've got the wrong path. Now you need to perist the changed library path. For this, you need to be root: 1. echo "/opt/Python2.7a/lib" > /etc/ld.so.conf.d/Pathon2.7a.conf 2. refresh your shared library cache with "ldconfig" Now you OS knows the new library location. But this is not clean. As Daniel mention, you should try to get a rpm. Otherwise you may get in trouble, if you install a newer Python2.7 version and forget to maintain you library paths. Cheers, Nils -- http://mail.python.org/mailman/listinfo/python-list
Re: source install of python2.7 and rpm install of cx_Oracle collision
Hi Jim, Jim Qiu wrote: [...] I find out that only libpython2.7.a generated when I install python2.7, who can tell me what I need to do ? I want a libpython2.7.so.1.0 generated when I've didn't read your complete mail... In addition to the steps I've described in my other mail, you need to the "configure" script, that you like to have shared libraries. So you need to add --enable-shared to your configure call: ./configure --prefix=/opt/Python2.7a --enable-shared Now you got the shared libraries in the lib folder. Cheers, Nils -- http://mail.python.org/mailman/listinfo/python-list
Re: source install of python2.7 and rpm install of cx_Oracle collision
> I make installed python 2.7 from source, and also installed the RPM version > of cx_Oracle for python 2.7. > > But ldd tells me : > #ldd cx_Oracle.so > libpython2.7.so.1.0 => not found > > I find out that only libpython2.7.a generated when I install python2.7, who > can tell me what I need to do ? I want a libpython2.7.so.1.0 generated when > > > I install python. > > I am not familiar with GCC and .so .a stuff. In this case I'd recommend removing the source install of python 2.7, install it from rpm, followed by installing cx_Oracle from rpm. HTH, Daniel -- Psss, psss, put it down! - http://www.cafepress.com/putitdown -- http://mail.python.org/mailman/listinfo/python-list
source install of python2.7 and rpm install of cx_Oracle collision
Hi all, I make installed python 2.7 from source, and also installed the RPM version of cx_Oracle for python 2.7. But ldd tells me : #ldd cx_Oracle.so libpython2.7.so.1.0 => not found I find out that only libpython2.7.a generated when I install python2.7, who can tell me what I need to do ? I want a libpython2.7.so.1.0 generated when I install python. I am not familiar with GCC and .so .a stuff. Best regards, Jim -- http://mail.python.org/mailman/listinfo/python-list
Re: mod_python load cx_Oracle error
On Mon, 19 Jul 2010 09:12:20 -0700, li wang wrote: > It's quite weird when I import cx_Oracle in python interactive shell, it > works perfectly. > but when I import cx_Oracle in a *,py script, handled by > mod_python.publisher, it keep reportint : > > ImportError: libclntsh.so.10.1: cannot open shared object file: No such > file or directory > > Can I anyone have a clue what's the matter, any help would be > appreciated! That's an Oracle error, it means that you didn't set and export LD_LIBRARY_PATH like this: export LD_LIBRARY_PATH=$ORACLE_HOME/lib This is how it normally works: mgog...@nycwxp2622:~$ python Python 2.6.4 (r264:75706, Dec 7 2009, 18:45:15) [GCC 4.4.1] on linux2 Type "help", "copyright", "credits" or "license" for more information. >>> import cx_Oracle >>> And this is what happens when I unset the shell variable: mgog...@nycwxp2622:~$ unset LD_LIBRARY_PATH mgog...@nycwxp2622:~$ python Python 2.6.4 (r264:75706, Dec 7 2009, 18:45:15) [GCC 4.4.1] on linux2 Type "help", "copyright", "credits" or "license" for more information. >>> >>> import cx_Oracle Traceback (most recent call last): File "", line 1, in ImportError: libclntsh.so.11.1: cannot open shared object file: No such file or directory >>> My cx_Oracle is linked against Oracle instant client 11.2 on Ubuntu. -- http://mgogala.byethost5.com -- http://mail.python.org/mailman/listinfo/python-list
mod_python load cx_Oracle error
It's quite weird when I import cx_Oracle in python interactive shell, it works perfectly. but when I import cx_Oracle in a *,py script, handled by mod_python.publisher, it keep reportint : ImportError: libclntsh.so.10.1: cannot open shared object file: No such file or directory Can I anyone have a clue what's the matter, any help would be appreciated! -- http://mail.python.org/mailman/listinfo/python-list
Re: Python 3.1 cx_Oracle 5.0.2 "ImportError: DLL load failed: The specified module could not be found."
On Nov 19, 6:57 pm, Neil Hodgson wrote: > André: > > > Apparently the error is caused by cx_Oracle not being able to find the > > Oracle client DLLs (oci.dll and others). The client home path and the > > client home path bin directory are in the PATH System Variable and > > oci.dll is there. > > Open the cx_Oracle extension with Dependency Walker > (http://www.dependencywalker.com/) to get a better idea about what the > problem is in more detail. > > Neil Thanks Neil. I used Dependency Walker and discovered cx_Oracle was looking for python30.dll. I seems to be a known issue with Python 3.1 http://bugs.python.org/issue4091. I'm now used Python 2.6.4 and the corresponding cx_Oracle version with no problems. Thanks for the help -- http://mail.python.org/mailman/listinfo/python-list
Re: Python 3.1 cx_Oracle 5.0.2 "ImportError: DLL load failed: The specified module could not be found."
André: > Apparently the error is caused by cx_Oracle not being able to find the > Oracle client DLLs (oci.dll and others). The client home path and the > client home path bin directory are in the PATH System Variable and > oci.dll is there. Open the cx_Oracle extension with Dependency Walker (http://www.dependencywalker.com/) to get a better idea about what the problem is in more detail. Neil -- http://mail.python.org/mailman/listinfo/python-list
Python 3.1 cx_Oracle 5.0.2 "ImportError: DLL load failed: The specified module could not be found."
Hello, I'm trying to get Python 3.1 and cx_Oracle 5.02 (cx_Oracle-5.0.2-10g.win32-py3.0.msi) to connect to an Oracle 11.1.0.7.0 database via OraClient10g 10.2.0.3.0 with Pydev 1.5.1.1258496115 in Eclipse 20090920-1017 on Windows XP SP 3 v2002. The import cx_Oracle line appears as an unresolved import and when I run the application I get the following error to console: Traceback (most recent call last): File "FILEPATHHERE", line 1, in import cx_Oracle ImportError: DLL load failed: The specified module could not be found. Apparently the error is caused by cx_Oracle not being able to find the Oracle client DLLs (oci.dll and others). The client home path and the client home path bin directory are in the PATH System Variable and oci.dll is there. I tried getting the Oracle Instant Client (instantclient-basic- win32-11.1.0.7.0.zip from http://www.oracle.com/technology/software/tech/oci/instantclient/htdocs/winsoft.html) and installing it as directed. I added the instant client path to the PATH System Variable but that didn't work either. I have scoured the internet and have not found a solution. Please help! -- http://mail.python.org/mailman/listinfo/python-list
Problems with cx_Oracle and Oracle 11.1 on Windows
Hi, I am trying to use cx_Oracle and SQLAlchemy with Oracle 11gR1 (11.1) on Windows Vista 64 bit. When I import cx_Oracle, I get this error: >>> import cx_Oracle Traceback (most recent call last): File "", line 1, in ImportError: DLL load failed: %1 is not a valid Win32 application. I have a full install of Oracle (not an instant client). I know in UNIX, you have to set the LD_LIBRARY_PATH variable, but I am not sure if there is an equivalent in Windows. Has anyone else here run into this? Or, can anyone offer me suggestions on how to get this import to work correctly? Thank you so much for any help you can give me. -- Ron Reidy -- http://mail.python.org/mailman/listinfo/python-list
Re: cx_Oracle problem
On Sep 15, 9:45 am, Squid wrote: > It's time for another round of "stump-the-geek". (thats what we call > it in my office) > > If actual code is needed I can provide but lets start off small for > this one... > > I've got a Python script that uses cx_Oracle to access an Oracle DB. > running the script from command line runs perfect > running the script as a cron job produces "Unable to acquire Oracle > environment handle" in log. > > In both cases it is running as the same user, in the same environment. > > any ideas??? nevermind, I should have searched before posting... -- http://mail.python.org/mailman/listinfo/python-list
cx_Oracle problem
It's time for another round of "stump-the-geek". (thats what we call it in my office) If actual code is needed I can provide but lets start off small for this one... I've got a Python script that uses cx_Oracle to access an Oracle DB. running the script from command line runs perfect running the script as a cron job produces "Unable to acquire Oracle environment handle" in log. In both cases it is running as the same user, in the same environment. any ideas??? -- http://mail.python.org/mailman/listinfo/python-list
Re: Print value CLOB via cx_Oracle
On 28 Lip, 20:02, Vincent Vega wrote: > Hi, > > I call function in Oracle database use cx_Oracle. > In standard I define: > > db = cx_Oracle.connect(username, password, tnsentry) > cursor = db.cursor() > > I create variable 'y' (result function 'fun_name') and call function > 'fun_name': > > y = cursor.var(cx_Oracle.CLOB) > cursor.callfunc(fun_name, cx_Oracle.CLOB, y) > > When I print variable 'y' I receive: > > > > How can I get value variable 'y'??? > How read ??? > > Vincent Vega > > Note: > Off course y.read() does not work, cause 'y'is CLOB not LOB. > (see datatypes table in > site:http://www.oracle.com/technology/pub/articles/prez-python-queries.html) The solution is simple: print y.getvalue() (see: http://cx-oracle.sourceforge.net/html/variable.html) Thanks! :) Vincent Vega -- http://mail.python.org/mailman/listinfo/python-list
Print value CLOB via cx_Oracle
Hi, I call function in Oracle database use cx_Oracle. In standard I define: db = cx_Oracle.connect(username, password, tnsentry) cursor = db.cursor() I create variable 'y' (result function 'fun_name') and call function 'fun_name': y = cursor.var(cx_Oracle.CLOB) cursor.callfunc(fun_name, cx_Oracle.CLOB, y) When I print variable 'y' I receive: > How can I get value variable 'y'??? How read ??? Vincent Vega Note: Off course y.read() does not work, cause 'y'is CLOB not LOB. (see datatypes table in site: http://www.oracle.com/technology/pub/articles/prez-python-queries.html) -- http://mail.python.org/mailman/listinfo/python-list
Re: problem about cx_Oracle
On Jul 16, 12:18 pm, Vincent wrote: > hi, all: > > i am using cx_oracle now. > > i write code as below: > > def __getfields_by_tbname(self,tbname): > cursor = self.ora_db.cursor() > print tbname > sql = 'select * from %s where rownum <=2' % tbname > print sql > cursor = cursor.execute(sql) > return self.getfields(cursor) > > and i got a error, it's message is : > JRYZCFZB_X_ZQY > select * from JRYZCFZB_X_ZQY where rownum <=2 > Traceback (most recent call last): > File "", line 1, in > File "c:/vincent/wd/django/mysite/mysite\..\mysite\tools > \data_convert.py", line 107, in convert > self.convert_table(tbname) > File "c:/vincent/wd/django/mysite/mysite\..\mysite\tools > \data_convert.py", line 94, in convert_table > field_list = self.__getfields_by_tbname(tbname) > File "c:/vincent/wd/django/mysite/mysite\..\mysite\tools > \data_convert.py", line 38, in __getfields_by_tbname > cursor = cursor.execute(sql) > TypeError: expecting None or a string > > i'm sure the cursor instance is not None. > could anybody give me sussgestion? i will apreciate it. > > vincent i have the answer now. the variant sql is unicode. i neet to convert it to string. -- http://mail.python.org/mailman/listinfo/python-list
problem about cx_Oracle
hi, all: i am using cx_oracle now. i write code as below: def __getfields_by_tbname(self,tbname): cursor = self.ora_db.cursor() print tbname sql = 'select * from %s where rownum <=2' % tbname print sql cursor = cursor.execute(sql) return self.getfields(cursor) and i got a error, it's message is : JRYZCFZB_X_ZQY select * from JRYZCFZB_X_ZQY where rownum <=2 Traceback (most recent call last): File "", line 1, in File "c:/vincent/wd/django/mysite/mysite\..\mysite\tools \data_convert.py", line 107, in convert self.convert_table(tbname) File "c:/vincent/wd/django/mysite/mysite\..\mysite\tools \data_convert.py", line 94, in convert_table field_list = self.__getfields_by_tbname(tbname) File "c:/vincent/wd/django/mysite/mysite\..\mysite\tools \data_convert.py", line 38, in __getfields_by_tbname cursor = cursor.execute(sql) TypeError: expecting None or a string i'm sure the cursor instance is not None. could anybody give me sussgestion? i will apreciate it. vincent -- http://mail.python.org/mailman/listinfo/python-list
cx_oracle
Hello everyone, 1. I installed Oracle client on my linux x86_64 machine. 2. Set oracle home and LD_LIBRARY_PATH 3. Installed cx_oracle using rpm for python 2.5 -> It's installed and the following file exists: /usr/local/lib/python2.5/site-packages/cx_Oracle.so -> When I run python -c 'import cx_Oracle' it returns the following error: ImportError: /usr/local/lib/python2.5/site-packages/cx_Oracle.so: cannot open shared object file: No such file or directory path /usr/local/lib/python2.5/site-packages is included in sys.path list Any comment? -- http://mail.python.org/mailman/listinfo/python-list
Re: cx_Oracle - DLL load failed
cwurld wrote: Hi, I am having some trouble getting cx_Oracle to work. When I try to import cx_Oracle, I get the following error message: ImportError: DLL load failed: %1 is not a valid Win32 application. I am using Python 2.6 on WIndows. Oracle Client 10g. Any ideas? Thanks Hmm some time ago I used cx, but I remember that I sometimes forgot to install the oracle client libraries (eventually just packed the needed binaries in the same dir as cx). Maybe something along that route? -- mph -- http://mail.python.org/mailman/listinfo/python-list
cx_Oracle - DLL load failed
Hi, I am having some trouble getting cx_Oracle to work. When I try to import cx_Oracle, I get the following error message: ImportError: DLL load failed: %1 is not a valid Win32 application. I am using Python 2.6 on WIndows. Oracle Client 10g. Any ideas? Thanks -- http://mail.python.org/mailman/listinfo/python-list
Re: how to manage CLOB type with cx_oracle
En Tue, 03 Mar 2009 15:23:38 -0200, Loredana escribió: I try this code and it works: curs.execute(sqlstr) for rows in curs: for col in rows: try: print col.read() except: print col onother question? which is the best wey (fastest way) to discern LOB from other type? type()? Add a line like this in the code above: print type(col) -- Gabriel Genellina -- http://mail.python.org/mailman/listinfo/python-list
Re: how to manage CLOB type with cx_oracle
On Mar 3, 5:12 pm, "Gabriel Genellina" wrote: > En Tue, 03 Mar 2009 13:33:19 -0200, Loredana > escribió: > > > > > > > On Mar 3, 1:01 pm, Loredana wrote: > >> Hi, > > >> I need to read CLOB field type (it is long text) > > >> if I use this code: > > >> curs.execute(sqlstr) > >> rows['name_of_columns'] = name_of_columns > >> rows['data'] = curs.fetchall() > > >> it returns me this values: > > >> test = {'name_of_columns': ['FILENAME', 'CRONTIME', 'SHORT_TAIL', > >> 'LONG_TAIL'], 'data': [('dd','asdds','adadsa', >> 0x2a955bc230>')]} > > >> any ideas? > > >> Thanks > > >> Lory > > > Hi all, > > I success to read one row with the following code: > > > curs.execute(sqlstr) > > name_of_columns = [] > > for fieldDesc in curs.description: > > name_of_columns.append(fieldDesc[0]) > > for rows_ in curs.fetchone(): > > try: > > print rows_.read() > > except: > > print "except. ",rows_ > > > but if I try with fetchmany() it doesn't work > > any ideas? > > cx_Oracle implements DBAPI 2.0, then you should follow the general > guidelines in the specification:http://www.python.org/dev/peps/pep-0249/ > > LOBs are an extension to DBAPI -- see > http://cx-oracle.sourceforge.net/html/lob.htmland carefully read the > second note: > > """Note: Internally, Oracle uses LOB locators which are allocated based on > the cursor array size. Thus, it is important that the data in the LOB > object be manipulated before another internal fetch takes place. The > safest way to do this is to use the cursor as an iterator. In particular, > do not use the fetchall() method. The exception “LOB variable no longer > valid after subsequent fetch” will be raised if an attempt to access a LOB > variable after a subsequent fetch is detected.""" > > -- > Gabriel Genellina- Hide quoted text - > > - Show quoted text - ok... I try this code and it works: curs.execute(sqlstr) for rows in curs: for col in rows: try: print col.read() except: print col onother question? which is the best wey (fastest way) to discern LOB from other type? thanks Loredana -- http://mail.python.org/mailman/listinfo/python-list
Re: how to manage CLOB type with cx_oracle
En Tue, 03 Mar 2009 13:33:19 -0200, Loredana escribió: On Mar 3, 1:01 pm, Loredana wrote: Hi, I need to read CLOB field type (it is long text) if I use this code: curs.execute(sqlstr) rows['name_of_columns'] = name_of_columns rows['data'] = curs.fetchall() it returns me this values: test = {'name_of_columns': ['FILENAME', 'CRONTIME', 'SHORT_TAIL', 'LONG_TAIL'], 'data': [('dd','asdds','adadsa',')]} any ideas? Thanks Lory Hi all, I success to read one row with the following code: curs.execute(sqlstr) name_of_columns = [] for fieldDesc in curs.description: name_of_columns.append(fieldDesc[0]) for rows_ in curs.fetchone(): try: print rows_.read() except: print "except. ",rows_ but if I try with fetchmany() it doesn't work any ideas? cx_Oracle implements DBAPI 2.0, then you should follow the general guidelines in the specification: http://www.python.org/dev/peps/pep-0249/ LOBs are an extension to DBAPI -- see http://cx-oracle.sourceforge.net/html/lob.html and carefully read the second note: """Note: Internally, Oracle uses LOB locators which are allocated based on the cursor array size. Thus, it is important that the data in the LOB object be manipulated before another internal fetch takes place. The safest way to do this is to use the cursor as an iterator. In particular, do not use the fetchall() method. The exception “LOB variable no longer valid after subsequent fetch” will be raised if an attempt to access a LOB variable after a subsequent fetch is detected.""" -- Gabriel Genellina -- http://mail.python.org/mailman/listinfo/python-list
Re: how to manage CLOB type with cx_oracle
On Mar 3, 1:01 pm, Loredana wrote: > Hi, > > I need to read CLOB field type (it is long text) > > if I use this code: > > curs.execute(sqlstr) > rows['name_of_columns'] = name_of_columns > rows['data'] = curs.fetchall() > > it returns me this values: > > test = {'name_of_columns': ['FILENAME', 'CRONTIME', 'SHORT_TAIL', > 'LONG_TAIL'], 'data': [('dd','asdds','adadsa', 0x2a955bc230>')]} > > any ideas? > > Thanks > > Lory Hi all, I success to read one row with the following code: curs.execute(sqlstr) name_of_columns = [] for fieldDesc in curs.description: name_of_columns.append(fieldDesc[0]) for rows_ in curs.fetchone(): try: print rows_.read() except: print "except. ",rows_ but if I try with fetchmany() it doesn't work any ideas? thanks Loredana -- http://mail.python.org/mailman/listinfo/python-list
how to manage CLOB type with cx_oracle
Hi, I need to read CLOB field type (it is long text) if I use this code: curs.execute(sqlstr) rows['name_of_columns'] = name_of_columns rows['data'] = curs.fetchall() it returns me this values: test = {'name_of_columns': ['FILENAME', 'CRONTIME', 'SHORT_TAIL', 'LONG_TAIL'], 'data': [('dd','asdds','adadsa',')]} any ideas? Thanks Lory -- http://mail.python.org/mailman/listinfo/python-list
Re: Problem with environment variables and cx_Oracle
On Feb 24, 2009, at 4:34 PM, Brandon Taylor wrote: Here's my setup: OS X (10.5.6 - Intel), Oracle Instant Clinet 10_2, Python 2.6.1, Django trunk OS X is an important detail here. In my .bash_profile, I have ORACLE_HOME and LD_LIBRARY_PATH specified as: ORACLE_HOME="$HOME/Library/Oracle/instantclient_10_2" export ORACLE_HOME LD_LIBRARY_PATH=$ORACLE_HOME export LD_LIBRARY_PATH Shouldn't this be DYLD_LIBRARY_PATH for Mac? -- http://mail.python.org/mailman/listinfo/python-list
Problem with environment variables and cx_Oracle
Hello everyone, Here's my setup: OS X (10.5.6 - Intel), Oracle Instant Clinet 10_2, Python 2.6.1, Django trunk I have my Oracle instantclient folder at: /Users/bft228/Library/Oracle/ instantclient_10_2 In my .bash_profile, I have ORACLE_HOME and LD_LIBRARY_PATH specified as: ORACLE_HOME="$HOME/Library/Oracle/instantclient_10_2" export ORACLE_HOME LD_LIBRARY_PATH=$ORACLE_HOME export LD_LIBRARY_PATH When I try to compile cx_Oracle-4.4.1 or 5.0.1, I get an error stating that it cannot find an Oracle installation. setup.py will error here: # try to determine the Oracle home userOracleHome = os.environ.get("ORACLE_HOME") Now, here's where things get wierd... If I: echo $ORACLE_HOME = /Users/bft228/Library/Oracle/ instantclient_10_2 If I: python import os os.environ 'ORACLE_HOME': '/Users/bft228/Library/Oracle/ instantclient_10_2', 'LD_LIBRARY_PATH': '/Users/bft228/Library/Oracle/ instantclient_10_2' If I hard-code the userOracleHome, cx_Oracle will compile, but I'm getting errors wen attempting to connect to Oracle, like: cx_Oracle.InterfaceError: Unable to acquire Oracle environment handle I've been wrestling with this for quite some time. My Oracle person assures me that my user has appropriate permissions for the schema. My Oracle experience is pretty limited, but this seems like it's an issue with the environment on my Mac. Does anyone have any ideas? I would REALLY appreciate some insight. Kind regards, Brandon Taylor Senior Web Developer The University of Texas at Austin -- http://mail.python.org/mailman/listinfo/python-list
Re: cx_Oracle-5.0 Problem
On Feb 12, 9:31 am, redbaron wrote: > > ImportError: dlopen(/Library/Frameworks/Python.framework/Versions/2.6/ > > lib/python2.6/site-packages/cx_Oracle.so, 2): Symbol not found: > > ___divdi3 > > You didn't link cx_Oracle.so all libs which it use. run "ldd -r > cx_Oracle.so" and you'll have an idea about all missing symbols. The > names of missed symbols could give you an idea what else should > cx_Oracle.so should be linked with We are getting a "command not found" error for the ldd command in OS X 10.5.6 Please advise. -- http://mail.python.org/mailman/listinfo/python-list
Re: cx_Oracle-5.0 Problem
> ImportError: dlopen(/Library/Frameworks/Python.framework/Versions/2.6/ > lib/python2.6/site-packages/cx_Oracle.so, 2): Symbol not found: > ___divdi3 You didn't link cx_Oracle.so all libs which it use. run "ldd -r cx_Oracle.so" and you'll have an idea about all missing symbols. The names of missed symbols could give you an idea what else should cx_Oracle.so should be linked with -- http://mail.python.org/mailman/listinfo/python-list
cx_Oracle-5.0 Problem
Hello everyone, I'm Brandon Taylor, senior web developer with the University of Texas at Austin. We're using Python 2.6.1 and having a lot of difficulty getting the cx_Oracle-5.0 library to install on one of our MacBooks running OS X 10.5.6. We can get cx_Oracle to compile, but after running setup.py install and trying to import the library, we are getting an error: Traceback (most recent call last): File "", line 1, in ImportError: dlopen(/Library/Frameworks/Python.framework/Versions/2.6/ lib/python2.6/site-packages/cx_Oracle.so, 2): Symbol not found: ___divdi3 Referenced from: /Users/mas80/Library/Oracle/instantclient_10_2/ libclntsh.dylib.10.1 Expected in: flat namespace Can anyone shed some light on how we can solve this issue? It's a show stopper for us of we can not connect to Oracle from Python. Kind regards, Brandon Taylor -- http://mail.python.org/mailman/listinfo/python-list
Re: cx_Oracle issues
On Dec 18 2008, 10:34 am, huw_at1 wrote: > On Dec 16, 12:17 pm, huw_at1 wrote: > > > > > On Dec 15, 12:59 pm, "ron.re...@gmail.com" > > wrote: > > > > On Dec 15, 2:44 am, huw_at1 wrote: > > > > > On Dec 11, 5:34 pm, "ron.re...@gmail.com" wrote: > > > > > > On Dec 10, 9:48 am, huw_at1 wrote: > > > > > > > Hey all. When usingcx_Oracleto run a procedure like: > > > > > > > cursor.execute("select (obj.function(value)) from table where > > > > > > id=blah") > > > > > > > I am getting the following error: > > > > > > > ORA-06502: PL/SQL: numeric or value error: character string buffer > > > > > > too > > > > > > small ORA-06512: at line 1 > > > > > > > Looking at cursor.description I get: > > > > > > > [('(obj.function(value))', , 4000, 4000, 0, > > > > > > 0, 1)] > > > > > > > Any tips - i have never seen this error before but am guessing that > > > > > > the value being returned is too big for the buffer size set for the > > > > > > cursor. the procedure fetches data from a LOB. > > > > > > > Any suggestions/confirmations? > > > > > > > Many thanks > > > > > > This error is a problem with the PL/SQL, notcx_Oracle. You need to > > > > > debug obj.function to see what kind of data is being accessed and then > > > > > a data analysis of that data to understand why this error occurs. I > > > > > can tell you the function is most likely expecting characters from a > > > > > column that are numeric [0 .. 9] and is getting alpha characters. > > > > > > -- > > > > > Ron Reidy > > > > > Sr. Oracle DBA > > > > > Hi thanks for the responses. Unfortunately the procedure in question > > > > is from a third party vendor so I can't really debug it so I'd say I > > > > was fairly stumped. Just out of interest how do you increase the > > > > output buffer size withcx_Oracle? > > > > > Many thanks- Hide quoted text - > > > > > - Show quoted text - > > > > Hi, > > > > Sure you can. You can see the PL/SQL source from the ditionary view > > > ALL_SOURCE: > > > select text from all_source where name = 'NAME_OF_FUNCTION'; > > > > From there, reverse engineeer which table(s) and column(s) are being > > > accesses and do the data analysis. > > > > -- > > > Ron Reidy > > > Hi all, > > > So I tried Rons query but unfortunately I got 0 records returned. > > However I can confirm that running the select query from a client does > > indeed generate the same error. Is there anything else I could try? > > Otherwise I'll just get in touch with the vendor I guess. > > Hi again. A further update to theseissuesis that I found some java > executable which seemed to execute the SQL query without hitch. My > Java isn't great but from what I could make out it seems that the > query is broken down from: > > select (obj.function(value)) from table where id=blah > > to: > > select value from table where id=blah > > obj.function(value) > > So make two queries. In the first retrieve the BLOB (value) and store > it in a java.sql.blob object. Then pass this back in to the stored > procedure. I'm a bit puzzled as to why this way would work over just > performing the straight select statement. Culd it be the jdbc > connector handles BLOBs better? Anyway I was wondering if I could > implement something similar usingcx_Oracle. however I am a bit stuck > on how to pass a BLOB in to the second query - specifically: > > cursor.execute(obj.function(value)) > > where value is the BLOB. I get an error: > > cx_Oracle.NotSupportedError: Variable_TypeByValue(): unhandled data > typecx_Oracle.LOB > > So I wonder if I need to set something for the input type but I do not > know how to do this. > > Any suggestions? > > Many thanks again. Hi there. Any suggestions? I'm still a bit stuck on this one? Cheers -- http://mail.python.org/mailman/listinfo/python-list
Re: cx_Oracle issues
On Dec 16, 12:17 pm, huw_at1 wrote: > On Dec 15, 12:59 pm, "ron.re...@gmail.com" > wrote: > > > > > On Dec 15, 2:44 am, huw_at1 wrote: > > > > On Dec 11, 5:34 pm, "ron.re...@gmail.com" wrote: > > > > > On Dec 10, 9:48 am, huw_at1 wrote: > > > > > > Hey all. When usingcx_Oracleto run a procedure like: > > > > > > cursor.execute("select (obj.function(value)) from table where > > > > > id=blah") > > > > > > I am getting the following error: > > > > > > ORA-06502: PL/SQL: numeric or value error: character string buffer too > > > > > small ORA-06512: at line 1 > > > > > > Looking at cursor.description I get: > > > > > > [('(obj.function(value))', , 4000, 4000, 0, > > > > > 0, 1)] > > > > > > Any tips - i have never seen this error before but am guessing that > > > > > the value being returned is too big for the buffer size set for the > > > > > cursor. the procedure fetches data from a LOB. > > > > > > Any suggestions/confirmations? > > > > > > Many thanks > > > > > This error is a problem with the PL/SQL, notcx_Oracle. You need to > > > > debug obj.function to see what kind of data is being accessed and then > > > > a data analysis of that data to understand why this error occurs. I > > > > can tell you the function is most likely expecting characters from a > > > > column that are numeric [0 .. 9] and is getting alpha characters. > > > > > -- > > > > Ron Reidy > > > > Sr. Oracle DBA > > > > Hi thanks for the responses. Unfortunately the procedure in question > > > is from a third party vendor so I can't really debug it so I'd say I > > > was fairly stumped. Just out of interest how do you increase the > > > output buffer size withcx_Oracle? > > > > Many thanks- Hide quoted text - > > > > - Show quoted text - > > > Hi, > > > Sure you can. You can see the PL/SQL source from the ditionary view > > ALL_SOURCE: > > select text from all_source where name = 'NAME_OF_FUNCTION'; > > > From there, reverse engineeer which table(s) and column(s) are being > > accesses and do the data analysis. > > > -- > > Ron Reidy > > Hi all, > > So I tried Rons query but unfortunately I got 0 records returned. > However I can confirm that running the select query from a client does > indeed generate the same error. Is there anything else I could try? > Otherwise I'll just get in touch with the vendor I guess. Hi again. A further update to these issues is that I found some java executable which seemed to execute the SQL query without hitch. My Java isn't great but from what I could make out it seems that the query is broken down from: select (obj.function(value)) from table where id=blah to: select value from table where id=blah obj.function(value) So make two queries. In the first retrieve the BLOB (value) and store it in a java.sql.blob object. Then pass this back in to the stored procedure. I'm a bit puzzled as to why this way would work over just performing the straight select statement. Culd it be the jdbc connector handles BLOBs better? Anyway I was wondering if I could implement something similar using cx_Oracle. however I am a bit stuck on how to pass a BLOB in to the second query - specifically: cursor.execute(obj.function(value)) where value is the BLOB. I get an error: cx_Oracle.NotSupportedError: Variable_TypeByValue(): unhandled data type cx_Oracle.LOB So I wonder if I need to set something for the input type but I do not know how to do this. Any suggestions? Many thanks again. -- http://mail.python.org/mailman/listinfo/python-list
Re: cx_Oracle issues
On Dec 15, 12:59 pm, "ron.re...@gmail.com" wrote: > On Dec 15, 2:44 am, huw_at1 wrote: > > > > > On Dec 11, 5:34 pm, "ron.re...@gmail.com" wrote: > > > > On Dec 10, 9:48 am, huw_at1 wrote: > > > > > Hey all. When usingcx_Oracleto run a procedure like: > > > > > cursor.execute("select (obj.function(value)) from table where > > > > id=blah") > > > > > I am getting the following error: > > > > > ORA-06502: PL/SQL: numeric or value error: character string buffer too > > > > small ORA-06512: at line 1 > > > > > Looking at cursor.description I get: > > > > > [('(obj.function(value))', , 4000, 4000, 0, > > > > 0, 1)] > > > > > Any tips - i have never seen this error before but am guessing that > > > > the value being returned is too big for the buffer size set for the > > > > cursor. the procedure fetches data from a LOB. > > > > > Any suggestions/confirmations? > > > > > Many thanks > > > > This error is a problem with the PL/SQL, notcx_Oracle. You need to > > > debug obj.function to see what kind of data is being accessed and then > > > a data analysis of that data to understand why this error occurs. I > > > can tell you the function is most likely expecting characters from a > > > column that are numeric [0 .. 9] and is getting alpha characters. > > > > -- > > > Ron Reidy > > > Sr. Oracle DBA > > > Hi thanks for the responses. Unfortunately the procedure in question > > is from a third party vendor so I can't really debug it so I'd say I > > was fairly stumped. Just out of interest how do you increase the > > output buffer size withcx_Oracle? > > > Many thanks- Hide quoted text - > > > - Show quoted text - > > Hi, > > Sure you can. You can see the PL/SQL source from the ditionary view > ALL_SOURCE: > select text from all_source where name = 'NAME_OF_FUNCTION'; > > From there, reverse engineeer which table(s) and column(s) are being > accesses and do the data analysis. > > -- > Ron Reidy Hi all, So I tried Rons query but unfortunately I got 0 records returned. However I can confirm that running the select query from a client does indeed generate the same error. Is there anything else I could try? Otherwise I'll just get in touch with the vendor I guess. -- http://mail.python.org/mailman/listinfo/python-list
Re: cx_Oracle issues
On Dec 15, 12:59 pm, "ron.re...@gmail.com" wrote: > On Dec 15, 2:44 am, huw_at1 wrote: > > > > > On Dec 11, 5:34 pm, "ron.re...@gmail.com" wrote: > > > > On Dec 10, 9:48 am, huw_at1 wrote: > > > > > Hey all. When usingcx_Oracleto run a procedure like: > > > > > cursor.execute("select (obj.function(value)) from table where > > > > id=blah") > > > > > I am getting the following error: > > > > > ORA-06502: PL/SQL: numeric or value error: character string buffer too > > > > small ORA-06512: at line 1 > > > > > Looking at cursor.description I get: > > > > > [('(obj.function(value))', , 4000, 4000, 0, > > > > 0, 1)] > > > > > Any tips - i have never seen this error before but am guessing that > > > > the value being returned is too big for the buffer size set for the > > > > cursor. the procedure fetches data from a LOB. > > > > > Any suggestions/confirmations? > > > > > Many thanks > > > > This error is a problem with the PL/SQL, notcx_Oracle. You need to > > > debug obj.function to see what kind of data is being accessed and then > > > a data analysis of that data to understand why this error occurs. I > > > can tell you the function is most likely expecting characters from a > > > column that are numeric [0 .. 9] and is getting alpha characters. > > > > -- > > > Ron Reidy > > > Sr. Oracle DBA > > > Hi thanks for the responses. Unfortunately the procedure in question > > is from a third party vendor so I can't really debug it so I'd say I > > was fairly stumped. Just out of interest how do you increase the > > output buffer size withcx_Oracle? > > > Many thanks- Hide quoted text - > > > - Show quoted text - > > Hi, > > Sure you can. You can see the PL/SQL source from the ditionary view > ALL_SOURCE: > select text from all_source where name = 'NAME_OF_FUNCTION'; > > From there, reverse engineeer which table(s) and column(s) are being > accesses and do the data analysis. > > -- > Ron Reidy Hi all, So I tried Rons query but unfortunately I got 0 records returned. However I can confirm that running the select query from a client does indeed generate the same error. Is there anything else I could try? Otherwise I'll just get in touch with the vendor I guess. -- http://mail.python.org/mailman/listinfo/python-list
Re: cx_Oracle issues
On Dec 15, 2:44 am, huw_at1 wrote: > On Dec 11, 5:34 pm, "ron.re...@gmail.com" wrote: > > > > > > > On Dec 10, 9:48 am, huw_at1 wrote: > > > > Hey all. When usingcx_Oracleto run a procedure like: > > > > cursor.execute("select (obj.function(value)) from table where > > > id=blah") > > > > I am getting the following error: > > > > ORA-06502: PL/SQL: numeric or value error: character string buffer too > > > small ORA-06512: at line 1 > > > > Looking at cursor.description I get: > > > > [('(obj.function(value))', , 4000, 4000, 0, > > > 0, 1)] > > > > Any tips - i have never seen this error before but am guessing that > > > the value being returned is too big for the buffer size set for the > > > cursor. the procedure fetches data from a LOB. > > > > Any suggestions/confirmations? > > > > Many thanks > > > This error is a problem with the PL/SQL, notcx_Oracle. You need to > > debug obj.function to see what kind of data is being accessed and then > > a data analysis of that data to understand why this error occurs. I > > can tell you the function is most likely expecting characters from a > > column that are numeric [0 .. 9] and is getting alpha characters. > > > -- > > Ron Reidy > > Sr. Oracle DBA > > Hi thanks for the responses. Unfortunately the procedure in question > is from a third party vendor so I can't really debug it so I'd say I > was fairly stumped. Just out of interest how do you increase the > output buffer size withcx_Oracle? > > Many thanks- Hide quoted text - > > - Show quoted text - Hi, Sure you can. You can see the PL/SQL source from the ditionary view ALL_SOURCE: select text from all_source where name = 'NAME_OF_FUNCTION'; >From there, reverse engineeer which table(s) and column(s) are being accesses and do the data analysis. -- Ron Reidy -- http://mail.python.org/mailman/listinfo/python-list
Re: cx_Oracle issues
huw_at1 writes: >> > ORA-06502: PL/SQL: numeric or value error: character string buffer too >> > small ORA-06512: at line 1 >> >> This error is a problem with the PL/SQL, not cx_Oracle. You need to >> debug obj.function to see what kind of data is being accessed and then >> a data analysis of that data to understand why this error occurs. I >> can tell you the function is most likely expecting characters from a >> column that are numeric [0 .. 9] and is getting alpha characters. > > Hi thanks for the responses. Unfortunately the procedure in question > is from a third party vendor so I can't really debug it so I'd say I > was fairly stumped. Just out of interest how do you increase the > output buffer size with cx_Oracle? Ron's point was that you cannot fix this problem on the side of cx_Oracle because the exception occurs before cx_Oracle ever sees the result, during the execution of PL/SQL code. This is easy to verify: simply run the function the same way in sqlplus. If the problem persists, it's a bug in the function (or in the way you're calling it, or setting up the data, etc.) and you should complain to your vendor, or somehow work around the problem. Otherwise it's a cx_Oracle related problem. -- http://mail.python.org/mailman/listinfo/python-list
Re: cx_Oracle issues
On Dec 11, 5:34 pm, "ron.re...@gmail.com" wrote: > On Dec 10, 9:48 am, huw_at1 wrote: > > > > > Hey all. When using cx_Oracle to run a procedure like: > > > cursor.execute("select (obj.function(value)) from table where > > id=blah") > > > I am getting the following error: > > > ORA-06502: PL/SQL: numeric or value error: character string buffer too > > small ORA-06512: at line 1 > > > Looking at cursor.description I get: > > > [('(obj.function(value))', , 4000, 4000, 0, > > 0, 1)] > > > Any tips - i have never seen this error before but am guessing that > > the value being returned is too big for the buffer size set for the > > cursor. the procedure fetches data from a LOB. > > > Any suggestions/confirmations? > > > Many thanks > > This error is a problem with the PL/SQL, not cx_Oracle. You need to > debug obj.function to see what kind of data is being accessed and then > a data analysis of that data to understand why this error occurs. I > can tell you the function is most likely expecting characters from a > column that are numeric [0 .. 9] and is getting alpha characters. > > -- > Ron Reidy > Sr. Oracle DBA Hi thanks for the responses. Unfortunately the procedure in question is from a third party vendor so I can't really debug it so I'd say I was fairly stumped. Just out of interest how do you increase the output buffer size with cx_Oracle? Many thanks -- http://mail.python.org/mailman/listinfo/python-list
Re: cx_Oracle issues
On Dec 10, 9:48 am, huw_at1 <[EMAIL PROTECTED]> wrote: > Hey all. When using cx_Oracle to run a procedure like: > > cursor.execute("select (obj.function(value)) from table where > id=blah") > > I am getting the following error: > > ORA-06502: PL/SQL: numeric or value error: character string buffer too > small ORA-06512: at line 1 > > Looking at cursor.description I get: > > [('(obj.function(value))', , 4000, 4000, 0, > 0, 1)] > > Any tips - i have never seen this error before but am guessing that > the value being returned is too big for the buffer size set for the > cursor. the procedure fetches data from a LOB. > > Any suggestions/confirmations? > > Many thanks This error is a problem with the PL/SQL, not cx_Oracle. You need to debug obj.function to see what kind of data is being accessed and then a data analysis of that data to understand why this error occurs. I can tell you the function is most likely expecting characters from a column that are numeric [0 .. 9] and is getting alpha characters. -- Ron Reidy Sr. Oracle DBA -- http://mail.python.org/mailman/listinfo/python-list
Re: cx_Oracle issues
On Thu, Dec 11, 2008 at 2:48 AM, huw_at1 <[EMAIL PROTECTED]> wrote: > Any tips - i have never seen this error before but am guessing that > the value being returned is too big for the buffer size set for the > cursor. the procedure fetches data from a LOB. > > Any suggestions/confirmations? Could you not increase the buffer size ? I think you can do this with cx_Oracle. cheers James -- -- -- "Problems are solved by method" -- http://mail.python.org/mailman/listinfo/python-list
cx_Oracle issues
Hey all. When using cx_Oracle to run a procedure like: cursor.execute("select (obj.function(value)) from table where id=blah") I am getting the following error: ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at line 1 Looking at cursor.description I get: [('(obj.function(value))', , 4000, 4000, 0, 0, 1)] Any tips - i have never seen this error before but am guessing that the value being returned is too big for the buffer size set for the cursor. the procedure fetches data from a LOB. Any suggestions/confirmations? Many thanks -- http://mail.python.org/mailman/listinfo/python-list
Re: cx_oracle and commands
gaius hammond Wrote: >Hi all, > > >I am having a very strange problem with cx_Oracle, has anyone >seen this kind of behavior before: > > > >ActivePython 2.5.2.2 (ActiveState Software Inc.) based on >Python 2.5.2 (r252:60911, Mar 27 2008, 18:53:24) [C] on sunos5 >Type "help", "copyright", "credits" or "license" for more information. >>>> from commands import getstatusoutput >>>> (status, output) = getstatusoutput('ls') >>>> status >0 >>>> from cx_Oracle import connect, SYSDBA >>>> db = connect(mode=SYSDBA) >>>> (status, output) = getstatusoutput('ls') >Traceback (most recent call last): > File "", line 1, in > File "/opt/ASpy25/lib/python2.5/commands.py", line 55, in >getstatusoutput >sts = pipe.close() >IOError: [Errno 10] No child processes > > > >Basically, once I have made a connection to Oracle I can no >longer use getstatusoutput(). This is a real problem as >I need to get a list of things to work on from Oracle then >spawn commands to process them... > > >Thanks, > There is no mode parameter to connect. use try-catch around db = connect(mode=SYSDBA) line to see what's going on. Edwin The information contained in this message and any attachment may be proprietary, confidential, and privileged or subject to the work product doctrine and thus protected from disclosure. If the reader of this message is not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify me immediately by replying to this message and deleting it and all copies and backups thereof. Thank you. -- http://mail.python.org/mailman/listinfo/python-list
cx_oracle and commands
Hi all, I am having a very strange problem with cx_Oracle, has anyone seen this kind of behavior before: ActivePython 2.5.2.2 (ActiveState Software Inc.) based on Python 2.5.2 (r252:60911, Mar 27 2008, 18:53:24) [C] on sunos5 Type "help", "copyright", "credits" or "license" for more information. >>> from commands import getstatusoutput >>> (status, output) = getstatusoutput('ls') >>> status 0 >>> from cx_Oracle import connect, SYSDBA >>> db = connect(mode=SYSDBA) >>> (status, output) = getstatusoutput('ls') Traceback (most recent call last): File "", line 1, in File "/opt/ASpy25/lib/python2.5/commands.py", line 55, in getstatusoutput sts = pipe.close() IOError: [Errno 10] No child processes Basically, once I have made a connection to Oracle I can no longer use getstatusoutput(). This is a real problem as I need to get a list of things to work on from Oracle then spawn commands to process them... Thanks, G -- [EMAIL PROTECTED] http://www.gaius.org.uk/ "First things first, but not necessarily in that order" -- The Doctor, "Doctor Who" - Visit Pipex Business: The homepage for UK Small Businesses Go to http://www.pipex.co.uk/business-services -- http://mail.python.org/mailman/listinfo/python-list
Re: Python cx_Oracle and Apache
On Aug 25, 1:53 pm, Cousin Stanley <[EMAIL PROTECTED]> wrote: > > > > > def generate_output(): > > print '' > > > generate_output() > > Raja > > You might try adding a Content-type header followed by > a blank line to your generate_output() function > > def generate_output() : > print 'Content-type: text/html' > print > print rest of your html > > -- > Stanley C. Kitching > Human Being > Phoenix, Arizona Hi, Thanks for the help. I actually got the solution. The problem was apache's timeout is set to 1second. I just changed that value and it worked. Thanks, Raja. -- http://mail.python.org/mailman/listinfo/python-list
Re: Python cx_Oracle and Apache
> > > def generate_output(): > print '' > > generate_output() Raja You might try adding a Content-type header followed by a blank line to your generate_output() function def generate_output() : print 'Content-type: text/html' print print rest of your html -- Stanley C. Kitching Human Being Phoenix, Arizona -- http://mail.python.org/mailman/listinfo/python-list
Re: Python cx_Oracle and Apache
Raja <[EMAIL PROTECTED]> writes: > The code is working fine on the command line but when executing it > on the browser i get the famouse "Premature end of script headers" > error. Look at the server's error log to see what the real error message is. You are probably missing an environment variable such as ORACLE_HOME. -- http://mail.python.org/mailman/listinfo/python-list
Python cx_Oracle and Apache
Hi, I am trying to write a cgi program which would be executed on browser with Apache server installed. The program would make a connection to a database using cx_Oracle module and display results on page. The code is working fine on the command line but when executing it on the browser i get the famouse "Premature end of script headers" error. I went ahead and found the problem occuring exactly when the code is asking for a connection to the database. I am attaching the program for reference. Let me know if there is anything I need to make changes to apache or any other way to overcome this issue. #!/apollo/bin/env -e TestDatabaseTraceDashboard python import cgi import sys, os #import cgitb;cgitb.enable() #os.environ[ 'ORACLE_HOME' ] = '/opt/app/oracle/product/10.2.0.2/ client' #if os.environ.has_key( 'LD_LIBRARY_PATH' ): # ld_library_path = os.environ[ 'LD_LIBRARY_PATH' ] # os.environ[ 'LD_LIBRARY_PATH' ] = '%s/lib:%s' % ( os.environ[ 'ORACLE_HOME' ], ld_library_path ) #os.environ['BRAZIL_CONFIG']="--root=/apollo/env/ TestDatabaseTraceDashboard --user=oracle" try: import cx_Oracle import cx_Oracle_Amazon except ImportError, e: #sys.stdout.write( "Import error: %s" % ( e ) ) #sys.argv[0] = '/opt/app/oracle/admin/scripts/monitor-manager' def test_dbconnect(): connection=cx_Oracle.Connection('suvidhak_admin','database','dcrno1') cur=connection.cursor() cur.execute('select shipment_id from pending_customer_shipments where rownum < 10') return cur cursor = test_dbconnect() def generate_output(): print '' generate_output() Thanks, Raja. -- http://mail.python.org/mailman/listinfo/python-list
Re: cx_Oracle execute procedure
Thanks Jerry and Diez. The first two replies I found answered my noob question. "Jerry Hill" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > On Wed, Mar 19, 2008 at 11:03 AM, Poppy <[EMAIL PROTECTED]> > wrote: >> I've been working on the code below and and executes silently, no >> complaints, however the end result should be a record in my table and >> it's >> not added. The procedure works with the passed credentials using SQLPlus >> or >> SQL Developer clients. However I'm not sure if I'm constructing my >> python >> code correctly to interact with Oracle. > ... >> connection.commit >> cur.close >> connection.close > > You have to actually call these methods: > connection.commit() > cur.close() > connection.close() > > Without the parentheses, you're just getting a reference to the > methods and immediately discarding them. > > -- > Jerry -- http://mail.python.org/mailman/listinfo/python-list
Re: cx_Oracle execute procedure
Poppy wrote: > I've been working on the code below and and executes silently, no > complaints, however the end result should be a record in my table and it's > not added. The procedure works with the passed credentials using SQLPlus > or SQL Developer clients. However I'm not sure if I'm constructing my > python code correctly to interact with Oracle. > > I've been basing the code below on what I found in this thread > http://www.thescripts.com/forum/thread33728.html . > > Zach- > > import cx_Oracle > > connection = cx_Oracle.connect("user/[EMAIL PROTECTED]") > > ## PROCEDURE rptmgr.rep_utils.CLONE_REPORT( p_ordernum varchar2,p_repnum > varchar2, p_prefix number) > > cur = connection.cursor() > > repParams = {} > repParams['arg1'] = "555" > repParams['arg2'] = "2" > repParams['arg3'] = "999" > > sqlStr = """BEGIN rptmgr.rep_utils.CLONE_REPORT( :arg1, :arg2, :arg3); > end;""" > > cur.execute(sqlStr, repParams) > > connection.commit > > cur.close > > connection.close You forgot to call the methods using the ()-operator. connection.commit() and so forth. Diez -- http://mail.python.org/mailman/listinfo/python-list
Re: cx_Oracle execute procedure
On Wed, Mar 19, 2008 at 11:03 AM, Poppy <[EMAIL PROTECTED]> wrote: > I've been working on the code below and and executes silently, no > complaints, however the end result should be a record in my table and it's > not added. The procedure works with the passed credentials using SQLPlus or > SQL Developer clients. However I'm not sure if I'm constructing my python > code correctly to interact with Oracle. ... > connection.commit > cur.close > connection.close You have to actually call these methods: connection.commit() cur.close() connection.close() Without the parentheses, you're just getting a reference to the methods and immediately discarding them. -- Jerry -- http://mail.python.org/mailman/listinfo/python-list
cx_Oracle execute procedure
I've been working on the code below and and executes silently, no complaints, however the end result should be a record in my table and it's not added. The procedure works with the passed credentials using SQLPlus or SQL Developer clients. However I'm not sure if I'm constructing my python code correctly to interact with Oracle. I've been basing the code below on what I found in this thread http://www.thescripts.com/forum/thread33728.html . Zach- import cx_Oracle connection = cx_Oracle.connect("user/[EMAIL PROTECTED]") ## PROCEDURE rptmgr.rep_utils.CLONE_REPORT( p_ordernum varchar2,p_repnum varchar2, p_prefix number) cur = connection.cursor() repParams = {} repParams['arg1'] = "555" repParams['arg2'] = "2" repParams['arg3'] = "999" sqlStr = """BEGIN rptmgr.rep_utils.CLONE_REPORT( :arg1, :arg2, :arg3); end;""" cur.execute(sqlStr, repParams) connection.commit cur.close connection.close -- http://mail.python.org/mailman/listinfo/python-list
Re: Insert to a clob field using cx_Oracle via a stored procedure
On Jan 2, 2:01 pm, [EMAIL PROTECTED] wrote: > Hello, > > Does anyone have experience using cx_Oracle to call a stored procedure > that inserts to a clob field? We have done this successfully via > straight SQL, but we are at a loss on how to do the same insert using > a stored procedure call. Any assistance would be much appreciated. > Thanks. > > Jason Found a solution to this - see the following thread if you interested. http://forums.oracle.com/forums/thread.jspa?forumID=376&threadID=601700 -- http://mail.python.org/mailman/listinfo/python-list
Insert to a clob field using cx_Oracle via a stored procedure
Hello, Does anyone have experience using cx_Oracle to call a stored procedure that inserts to a clob field? We have done this successfully via straight SQL, but we are at a loss on how to do the same insert using a stored procedure call. Any assistance would be much appreciated. Thanks. Jason -- http://mail.python.org/mailman/listinfo/python-list
Re: cx_Oracle + array parameter
On 3 Gru, 19:07, Ian Clark <[EMAIL PROTECTED]> wrote: > [EMAIL PROTECTED] wrote: > > Hello, > > > I'm trying to pass array as an argument into PL/SQL procedure. > > According to cursor manual (http://cx-oracle.sourceforge.net/html/ > > cursorobj.html) arrayvar() should be use to do it. I've created my > > array type in PL/SQL: > > > CREATE OR REPLACE TYPE cx_array_string is table of varchar2(200); > > > and simple procedure: > > > CREATE OR REPLACE PROCEDURE text(ret IN cx_array_string) IS > > BEGIN > > null; > > END text; > > > My python code: > > > p_array = curs.arrayvar(cx_Oracle.STRING, ['1','3']) > > curs.execute('BEGIN text( :1 ); end;', [p_array] ) > > > And it gives me back an error: > > cx_Oracle.DatabaseError: ORA-06550: line 1, column 7: > > PLS-00306: wrong number or types of arguments in call to 'TEXT' > > ORA-06550: line 1, column 7: > > PL/SQL: Statement ignored > > > It's the same when i try to use callproc() instead of execute(). I've > > searched whole internet with no luck. Could anyone please give me a > > working example python + pl/sql how to pass string array form py to > > oracle procedure, please. > > > Thank you! > > First off I've never used cxOracle or done any PL/SQL from python, but > it looks like you're passing a list of a list to text(). > > > p_array = curs.arrayvar(cx_Oracle.STRING, ['1','3']) > > curs.execute('BEGIN text( :1 ); end;', [p_array] ) > > p_array appears to be some sort of cxOracle array, but when you pass it > to curs.execute you wrap it in a new list: [p_array]. Try removing the > parens and see what happens. > > Ian Hello, Thanks for your reply. The secound parameter in curs.execute has to be list. I passed only one parameter so it looks bizzare but this is right. Anyway i know why it was wrong. Problem is in the cx_array_string. This type has to be INDEX BY BINARY_INTEGER I hope it will help somebody in the future. -- http://mail.python.org/mailman/listinfo/python-list
Re: cx_Oracle + array parameter
On 3 Gru, 19:07, Ian Clark <[EMAIL PROTECTED]> wrote: > [EMAIL PROTECTED] wrote: > > Hello, > > > I'm trying to pass array as an argument into PL/SQL procedure. > > According to cursor manual (http://cx-oracle.sourceforge.net/html/ > > cursorobj.html) arrayvar() should be use to do it. I've created my > > array type in PL/SQL: > > > CREATE OR REPLACE TYPE cx_array_string is table of varchar2(200); > > > and simple procedure: > > > CREATE OR REPLACE PROCEDURE text(ret IN cx_array_string) IS > > BEGIN > > null; > > END text; > > > My python code: > > > p_array = curs.arrayvar(cx_Oracle.STRING, ['1','3']) > > curs.execute('BEGIN text( :1 ); end;', [p_array] ) > > > And it gives me back an error: > > cx_Oracle.DatabaseError: ORA-06550: line 1, column 7: > > PLS-00306: wrong number or types of arguments in call to 'TEXT' > > ORA-06550: line 1, column 7: > > PL/SQL: Statement ignored > > > It's the same when i try to use callproc() instead of execute(). I've > > searched whole internet with no luck. Could anyone please give me a > > working example python + pl/sql how to pass string array form py to > > oracle procedure, please. > > > Thank you! > > First off I've never used cxOracle or done any PL/SQL from python, but > it looks like you're passing a list of a list to text(). > > > p_array = curs.arrayvar(cx_Oracle.STRING, ['1','3']) > > curs.execute('BEGIN text( :1 ); end;', [p_array] ) > > p_array appears to be some sort of cxOracle array, but when you pass it > to curs.execute you wrap it in a new list: [p_array]. Try removing the > parens and see what happens. > > Ian Hello, Thanks for your reply. The secound parameter in curs.execute have to be list. I passed only one parameter so it looks bizzare but this is right. Anyway i know why it was wrong. Problem is in the cx_array_string. This type has to be INDEX BY BINARY_INTEGER I hope it will help somebody in the future. -- http://mail.python.org/mailman/listinfo/python-list
Re: cx_Oracle + array parameter
[EMAIL PROTECTED] wrote: > Hello, > > I'm trying to pass array as an argument into PL/SQL procedure. > According to cursor manual (http://cx-oracle.sourceforge.net/html/ > cursorobj.html) arrayvar() should be use to do it. I've created my > array type in PL/SQL: > > CREATE OR REPLACE TYPE cx_array_string is table of varchar2(200); > > and simple procedure: > > CREATE OR REPLACE PROCEDURE text(ret IN cx_array_string) IS > BEGIN > null; > END text; > > My python code: > > p_array = curs.arrayvar(cx_Oracle.STRING, ['1','3']) > curs.execute('BEGIN text( :1 ); end;', [p_array] ) > > And it gives me back an error: > cx_Oracle.DatabaseError: ORA-06550: line 1, column 7: > PLS-00306: wrong number or types of arguments in call to 'TEXT' > ORA-06550: line 1, column 7: > PL/SQL: Statement ignored > > It's the same when i try to use callproc() instead of execute(). I've > searched whole internet with no luck. Could anyone please give me a > working example python + pl/sql how to pass string array form py to > oracle procedure, please. > > Thank you! First off I've never used cxOracle or done any PL/SQL from python, but it looks like you're passing a list of a list to text(). > p_array = curs.arrayvar(cx_Oracle.STRING, ['1','3']) > curs.execute('BEGIN text( :1 ); end;', [p_array] ) p_array appears to be some sort of cxOracle array, but when you pass it to curs.execute you wrap it in a new list: [p_array]. Try removing the parens and see what happens. Ian -- http://mail.python.org/mailman/listinfo/python-list
cx_Oracle + array parameter
Hello, I'm trying to pass array as an argument into PL/SQL procedure. According to cursor manual (http://cx-oracle.sourceforge.net/html/ cursorobj.html) arrayvar() should be use to do it. I've created my array type in PL/SQL: CREATE OR REPLACE TYPE cx_array_string is table of varchar2(200); and simple procedure: CREATE OR REPLACE PROCEDURE text(ret IN cx_array_string) IS BEGIN null; END text; My python code: p_array = curs.arrayvar(cx_Oracle.STRING, ['1','3']) curs.execute('BEGIN text( :1 ); end;', [p_array] ) And it gives me back an error: cx_Oracle.DatabaseError: ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'TEXT' ORA-06550: line 1, column 7: PL/SQL: Statement ignored It's the same when i try to use callproc() instead of execute(). I've searched whole internet with no luck. Could anyone please give me a working example python + pl/sql how to pass string array form py to oracle procedure, please. Thank you! -- http://mail.python.org/mailman/listinfo/python-list
Re: cx_Oracle: Non-ASCII characters handling with different versions
On Nov 14, 2:03 pm, Benjamin Hell <[EMAIL PROTECTED]> wrote: > It's solved: Because of a local full Oracle DB installation the > "working" box had the registry key > HKEY_LOCAL_SYSTEM\SOFTWARE\ORACLE\KEY_OraBD10g_home1\NLS_LANG set to > "AMERICAN_AMERICA.WE8MSWIN1252". A similar key was missing on the > other box. I added HKEY_LOCAL_SYSTEM\SOFTWARE\ORACLE\NLS_LANG with > the same value and now it works. You could use environment variable NLS_LANG to set it at run time with: os.environ["NLS_LANG"] = "AMERICAN_AMERICA.WE8MSWIN1252" import cx_Oracle ... This way you don't have to deal with registry on each box. Note that os.environ["NLS_LANG"] must be BEFORE import cx_Oracle. Domino -- http://mail.python.org/mailman/listinfo/python-list
Re: cx_Oracle: Non-ASCII characters handling with different versions
Benjamin Hell wrote: > On a computer with cx_Oracle version 4.1 (Python 2.4.3, Oracle 10g) > I can get query results consisting of strings including non-ASCII > characters, e.g. the code example below outputs "é 0xe9" (which is > the correct ISO-8859-1 hex code for "é"). On a newer installation > with cx_Oracle 4.3.3 (Python 2.5.1, connecting to the same Oracle > 10g server) these characters are interpreted as ASCII (output "e > 0x65"). It's solved: Because of a local full Oracle DB installation the "working" box had the registry key HKEY_LOCAL_SYSTEM\SOFTWARE\ORACLE\KEY_OraBD10g_home1\NLS_LANG set to "AMERICAN_AMERICA.WE8MSWIN1252". A similar key was missing on the other box. I added HKEY_LOCAL_SYSTEM\SOFTWARE\ORACLE\NLS_LANG with the same value and now it works. -- http://mail.python.org/mailman/listinfo/python-list
Re: cx_Oracle: Non-ASCII characters handling with different versions
En Tue, 13 Nov 2007 13:37:16 -0300, Benjamin Hell <[EMAIL PROTECTED]> escribió: > I have a problem with the cx_Oracle module (Oracle database access): > > On a computer with cx_Oracle version 4.1 (Python 2.4.3, Oracle 10g) > I can get query results consisting of strings including non-ASCII > characters, e.g. the code example below outputs "é 0xe9" (which is > the correct ISO-8859-1 hex code for "é"). On a newer installation > with cx_Oracle 4.3.3 (Python 2.5.1, connecting to the same Oracle > 10g server) these characters are interpreted as ASCII (output "e > 0x65"). The database encoding is the default (and it's the same DB > in both cases anyways); I have no NLS environment environment > variables set on either system (I'm running cygwin). The HISTORY > file of my more recent cx_Oracle names a few changes related to > character sets, but noone strikes me to be relevant for this case. I've never used cx_Oracle, but Python (2.4.4 or 2.5.1) + pyodbc + the Oracle ODBC driver works fine for me using non-ascii characters. -- Gabriel Genellina -- http://mail.python.org/mailman/listinfo/python-list
cx_Oracle: Non-ASCII characters handling with different versions
Hi! I have a problem with the cx_Oracle module (Oracle database access): On a computer with cx_Oracle version 4.1 (Python 2.4.3, Oracle 10g) I can get query results consisting of strings including non-ASCII characters, e.g. the code example below outputs "é 0xe9" (which is the correct ISO-8859-1 hex code for "é"). On a newer installation with cx_Oracle 4.3.3 (Python 2.5.1, connecting to the same Oracle 10g server) these characters are interpreted as ASCII (output "e 0x65"). The database encoding is the default (and it's the same DB in both cases anyways); I have no NLS environment environment variables set on either system (I'm running cygwin). The HISTORY file of my more recent cx_Oracle names a few changes related to character sets, but noone strikes me to be relevant for this case. There is non-ASCII data strings in a database, and I need to find a way to get it out correctly. Is anybody able to help me? Thanks! Ben #!/usr/bin/env python import cx_Oracle database = cx_Oracle.connect('login/[EMAIL PROTECTED]') curs = database.cursor() sql = """SELECT CHR(233) FROM DUAL""" curs.execute(sql) result = curs.fetchone()[0] print result, "0x%x" % ord(result) -- http://mail.python.org/mailman/listinfo/python-list
Re: cx_oracle
Hi Lukas, you will need a working oracle OCI client middleware before cx_oracle can talk to your database. The easiest nowadays is the so-called instant client, which must be available from the otn.oracle.com site (downloads might require a free registration). Try to get sql*plus working (the standard Oracle command-line client), and cx should then pose no problem. Cheers, Bernard. -- http://mail.python.org/mailman/listinfo/python-list
cx_oracle
Hi, I want to get an access to an oracle database. For that I found the module cx_oracle (http://www.python.net/crew/atuining/cx_Oracle/) and I have installed the version 'Windows Installer (Oracle 10g, Python 2.5)'. Now I tried to run the script in the readme file: --- import cx_Oracle # connect via SQL*Net string or by each segment in a separate argument #connection = cx_Oracle.connect("user/[EMAIL PROTECTED]") connection = cx_Oracle.connect("user", "password", "TNS") cursor = connection.cursor() cursor.arraysize = 50 cursor.execute(""" select Col1, Col2, Col3 from SomeTable where Col4 = :arg_1 and Col5 between :arg_2 and :arg_3""", arg_1 = "VALUE", arg_2 = 5, arg_3 = 15) for column_1, column_2, column_3 in cursor.fetchall(): print "Values:", column_1, column_2, column_3 --- And I got an error: --- Traceback (most recent call last): File "C:\Python25\cal_adjustment.py", line 1, in import cx_Oracle ImportError: DLL load failed: Das angegebene Modul wurde nicht gefunden. --- Do someone have an idea to solve the problem? A reinstall doesn't work. Cheers lukas -- http://mail.python.org/mailman/listinfo/python-list
Re: installing cx_Oracle.
Doug Phillips wrote: >> It also works the other way around, at least on the non-empty >> set of systems that contains my workstation. export simply >> marks the variable name for automatic export to the >> environment of subsequent commands. The value at that time >> doesn't matter. What matters is the value that the name has >> at the time the command is run: [snip] > Just tried on a FreeBSD 6.1 development box with stock /bin/sh and it > works there too... As far as I know, it has been like this since the introduction of the Bourne shell in (according to a web search) 1979. Charles -- http://mail.python.org/mailman/listinfo/python-list
RE: installing cx_Oracle.
> It also works the other way around, at least on the non-empty > set of systems that contains my workstation. export simply > marks the variable name for automatic export to the > environment of subsequent commands. The value at that time > doesn't matter. What matters is the value that the name has > at the time the command is run: > > [EMAIL PROTECTED] ~]$ export FOOD > [EMAIL PROTECTED] ~]$ FOOD=spam > [EMAIL PROTECTED] ~]$ python -c "import os; print os.environ['FOOD']" > spam > [EMAIL PROTECTED] ~]$ FOOD=eggs > [EMAIL PROTECTED] ~]$ python -c "import os; print os.environ['FOOD']" > eggs Just tried on a FreeBSD 6.1 development box with stock /bin/sh and it works there too... ... And I just learned something new! -Doug -- http://mail.python.org/mailman/listinfo/python-list
Re: installing cx_Oracle.
On Thu, 2007-05-24 at 16:15 +, Dennis Lee Bieber wrote: > On Thu, 24 May 2007 09:07:07 -0500, Carl K <[EMAIL PROTECTED]> > declaimed the following in comp.lang.python: > > > Getting closer, thanks Bill and Diez. > > > > $ export ORACLE_HOME > > $ ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/client > > Don't those lines need to be reversed? Set the variable in the > current shell, and /then/ export it? It also works the other way around, at least on the non-empty set of systems that contains my workstation. export simply marks the variable name for automatic export to the environment of subsequent commands. The value at that time doesn't matter. What matters is the value that the name has at the time the command is run: [EMAIL PROTECTED] ~]$ export FOOD [EMAIL PROTECTED] ~]$ FOOD=spam [EMAIL PROTECTED] ~]$ python -c "import os; print os.environ['FOOD']" spam [EMAIL PROTECTED] ~]$ FOOD=eggs [EMAIL PROTECTED] ~]$ python -c "import os; print os.environ['FOOD']" eggs Regards, -- Carsten Haese http://informixdb.sourceforge.net -- http://mail.python.org/mailman/listinfo/python-list
Re: installing cx_Oracle.
Dennis Lee Bieber wrote: > On Thu, 24 May 2007 09:07:07 -0500, Carl K <[EMAIL PROTECTED]> > declaimed the following in comp.lang.python: > >> Getting closer, thanks Bill and Diez. >> >> $ export ORACLE_HOME >> $ ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/client > > Don't those lines need to be reversed? Set the variable in the > current shell, and /then/ export it? whoops - I may have cut/pasted too fast. Carl K -- http://mail.python.org/mailman/listinfo/python-list
Re: installing cx_Oracle.
Dennis Lee Bieber wrote: > On Thu, 24 May 2007 09:07:07 -0500, Carl K <[EMAIL PROTECTED]> > declaimed the following in comp.lang.python: > >> Getting closer, thanks Bill and Diez. >> >> $ export ORACLE_HOME >> $ ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/client > > Don't those lines need to be reversed? Set the variable in the > current shell, and /then/ export it? Modern shells actually allow the single statement export ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/client regards Steve -- Steve Holden+1 571 484 6266 +1 800 494 3119 Holden Web LLC/Ltd http://www.holdenweb.com Skype: holdenweb http://del.icio.us/steve.holden -- Asciimercial - Get on the web: Blog, lens and tag your way to fame!! holdenweb.blogspot.comsquidoo.com/pythonology tagged items: del.icio.us/steve.holden/python All these services currently offer free registration! -- Thank You for Reading -- http://mail.python.org/mailman/listinfo/python-list