Solved

How to create Oracle ODBC connection in Windows

Posted on 2009-05-08
9
3,444 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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
active directory 5 48
Parametric query in oracle 6 42
Has anyone used domo? 1 40
sql query 9 22
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This article describes some very basic things about SQL Server filegroups.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

746 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

12 Experts available now in Live!

Get 1:1 Help Now