Python and Oracle 9i

[Andy Todd]

boblancaster at zxmail.com (Bob Lancaster) wrote in 
news:b470cb58.0202250816.7839c1c4 at posting.google.com:

> I am in a bit of a quandry. 
> I am writing a python module which converts some data from various
> python variables, lists, etc. into SQL, which in turn is inserted to
> or selected from a database.
> 
> I need to support three combinations.
> (1) PostgreSQL and Linux, for academic customers.  No serious problems
> here.  In fact, I have learned why folks love PostgreSQL so much.
> 
> (2) Oracle 9i and Linux.  I haven't tried this one yet, but it seems
> to be at least as easy as the third combination.
> 
> (3) Oracle 9i and Windows 2000, for industrial customers.  Trouble!
> I have tried two approaches.  
>   (a) DCOracle2:  builds, connects, but then does not work properly. 
> I see two possible sources of the problem.
> 
> The documentation for DCOracle2 says:
> 
> """Oracle installs the C headers to build OCI programs in
> $ORACLE_HOME/rdbms/demo, $ORACLE_HOME/network/public, and
> $ORACLE_HOME/plsql/public. If these directories do not have the proper
> include files, the build will not succeed. Oracle typically only
> installs these files when a complete server installation is
> performed."""
> 
>  
> Okay, under my ORACLE_HOME directory, I have rdbms/demo, which has
> lots of header files, I have plsql/public, which has a header file,
> but my ORACLE_HOME/network dircetory does not have a /public
> subdirectory.  I not sure why DCOracle2 would even build without this
> subdirectory.  Have the folks at Oracle, in their infinite wisdom,
> chosen to place the files elswhere so that I could copy into a
> /network/public directory? Or am I screwed?
> 
> The other possible problem is that I am using Python 2.1.1.  I rewrote
> the install.py to accept 2.1.1 in addition to 2.1, since the Python
> home page no longer lists 2.1 as a possible download.  (In fact, they
> only list 2.1.2 these days.) Would using 2.1.1 instead of 2.1 make
> that big a difference?
> 
> (b) I tried using cx_Oracle.  This seemed like the best solution,
> until I started doing more complicated stuff.  First, it sometimes
> crashes when I do a fetchall().  (Error message 50). I have looked at
> newsgroups, etc. and I saw that I am not the only one to ever have
> this problem.  Second, although I could insert and update
> TIMESTAMP_WITH_TIMEZONE and user-defined types, I could not retrieve
> them.  I checked the source code, and cx_Oracle is VERY limited in the
> types it handles.  I would hate to have to rewrite that module and
> recompile the whole thing, especially since I can't solve the
> fetchall() problem.
> 
> 
> It appears that I have three options at this point, none of which are
> very good.  All of them may require some substantial reworking of my
> database, alas.
> 
> I.  Somehow get DCOracle2 to work with Oracle 9i.  This seems to be my
> best bet, if I can figure out how.
> 
> II.  Figure out how to get around the fetchall() problem with
> cx_Oracle, and put all my user-defined types into VARCHARs, or
> seperate tables, or whatever.
> 
> III.  Use Jython and connect with the JDBC and a JDBC:ODBC bridge. 
> This seems to be the least satisfying, since I want to be able to put
> everything into a single package.
> 
> 
> Any advice would be most welcome.
> 
> Thanks,
> Bob
> 
> BTW, I have an exinct email listed, to avoid spam.  If you really need
> to contact me, try rlancasteratbruker-axsdotcom, converting the at and
> the dot to symbols.

Bob,

I think the DCOracle2 documentation is incorrect. All of the header and 
library files needed to compile OCI clients for Oracle should now (Oracle8i 
release 2 and above) be included in the %ORACLE_HOME%\oci directory on 
Windows. For information on which components are necessary to build OCI 
client modules have a look at the "Oracle Call Interface Getting Started 
Release x.x.x for Windows" manual provided with your database installation. 
Mine is for 8.1.6, ymmv. 

Something that has caught me out in the past is that I have performed a 
server install but forgot to include the Oracle client software. This then 
doesn't include the appropriate dlls which will result in run time errors 
as soon as you try to call them. If you do a 'full' install of Oracle on 
Windows this shouldn't be a problem, but if you have performed a 'custom' 
install you may have missed out the appropriate headers and dll files and 
that would definitely cause issues.

As far as I know you should have no problems using DCOracle2 with Python 
2.1.1 or 2.1.2. I'm running Python 2.1.2 as part of Zope on my Windows2000 
machine and I have successfully managed to install DCOracle2 into that 
environment. All I did was change line 14 of install.py from;

version = string.split(sys.version)[0]

to

version = string.split(sys.version)[0][0:3]

and everything was hunky dory. Hope this is of some help.

Regards,
Andy
-- 
Contents free posts a speciality