Python and Oracle 9i

[Bob Lancaster]

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.