Forum OpenACS Development: PostgreSQL and Oracle cross-db query support?

Posted by Andrew Piskorski on
Incidentally, for at least some users, OpenACS dropping Oracle support would be very much not trivial. To give one example, at work I have extensive dependencies on Oracle which have nothing at all to do with OpenACS. Yet, we use OpenACS to provide Intranet access and web UIs to a lot of that non-OpenACS data which lives in Oracle. Presumably that would become much more difficult if OpenACS dropped Oracle support... I don't know how common that scenario is, but surely I'm not the only such user.

Does anyone know how feasible it is for PostgreSQL and/or Oracle to query each other? In other words, if I need to do a query using some data from Oracle and some data from PostgreSQL, how could I do that (other than the obvious thing of having the client query both separately)?

Oracle has database links, but so far I've only ever used them to connect to other Oracle instances (which works pretty well). Oracle has "Transparent Gateway" support for various databases (Informix, Sybase, SQL Server, etc.), but AFAIK not for PostgreSQL.

PostgreSQL apparently added Two-Phase Commit support to the CVS Head back in June (woohoo!), and may eventually support the XA interface to it as well. PostgreSQL also has contrib/dblink, Oralink, and DBI-Link, but I'm not sure how well those really work. (Do they use the new two-phase commit stuff?)

Anyone know how well those really work, or have any further info on PostgreSQL to Oracle and Oracle to PostgreSQL dblink stuff?