Which Oracle Client do I need to Install to Connect Excel to Oracle?
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.
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you for this prompt answer. Am trying this now. Will report on my results soon.
A>
A>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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>
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>
- 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
- 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
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.
ASKER
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
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
>>I have added the TNS_NAME environment variable.
It was TNS_ADMIN. Typo or wrong env variable?
It was TNS_ADMIN. Typo or wrong env variable?
"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\admi
- 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
- 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\admi
- 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
- 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.
- the basic package need to be extracted to c:\oracleodbc and the odbc package need to run the installation.
ASKER
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\netw
The path variable contains C:\oracleodbc
I did download instantclient-odbc-windows
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-125
Any suggestions please.
Thanks in advance and I appreciate the patience and help.
A>
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\netw
The path variable contains C:\oracleodbc
I did download instantclient-odbc-windows
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-125
Any suggestions please.
Thanks in advance and I appreciate the patience and help.
A>
ASKER
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.
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.
great it finally work for you :)
OP
OP
ASKER
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>
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>
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