Oracle. A pain in the arse. Had to recently create an app that would run on scheduling a couple of times a day. OK, fine. Installed Oracle11 Express Client (it’s fucking huge!) – worked fine on my dev machine. On production- not so much. The app complained it needed a newer version of the oracle libs.
Anyway, at least I got the connection working by copying the following DLLs to the app directory:
oraociei1.dll, mfc71.dll, msvcr71.dll, oci.dll, ocijdbc11.dll, ociw32.dll, orannzsbb11.dll, oraocci11.dll
Probably not all needed, but better safe than sorry. Oracle error messages can not help themselves, or anyone else, out of a wet paper bag.
Now the dreaded ORA-12154 tnsnames.ora error message. Google around and you’ll find heaps of unsolved answers. OK, figure out I need to add an environment variable
(Control Panel -> System -> Advanced Settings -> Environment Variables)
Add TNS_ADMIN and set this to the full path to the location of tnsnames.ora – e.g. C:\oracle\tns
OK, test on production – works fine. Add all this to the scheduler.
Check back half a day later – see that Oracle cannot find tnsnames.ora – sigh – what now?
Turns out, and I blame Oracle, that it doesn’t use TNS_ADMIN if the app is started from a different path (the scheduler’s)
OK. Google more. Fuck tnsnames.ora and I put everything in the connection string itself:
string _cnx = String.Format("Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP) (HOST={0}) (PORT={1}) (CONNECT_DATA=(SERVICE_NAME={2})));User Id={3};Password={4};", DBHOST, PORTNUMBER, SERVICENAME, USER, PASSWORD);
And it finally works from anywhere. Fuck you, Oracle.
