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:
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:
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).
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With