Solved

How to create Oracle ODBC connection in Windows

Posted on 2009-05-08
9
3,449 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
  • 5
  • 4
9 Comments
 
LVL 40

Accepted Solution

by:
mrjoltcola earned 500 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
 
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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
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.

910 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now