?
Solved

Which Oracle Client do I need to Install to Connect Excel to Oracle?

Posted on 2011-05-13
17
Medium Priority
?
2,569 Views
Last Modified: 2012-06-27
Dear EE
I would some help to connect Excel to Oracle.
I am using Excel 2007 (32-bit) on Windows Vista Enterprise 64-bit. I want to connect to an Oracle 10g database through our local network using ODBC. I need to fetch some data once into a sheet and then the rest of the calculations would be done in Excel.
There are no firewall issues as I can connect from my machine, using SQL developer as a local client to the Oracle Database.
I tried to make an ODBC connection but Oracle is not listed as a driver.
I downloaded a 587 Mb file from oracle called win64_11gR2_client.zip
I'm reading the documentation and having problems choosing.

Which installation do I need: Install Client or Install Client Light, or Administrator, Runtime or Custom?
Do I need to install a 64bit client to access my Oracle Database ?
Can I use the 11G Client to access a 10 G database ?
Is ODBC the best way to do this?

Thanks for all your help, in advance.

Akajohn.
0
Comment
Question by:akajohn
  • 6
  • 5
  • 3
  • +1
17 Comments
 
LVL 30

Expert Comment

by:Randy Downs
ID: 35753846
Try this - http://www.oracle.com/technetwork/database/features/instant-client/index-100365.html

Oracle Database Instant Client
 
Instant Client allows you to run your applications without installing the standard Oracle client or having an ORACLE_HOME. OCI, OCCI, Pro*C, ODBC, and JDBC applications work without modification, while using significantly less disk space than before. Even SQL*Plus can be used with Instant Client. No recompile, no hassle.

ISVs and Partners benefit greatly from packaging Instant Client along with their applications, saving customers the extra step of installing and configuring an Oracle client. Free. The same fully-featured, high-performance applications that Oracle ISVs and partners have always delivered will continue to work, untouched.

Customers can try new packaged applications and Oracle client features quickly without worrying about other installations. Larger enterprises can automate setup and configuration of Instant Client by using installation scripts accessing a central IT repository. Finally, everyone can benefit from the smaller footprint.

Especially for production use. Especially for free.

 FAQ
 Whitepaper: Instant Client Overview
0
 
LVL 30

Expert Comment

by:Randy Downs
ID: 35753894
Since you have a Windows Vista Enterprise 64-bit , I 'd use the 64 bit version. Typically you can read a database with newer software so 11G should work but it may want to convert the data so it would be easiest to use 10G.
0
 
LVL 30

Assisted Solution

by:Randy Downs
Randy Downs earned 400 total points
ID: 35753911
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 6

Author Comment

by:akajohn
ID: 35753939
Thank you for this prompt answer. Am trying this now. Will report on my results soon.

A>
0
 
LVL 23

Accepted Solution

by:
OP_Zaharin earned 800 total points
ID: 35754030
since your application is 32bit, you need to use the odbc administrator of 32bit. the one in control panel is for 64bit driver.


The 32-bit version of the Odbcad32.exe file is located in the %systemdrive%\Windows\SysWoW64 folder.
The 64-bit version of the Odbcad32.exe file is located in the %systemdrive%\Windows\System32 folder.

- i would suggest you to download at 32bit Oracle instant client.
0
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 800 total points
ID: 35754100
To add:  you need the Instant client basic and the ODBC add on.  It is two downloads.
0
 
LVL 6

Author Comment

by:akajohn
ID: 35755239
Thanks for the advice. I have downloaded both files. Added the unzipped files' path (C:\oracleodbc) to the system path. Installed ODBC.

Now I went straight to the Windows ODBC Data Source Administrator and the ORacle driver is available.

Now I'm stuck with these options.


What do I need to put in Data Source name, Description, and TNS service Name.
I know what to put in USER ID  but do not see a password filed.
Do i need a TNSNAMES.ORA file in the fodler C:\oracleodbc ?

Thanks again.

