0

Hi I'm attempting to use some of the SQL code I've written in SQL developer through a direct database connection via Excel. Unfortunately the error message I receive whenever I try is:

"Attempt to load Oracle client libraries threw BadImageFormatException. This problem will occur when running in 64 bit mode with the 32 bit Oracle client components installed."

I've painstakingly installed the Oracle 64bit client on my PC, so that at least, should be working. Unfortunately I've spent the best part of two days looking up solutions on the internet with no success. Does anyone know how I can force Excel to use my 64bit client or avoid this error in another way?

Update: I believe despite my best efforts the problem is somewhere within my TNSNAMES file since I can't connect using SQL Plus either. Here's a redacted version of my Connection string. The ommitted details are correct since they are the same as I'm using for SQL Developer. Am I missing something?

DB1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.xxx.xxx)(PORT = 1521))
)
(CONNECT_DATA = (SID = SID1)(SERVER = DEDICATED)
)
)

DB2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.xxx.xxx)(PORT = 1521))
)
(CONNECT_DATA = (SID = SID2)(SERVER = DEDICATED)
)
)
Raven
  • 13
  • 3
  • Presumably you'd get a similar problem running 32-bit Excel with 64-bit Oracle client components; perhaps you're doing it that way around and the error message is too generic? – Alex Poole Feb 16 '17 at 13:03
  • [Possible duplicate](http://stackoverflow.com/q/24104210/266304) – Alex Poole Feb 16 '17 at 13:08

2 Answers2

0

Obviously, it's something small in your machine settings or configuration.

I would suggest you will look at your machine's PATH environment variable. It may refer to an older version of Oracle Server or the Oracle Client Components.

Make sure you know what you are using to access Oracle from Excel, JDBC or ODBC?

It's always the little details which drive us crazy.

Binyamin Regev
  • 914
  • 5
  • 19
  • 31
  • I manually altered the PATH environment variable to direct it towards my 64bit client so I'm fairly sure that's not the problem. I was using ODBC. – Raven Feb 16 '17 at 13:12
  • Did you check your ODBC Data Sources settings in Administrative Tools? To verify that you can connect to Oracle server via ODBC. – Binyamin Regev Feb 16 '17 at 13:22
  • I'm struggling to access it via the ODBC data sources menu. I added the data source with the connection details I used to connect via SQL Developer (having to write my own tnsnames.ora file in the process). But I get a "ORA-12154: TNS:could not resolve the connect identifier specified" error. I can connect to the database via SQL Developer so is there alternatively a JDBC equivalent driver I can use in this situation from Excel? – Raven Feb 16 '17 at 16:23
  • You need to check with documentation of the Excel version you are using. SQL developer to access Oracle Server is easier with JDBC. But, if you have an advanced (new) enough version of Excel, maybe it supports JDBC, or maybe you can use .NET framework to access the Oracle server. I can give you another tip: if you take an Excel XLSX file and rename it to ZIP file, open the ZIP file with WINZIP, WINRAR and you see it's made of XML files, those you can easily access with Java and/or .NET framework. The same goes to DOCX and the rest of MS Office data files versions 2010 and later. – Binyamin Regev Feb 16 '17 at 18:07
  • Good tip, thanks. Found lots of XML files in there so it looks promising. Is there a particular driver I should look for in that case? – Raven Feb 17 '17 at 11:34
  • any driver you like will do. I did it with POJO and JAXB – Binyamin Regev Feb 19 '17 at 09:36
  • you don't happen to have a link to a good (or easy to install) one do you? – Raven Feb 20 '17 at 15:21
  • I used the one from Oracle official website, and I found one on Microsoft website. I will see if I still have it. – Binyamin Regev Feb 20 '17 at 15:43
0

Did you see any of these answers?

SQL Server 2012 connectivity to Oracle

Getting MS Office 2013 (32 bit) Excel and Access to connect to Oracle database when both 32 and 64 clients are installed

and I found this link, too complicated to copy everything to the answer:

Connect To Oracle Via ODBC Driver

And you can try connecting to Oracle with ODBC using ODAC or Oracle client, download ODAC112030_x64.zip from Oracle's website. Here's a link on how to install it SETTING UP AN ORACLE ODBC DRIVER AND DATA SOURCE

Community
  • 1
  • 1
Binyamin Regev
  • 914
  • 5
  • 19
  • 31
  • I almost cried for joy when I found the executable in the zip file. I have been trying to set up the data connection from the administrator tools but I'm still getting a "ORA-12154: TNS:could not resolve the connect identifier specified" error. I can't see a problem with my TNSNAMES.ORA and I know the details are correct since I can connect through JDBC via SQL Developer. – Raven Feb 17 '17 at 11:11