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.
DB Tricks
DataBase Tips and Tricks
That’s save my life… thank you!!
Good to know. thank you for writing about this.
Pingback: Install Oracle Drive and TNS for Windows XP?
Thank you, it was too easy, who would have thought !
Thank you… you save me 🙂
Pingback: Install Oracle Drive and TNS for Windows XP? - Admins Goodies
very good article, thank you very much, it helped a lot! and you saved my time…
You rock!!!!
Thank you. Never expect the step is so simple…..you are great.
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?
@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.
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 🙂
simple and useful,
thank you very much bro
You are awesome!!
Now I can work on my projects. 🙂
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?
Thanks ! Waster many hours trying to fix things
Tons of webpages but only this one saved my day.
Thanks man!
Pingback: Install Oracle Drive and TNS for Windows XP? - Boot Panic