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.


Thanks
LVL 10
aman0711Asked:
Who is Participating?
 
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


 
# TNSNAMES.ORA
# Change CAT to your SID and the IP address to your server, then test with tnsping
CAT =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = CAT)
    )
  )

Open in new window

0
 
aman0711Author Commented:
Ohk... So I have install Oracle client for this.
0
 
aman0711Author Commented:
I meant, I have to install Oracle client lol
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
mrjoltcolaCommented:
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.
0
 
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.
0
 
mrjoltcolaCommented:
So you are good, now proceed to step 3 to get ODBC setup
0
 
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
0
 
aman0711Author Commented:
Thanks :)
0
 
mrjoltcolaCommented:
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.
0
All Courses

From novice to tech pro — start learning today.