How to Configure the ODBC on Oracle XE Client

Even Oracle veterans may encounter some challenges when it comes to setting an odbc  using the Oracle XE Client.    As every DBA will tell you, you will need to configure the tnsnames.ora  (using  net configuration Assistant or by going  to oracle home\network\admin and edit it manually). After that, theTNS Service name in the ODBC will be populated with the entries from the tnsnames.ora file. You will only be required to choose the appropriate entry and you should be good to go.
This was the case in all the latest Oracle releases However If you will download the Oracle XE client you will quickly discover  that there is no tnsnames.ora under the Oracle client XE directory structure. In addition, the network\admin directory is not there as well.
Oracle documentation explains (In their usual user friendly way) how to connect to the Data Base using a connection string (username/password@[//]host[:port][/service_name] where service name is usually XE and port is 1521) but it is not that obvious how to configure the odbc to use this.
The solution is simple enough (once you find it). All that needs to be done is to type  the connection string  on the ODBC’s “ TNS Service Name” field.  Just add the line 127.0.0.1/XE (no need to use the drop down) and test the connection.
If the Data Base is on a different machine, you can replace 127.0.0.1 with the address of the remote server.

15 thoughts on “How to Configure the ODBC on Oracle XE Client

  1. Pingback: Install Oracle Drive and TNS for Windows XP?

  2. Pingback: Install Oracle Drive and TNS for Windows XP? - Admins Goodies

  3. ClintJCL

    I actually can’t follow this.

    You say ” Just add the line”

    Add it to where??!? I know it’s not tnsnames.ora because this articles says that doesn’t exist. So what file am I adding this to?

  4. Asaf Tal Post author

    @clintjcl – you need to use this line in the odbc where you would normally put the tnsnames entry. I just noticed that the image is missing and that is the source of the confusion.

  5. ClintJCL

    Thanks for the quick update. Alas, I am apparently looking at a solution to a different problem. My problem is that “Create New Data Source” does not even list “Oracle” as something I can create a new data source for! Feel free to email me if you know a solution to that one. The person who created my company doesn’t even know 🙂

  6. Sebastian

    Your server is going to need a drvier for Oracle. That means ODBC or OLE. You need one or the other in order to connect.Assumung you truly cannot use ODBC, which I doubt, here’s an OLE connection string using Data Access Components: Provider=MSDAORA;Data Source=114.91.65.12:1521; User ID=userName; Password=userPassword; If that doesn’t work, you can try this:Provider=OraOLEDB.Oracle; Data Source=114.91.65.12:1521; User Id=myUsername; Password=myPassword;And I’d go ahead and try ODBC, since I’ve never heard of a Web server with OLE but without ODBC:Driver={Microsoft ODBC Driver for Oracle}; ConnectString=114.91.65.12:1521; Uid=myUsername; Pwd=myPassword;In all the strings above, you need to remove the spaces following semicolons (;) -2Was this answer helpful?

Leave a Reply

Your email address will not be published. Required fields are marked *