How do I setup an ODBC connection to Oracle using FireDAC?

Question

I can succesfully connect to Oracle through the native drivers, but cannot get an ODBC connection to work. The Oracle 11g client software is installed on my Windows 7 64 bit machine, as well as the 32-bit ODBC drivers downloaded from Instant Client Downloads for Microsoft Windows 32-bit.

Let me first describe the succesful connections:

TNSNames.ORA contains data for an Oracle 10 installation:

# TNSNAMES.ORA Network Configuration File: C:\oracle\ora92\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

VS2003-2005-10.TimeTellBV.nl =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = VS2003-2005-10)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = Orclvs10)
    )
  )

I set up my TFDConnection with

DriverName := S_FD_OraId;
Params.Database := 'VS2003-2005-10';

Alternatively, I can bypass TNSNAMES.ORA by specifying directly:

DriverName := S_FD_OraId;
Params.Database := '(DESCRIPTION=(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = VS2012-2012.timetellbv.nl)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = orcl12.timetellbv.nl) ))';

(As you can see, this is another Oracle server not in TNSNAMES.ORA)

Now the failing attempts through ODBC

Two 32-bit Oracle ODBC drivers are installed:

Microsoft ODBC for Oracle version 6.01.7601.19135 - I think this came with the Win7 OS
Oracle in instantclient_11_2 version 11.02.00.04 - I installed this manually from the source mentioned at the top (running as admin as suggested in the Oracle Instant Client ODBC Release Notes).

I have set up two System DSNs through these drivers but neither work.

The version 6 ODBC driver, giving it the TSNAMES.ORA entry name for 'Server':

and the version 11 Oracle driver idem:

For that second one I added another entry to TNSNAMES.ORA, since that dialog obviously suggested I had to go through that file:

VS2012-2012.TimetellBV.nl =
  (DESCRIPTION=
    (ADDRESS_LIST = 
      (ADDRESS = (PROTOCOL = TCP)(HOST = VS2012-2012)(PORT = 1521))
    )
    (CONNECT_DATA = 
      (SERVICE_NAME = orcl12.timetellbv.nl)
    )
  )

I set up my TFDConnection with

DriverName := S_FD_ODBCId;
Params.Add('DataSource=' + lODBCName);   // The ODBC DSN Name
Params.Database := lDataSection.Values[INISTRING_DATABASENAME];

Both this code and the test button in de 32-bit ODBC admin fail with error:

Unable to connect
SQLState IM003
The specified driver could not be loaded due to system error 127: cannot find procedure<br />
(Oracle in instantclient_11_2; D:\app\jan\product\11.2.0\client_1\SQORA32.dll)

That folder is where my Oracle client software is installed, it contains SQORA32.DLL (and others) and is in my system path.

I have tried all kinds of variations including/excluding domain name, database name, etc, without success.

How do I properly set up an ODBC connection to connect to Oracle, to be used in a Delphi-Tokyo 32 bit app through FireDAC?

What also did not help:

Reinstalling the Visual Studio 2013 32-bit redistributable
Copy sqora32.dll to the Windows SysWOW64 folder
Setting ORACLE_HOME environment variable to d:\app\jan\product\11.2.0\client_1\
Setting TNS_ADMIN environment variable to d:\app\jan\product\11.2.0\client_1\
Renaming sqora32.dll to SQORA32.dll
Replaced c:\windows\syswow64\mfc42.dll dated 11 March 2011 with a newer one from the Oracle home folder, dated 27 April 2011

I'm a bit confused that you mention FireDAC in this q since you seem to be talking about ODBC configuration: When you say that neither of the 32-bit system DSNs that you've set up work, surely the last page of the Windows System-DSN wizard gives you the opportunity to test the DSN?, so what happens why you do that? Does the test fail or succeed? — MartynA, Jun 25 '18 at 16:45
@MartynA Yes it's basically ODBC, but the final end is to get the ODBC connection to work with the TFDConnection (which params to use, Server Name or Database). I mentioned already that both the test button and the FireDAC connection fail with the 127 error. — Jan Doggen, Jun 25 '18 at 19:04

Jan Doggen · Accepted Answer · 2018-06-29T14:44:31.700

I don't know what was wrong. I found a client installer executable (i.e. that's different from the "instant client", which has no installer, and whose reinstallation did not help either), cleaned up my VM from all Oracle stuff, ran the installer with 'administrator' selection (=all available software in the package) and that finally works. ODBC was installed as well.

The only things that need to be set for the TFDCOnnection are:

DriverName := S_FD_ODBCId;
Params.Add('DataSource=' + lODBCName);

with lODBCName being the ODBC System DSN name.

(I intended to leave this as a comment for 10K users and then delete the question, but that's not possible since I put a bounty on it earlier today. Well, maybe it serves anyone else).

score 1 · Answer 2 · answered Jul 06 '18 at 07:30

I have already encountered similar issue.

I can use SQLPLUS or JDBC to connect to Oracle database, but when I try to define an ODBC connection or a .Net Linq connection, that don't work.

I have then modified my TNSNAMES.ora file and replaced SERVER-NAME by SID and suddently ODBC connection has been possible.

How do I setup an ODBC connection to Oracle using FireDAC?

2 Answers2