Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Connecting to Oracle with ODBC info

Posted on 2011-09-08
10
Medium Priority
?
695 Views
Last Modified: 2012-08-13
Not sure if this is possible or not,
but I'm trying to connect to an Oracle Database
using 3rd party software (Navicat).

I've been given instructions on how to get access to the
database through Access using an OBDC connection, but
would rather use Navicat to perform queries. I've tested the connection
in the ODBC manager, and it appears to work.

Thanks,
-JW
0
Comment
Question by:JustinW
[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
  • 6
  • 4
10 Comments
 
LVL 1

Author Comment

by:JustinW
ID: 36505307
Oh,
and in the ODBC Data Source Administrator,
the Driver is listed as "Oracle in OraClient 11g_home1"
0
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 36506145
What is the question?

Thanks
0
 
LVL 1

Author Comment

by:JustinW
ID: 36506220
How do I connect to the database?
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 15

Expert Comment

by:Devinder Singh Virdi
ID: 36506312
As per Navicat website, it will use TNS files, so you dont need ODBC.
TNS is the entry in  <ORACLE_HOME>/network/admin location.

Please read the following link
http://www.navicat.com/en/products/navicat_oracle/oracle_detail_win.html

0
 
LVL 1

Author Comment

by:JustinW
ID: 36506361
Thanks,

The Default Settings for Navicat's ORACLE - NEW CONNECTION are:

Connection Name: BLANK
Connection Type: Basic
Host Name/IP Address: BLANK
Port: 1521
Service Name/SID: ORCL
User Name: BLANK
Password: BLANK

Other than the Username and Password (which I have), What do I need to put in the blanks?
Is the rest of this configuration correct for what I want?
0
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 36506428
You can type anything in connection name,
However you can select TNS in Connection Type.
Then provide SCOTT as username and TIGER as password OR you can test with other users as well.
In host name you can enter your computer name or assign your computer some IP address and use that one.
0
 
LVL 1

Author Comment

by:JustinW
ID: 36506523
When I change the connection type From BASIC to TNS,
the form changes; giving me these options to complete:

Net Service Name: BLANK (dropdown)
User Name: BLANK
Password: BLANK

The Net Service Name Field doesn't have any prepopulated options
to chose from.

If I type in the DataBase I want to connect to (CPSPROD),
and click Test Connection, it throws
ORA-12154: TNS:could not resolve the connect identifier specified

Open in new window


The Oracle ODBC Driver Configuration form,
There's a Dropdown called **TNS Service Name**
that is prepopulated, and has the Database I want to connect to (CPSPROD).

When I test the connection on that; it says that the test was successful


0
 
LVL 1

Author Comment

by:JustinW
ID: 36506550
I've never installed anything oracle on my computer before,
could I be missing a driver?

0
 
LVL 15

Accepted Solution

by:
Devinder Singh Virdi earned 2000 total points
ID: 36506564
You can search for TNSNAMES.ORA file under c:\oracle...\network\admin directory

Add the following (Change YourMachineName and TypeDatabaseName accordingly)

CPSPROD=
  (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = YourMachineName)(PORT = 1512))
    (CONNECT_DATA =
     (SERVICE_NAME = TypeDatabaseName)
        (INSTANCE_NAME= CPSPROD)
    )
  )
0
 
LVL 1

Author Closing Comment

by:JustinW
ID: 36506728
Just copied the info from the ORA File into Navicat.
Lit right up!
Thx!
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

664 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