How to create Oracle ODBC connection in Windows

Hi experts,
                   I am working with Oracle 10g and XMLspy and for that I need to make an ODBC connection with my Oracle 10g database.

                   Can someone please guide me with a step by step instructions.

LVL 10
Who is Participating?

Improve company productivity with a Business Account.Sign Up

mrjoltcolaConnect With a Mentor Commented:
1) Make sure Oracle client is installed

2) Make sure TNSNAMES.ORA is configured for your service (assume ORA1), so make sure: "tsnping ORA1"  works before tring to add ODBC connection
If TNSNAMES.ORA is not working, create in {ORACLE_HOME}/network/admin  (see attached). tnsping MUST work before proceeding.

3) Start -> Settings -> Control Panel -3 Administrative Tools -> Date Sources (ODBC)

4) Click "Add" button on either User DSN or System DSN tab. Choose "Oracle in Oracle10g_home1" or whichever your Oracle home is named

5) In Data Source Name add "ORA1" or whatever

6) In TNS Service name you should see a menu that is built from TNSNAMES.ORA, select the TNS alias

7) Click "Test Connection" and enter a username and password for your Oracle schema, verify it works

8) Click Ok to save

# Change CAT to your SID and the IP address to your server, then test with tnsping
      (ADDRESS = (PROTOCOL = TCP)(HOST = = 1521))

Open in new window

aman0711Author Commented:
Ohk... So I have install Oracle client for this.
aman0711Author Commented:
I meant, I have to install Oracle client lol
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Yes, though you can get the instant client that is much smaller.

There are full Oracle ODBC drivers available that don't require the client, but I don't know that any are free.
aman0711Author Commented:
hi mrjoltcola,

            Lets start step by step :-) So I finished first two steps.

             Installed Oracle Client, and ran tns ping.. it gave me all the connection details with OK status code.
So you are good, now proceed to step 3 to get ODBC setup
aman0711Author Commented:
Done :-)

Thanks mrjoltcola :-)

      I have another question, though I can post it in another thread, just want some light from you.
     What is the easiest way to build tables for data coming in XML file
aman0711Author Commented:
Thanks :)
There are many ways, including external loading tools as well as Oracle's very good XML support inside the database. I recommend asking another question in both Oracle + XML zones, and you'll get better response. I will also keep an eye out for your questions and contribute.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.