[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

How to create Oracle ODBC connection in Windows

Posted on 2009-05-08
9
Medium Priority
?
3,489 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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
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

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.

Question has a verified solution.

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

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

656 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