Forum OpenACS Development: Connecting to a Oracle database

Collapse
Posted by Gary Roesler on
My system is setup and running on postgres 7.4, but I need to be able to connect to my clients oracle database to query some tables so I am trying to setup a new pool for the oracle connection. But I assume that I must have the oracle librarys on the system and the ora8.so. Is there a way to download the lib directory somewhere and the ora8.so, because the oracle database is on a separate machine? Any help in setting it up would be appreciated or if there is another way of connecting to the oracle database that would be great ie. (dblink_ora)

Gary

Collapse
Posted by Brian Fenton on
Hi Gary,

I think you will need to install the Oracle client software on the AOLserver machine. You can download this free from Oracle. But be warned, it's nearly a 0.5GB download!

Brian

Collapse
Posted by Brian Fenton on
Actually, I've just noticed this Oracle Instant Client. I've never seen this before - does anyone know if this would work with AOLserver? I imagine so, it claims to work with sql*plus.
http://www.oracle.com/technology/tech/oci/instantclient/index.html

Brian

Collapse
4: Oracle client libraries (response to 1)
Posted by Andrew Piskorski on
Gary, you are correct, you need to install the Oracle client libraries on any machine where you run AOLserver or any other Oracle client program. This also means you have to have the Listener configured correctly on the server, and the sqlnet.ora and tnsnames.ora files configured correctly on the client.

The Oracle client stuff is shipped with the RDBMS software, start up Oracle's Java installer the normal way, then just click on things to select only the client software. I recommend installing all the Oracle client stuff, not just the "Runtime" client. Easier to do that than find out later that your database driver works but you can't use sqlldr because it wasn't installed on the client, etc.

Also, Oracle tends to install all its software with overly restrictive file permissions. And at least on Windows, Oracle does not report file permissions problems correctly! So if you get a strange TNS failure when trying to connect to a remote database - especially if you are not logged on as the same user who installed the Oracle client sofware - check the permissions on the relevant files. For example, I have seen failures because I bizarrely did not even have read access to this file: "C:\oracle\ora92\network\ADMIN\tnsnames.ora"

(On Windows, to fix file permissions, you will, of course, need to turn off the moronic default "Use simple file sharing". It's in Windows Explorer; menu Tools, Folder Options, View tab; "Advanced Settings". But likely you're running Linux on the client side too, so you don't have to deal with that silliness.)

Collapse
Posted by Gary Roesler on
Does anyone know where I can download a copy of the oracle 8i client? Or will Oracle 10g client work with Oracle 8i?
Collapse
Posted by Brian Fenton on
Hi Gary,

The 10g client will work fine with 8i. You can download it here (or it'll be on the Oracle server CDs) http://www.oracle.com/technology/software/products/database/oracle10g/index.html

Brian