A>
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35755349
- you need to copy the tnsnames.ora to C:\oracleodbc from the database server in oracle folder.
- then in the environment variable, create the TNS_ADMIN path to  C:\oracleodbc
- then create the data source name, and keyin the TNS service name as in your tnsnames,ora entry
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35758147
The other option is to create a network/admin folder under your install directory and copy the tnsnames.ora file there.  That is the default location and won't require an environment variable.
0
 
LVL 6

Author Comment

by:akajohn
ID: 35779133
Apologies for the delay. Work commitments.

I have added the TNS_NAME environment variable.

The Data Source name I called it OracleLI
The Description I called it LI
I added the service name as in tnsnames.ora   (is this case sensitive ?)
I then put my user_id.

Then I went to Excel 2007, In the Data Tab

Chose "From Other Sopurces"
Then Chose "Data Connection Wizard"

In the Data Connection Wizard i chose "ODBC DSN"

Then Next and then  I cose "OracleLI" , it was in the list.

Then chose the Data Source Source in 1.

When I click "test Connection" I get "Test Connection failed because of an error in initializing provider - Unspecified error"

See Screenshot Please.


Any ideas ?


Thanks again for the help so far.

odbc.JPG
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35779168
>>I have added the TNS_NAME environment variable.

It was TNS_ADMIN.  Typo or wrong env variable?
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35781997
"I have added the TNS_NAME environment variable."
- kindly check your environment variable again. the entry suppose to be TNS_ADMIN. and it should contain the path to your tnsnames.ora location. depending where you save it C:\oracleodbc or C:\oracleodbc\network\admin
- in the environment variable, you should also add ;C:\oracleodbc to the existing PATH entry  

"I added the service name as in tnsnames.ora   (is this case sensitive ?)"
- its not case sensitive
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35782022
- btw just to check, you should also download 2 sets of instant client package which is instant client basic and odbc as pointed by slightwv.
- the basic package need to be extracted to c:\oracleodbc and the odbc package need to run the installation.
0
 
LVL 6

Author Comment

by:akajohn
ID: 35797513
Hi again,

OP_Zaharin-> Correct it was a typo. I just checked after rebooting in the command windows. The TNS_ADMIN variable is set to C:\oracleodbc.
I copied the tnsnames.ora from the server (C:\Oracle\Ora10gR2DB\network\admin\tnsnames.ora)
The path variable contains  C:\oracleodbc

I did download instantclient-odbc-windows.x64-11.2.0.2.0.zip and instantclient-basiclite-windows.x64-11.2.0.2.0.zip as instructed. The odbc_install.exe told me it installed successfully.
Previously  in the user dns tab a driver for Oracle was not listed. Now it is. So this bit works fine.


Ok. Thanks again. Bit I went one step back to do some more testing.


Back to the user dsn tab in the ODBC dialog box in windows.

Used the following values

Data Source name: OracleLI
Description 123
TNS Service Name: I chose fro ma drop down menu our prod environment. (There are two entries with the production name, one in caps and the other in lowercase, the other entries correspond to other environments that were in the tnsnames.ora.)
Userid: my username

I tried "Test the connection" button and got this:

Unable to Connect

SQlState=S1000
[Oracle][ODBC][Ora]ORA-12545: Connect failed because target host or object does not exist
Any suggestions please.

Thanks in advance and I appreciate the patience and help.
A>
0
 
LVL 6

Author Comment

by:akajohn
ID: 35893390
Thanks for the help and apologies for the delay in replying.
I can see my mistake now and thanks very much for the pointer for the ODBC 32 and 64 bit.

I needed to use 32 bit drivers and then it worked.


Akajohn.
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35893594
great it finally work for you :)

OP
0
 
LVL 6

Author Comment

by:akajohn
ID: 35894145
Information to help others: I got to the bit where I get to select the tables and I was happy. Then I realised, where do you actually put SQL your query in, instead of pulling the whole table.
You could do views but that is not the point. I got an ORA-error but managed to correct it.

These two links helped with the final few steps:

http://blog.mclaughlinsoftware.com/2009/11/30/sql-query-in-excel-2007/

http://blog.mclaughlinsoftware.com/microsoft-excel/how-to-query-oracle-from-excel-2007/


A>

0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Suggested Courses

807 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