There are a few ways to connect Zope to Oracle.
- DCOracle2 is a free (and Free) DBA for Zope, but it is somewhat under-maintained (no new releases since 2003) and does not always support the latest versions of Oracle. (For example, to compile it against the Oracle 10g client libraries, you need to patch the DCOracle2 source code as described here
- Commercial products using ODBC: the commercial mxODBC adapter allows Zope to talk to any database that supports ODBC. You would have to buy one mxODBC license per each Zope instance. If you are running Windows, that's all you need to buy: Oracle provides an ODBC driver and manager for Windows. If you are on Linux or Unix, you will need to install a (free) ODBC manager such as unixodbc or iodbc , as well as a (commercial) ODBC driver for Oracle, such as the one provided by easysoft. You need one Easysoft license per each machine.
- cx_Oracle provides a Free Software python DBA interface to Oracle, and it appears to be well supported. There are two possible implementations - ZXOracleDA? and ZcxOracleDA? - see below.
- ZXOracleDA? - cx_oracle DA for Zope, also available on sourceforge here: http://sourceforge.net/project/showfiles.php?group_id=155736
- I failed to find that package on both zope.org and sourceforge so quickly knocked together a simple DA for cx_Oracle. Its on zope.org as ZcxOracleDA?
DCOracle2 -- Thu, 02 Jun 2005 09:38:21 -0700 reply
My company uses DCOracle2 (in Zope and directly) in a mission-critical OLTP environment (several million transactions per day). It has its quirks, but it works well. For the last couple of months, our production environment has been running it on Solaris 10/x64 linked against the Oracle 10g client libraries (the databases themselves are still 9i), with no problems.
I can't comment about cx_oracle, but ODBC isn't really an option. The ODBC API is a lowest-common denominator approach that has significantly higher overhead than native Oracle OCI as used in DCOracle2/cx_oracle, and it also limits your ability to use Oracle extensions like bind variables, INSERT INTO ... RETURNING or multi-row queries using binding arrays, that are necessary for serious Oracle performance tuning. If you don't care about squeezing the last drop of performance out of Oracle, you probably don't need Oracle anyway, the open-source databases should meet your needs.
Fazal Majid (www.majid.info)
Zope DA, DCOracle2 and cx_Oracle --Dario Lopez-Kästen
We normally use DCOracle2 for our Oracle connectivity needs, however we have had some serious troubles with it on and off (we have a lot of concurrent users and heavy queries on top of that). We are still using it compiled for Oracle 8 client libraries, and have had some success with compiling it for Oracle 9; we have not tried Oracle 10 yet (thanks for the link, btw :).
As far as I understand it, there is no active development of DCOracle? 2 going on at all. Matt Kromer is no longer at Zope Corp, however he still answers specific questions about it whenever he has the opportunity. Chris Withers has contributed a lot of work to the ZopeDa? parts (python), however there are still some issues with DCOracle2 as a whole.
For our latest project, we have used cx_Oracle. This time we have not gone the ZopeDA? path, because we have built an application specific library that manages the connection with Oracle for us. This library is shared between our different interfaces to the application, at the moment a Plone-based Web GUI and a non-zope Command Line Interface.
The benefit I see of using cx_Oracle is that it is actively maintained and updated. The benefit of DCOracle2 is that it has a Zope DA interface, for those situations when you might need it.
Performance with cx_Oracle is much better than using a DCOracle2 DA ZSQL Queries. However, I do not believe that this is a result of significant performance differences between cx_Oracle and DCOracle2; instead I believe that not using the Zope DA layer, and in particular ZSQL Methods, gives general performance improvements. I have noticed similar performance boosts with DCO2 when used directly (I think this is because the Zope Security machinery has to examine every single cell of the results from a ZSQL query).
This is a potentially interesting topic for discussion: to use or not the Zope DA interfaces. Apart from performance reasons, I have encountered quite a few situations where Zope DA's integration with the Zope transaction mechanism is a problem; often there are reasons to why you want Web-application transactions managed separately from the Database layer transactions. When using DCO2 or cx_Oracle directly, without specific zope integration, you have much better control of the database transactions, at the cost of not having them automatically managed.
Dario Lopez-Kästen Chalmers University of Technology
SQLRelay? -- Tue, 07 Jun 2005 00:18:48 -0700 reply
There is another product that can be used to connect with Oracle database from Python: SQLRelay?. It has its own ZopeDA? interface and seems to work well, but I haven't tested it in production environment yet. Now I'm writing own simple product to be able to call oracle stored procedures from within Zope.
SQLrelay? has different handling of some data types than DCOracle? (eg. date is returned not as DateTime object but as string) but a lot of these things can be tuned up with nls_xxx oracle environmental variables or with small changes in SQLRelay? code (a bit dirty) eg. calling getNullsAsNone to have nulls as None not as empty strings.
Maciej Wisniowski
Good resource fo for installing DCOracle2 with 10g -- Thu, 29 Sep 2005 12:21:27 -0700 reply
Jet Wilda wrote some great instructions on installing DCOracle2 to work with Oracle 10g. Here is the link: http://www.zope.org/Members/jetfu/HowToInstallDcOracle2/howto_view
Good resource fo for installing DCOracle2 with 10g -- Thu, 29 Sep 2005 12:21:55 -0700 reply
Jet Wilda wrote some great instructions on installing DCOracle2 to work with Oracle 10g. Here is the link: http://www.zope.org/Members/jetfu/HowToInstallDcOracle2/howto_view
Good resource fo for installing DCOracle2 with 10g --dario, Fri, 12 May 2006 00:59:40 -0700 reply
Why do you need the PDO for DCOracle2?
/dario
I second the PDO question --MichaelBest?, Mon, 03 Jul 2006 11:25:43 -0700 reply
I never needed PDO to install DCOracle? before...
ZcxOracleDA? --andyhird, Fri, 17 Aug 2007 09:06:51 +0000 reply
I recently uploaded an early version of a Zope DA for cxOracle to
http://www.zope.org/Members/andyhird/ZcxOracleDA for people wanting to give it a go.
SQLRelay? works best --sandland, Thu, 11 Sep 2008 14:07:51 -0400 reply
We've tried mxODBC, DCOracle2 and SQLRelay? in our production environment, and SQLRelay? is the most stable of the three. It's also a pain to set up properly, but well worth the hassle.