?
Solved

How to create Oracle ODBC connection in Windows

Posted on 2009-05-08
9
Medium Priority
?
3,485 Views
Last Modified: 2012-05-06
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
0
Comment
Question by:aman0711
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 40

Accepted Solution

by:
mrjoltcola earned 2000 total points
ID: 24340526
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
 
LVL 10

Author Comment

by:aman0711
ID: 24340534
Ohk... So I have install Oracle client for this.
0
 
LVL 10

Author Comment

by:aman0711
ID: 24340587
I meant, I have to install Oracle client lol
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24340623
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
 
LVL 10

Author Comment

by:aman0711
ID: 24355662
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
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24355975
So you are good, now proceed to step 3 to get ODBC setup
0
 
LVL 10

Author Comment

by:aman0711
ID: 24356114
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
 
LVL 10

Author Closing Comment

by:aman0711
ID: 31579621
Thanks :)
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24356439
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

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
This article provides a convenient collection of links to Microsoft provided Security Patches for operating systems that have reached their End of Life support cycle. Included operating systems covered by this article are Windows XP,  Windows Server…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question