Minimal set of steps to connect to Oracle DB
Posted by Igor Moochnick on 01/28/2008
This is what you need to do on your deployment server to make sure your app can access Oracle:
- Get the Oracle ODAC. Make sure that you’re using the appropriate version. In most of the cases ODAC versions are backward compatible, so you can use ODAC from 10g to connect to 9i, but you’ll have problem in some cases to connect to 11g. I, personally, prefer to use the most recent and most distributed/polished version. So today I use 10g since 11g is not that thoroughly polished.
- Install just the ODAC Components – see it on the picture. Do not install DB Extensions if you really need them.
- Make sure to change your connection string appropriately.
Personally, I prefer not to use the TNSNames.ora file to store my connection info, mainly because I have a heterogeneous environment where some of my services are connecting to MS SQL and some of them to Oracle. This allows me to control all the connection definition in the consistent manner, so this is, for example, how you connection string to Oracle may look like:
Note that the connection looks almost identical (including the connection pooling) to the SQL one except from the following things:
- providerName – makes sure the ADO will use Oracle ODAC
- DataSource – to make it simple, just copy your connection info from TNSNames.ora directly into your connection string (AS-IS). You can use any Oracle dev tool to create such connection definition.
- A bunch of connection string parameters are not working in Oracle connection (it’ll throw an Exception when you’ll create a DB connection) – refer to documentation for more info.
Stay tuned for more info on how to write your code to be DB agnostic. It’s pretty tricky and not straightforward as it described in the DbProvider-related documentation mainly due to vast differences between MS SQL and Oracle implementations.