Как установить odbc драйвер для oracle
How to install ODBC driver for Oracle in Windows 7.
Installation of the ODBC driver in Windows 7 is quite simple but there are a few tricks better to know. Here is a step-by-step instruction how to install the ODBC driver.
First of all, you need to download proper files:
– here is the link for Instant Client Downloads for Microsoft Windows (32-bit), for ODBC, you need instantclient-basic-nt-11.2.0.3.0.zip and instantclient-odbc-nt-11.2.0.3.0.zip files (Oracle 11.2.0.3 version)
– here is the link for Instant Client Downloads for Microsoft Windows (x64), for ODBC, you need instantclient-basic-windows.x64-11.2.0.3.0.zip and instantclient-odbc-windows.x64-11.2.0.3.0.zip files (Oracle 11.2.0.3 version)
Which version to choose – 32-bit or 64-bit?
Well, I think the best way to answer this question is to describe my situation. I need to get data from Oracle to Excel. I have on my PC: Windows 7 – 64-bit version, 64-bit Oracle client already installed but MS Office in 32-bit version. So in my situation I had to choose 32-bit ODBC driver. Generally, you choose ODBC driver version based on tool version, that you use to get data from Oracle database.
Anyway, both downloaded files, you need to unzip to THE SAME folder (for example: c:\oracle\instant_client_11), then add the folder to the PATH environment variable:
Then add the TNS_ADMIN environment variable indicating the path to the tnsnames.ora file (in my PC it is c:\oracle\11.2.0\CLIENT\network\admin):
Next, open the command line (Run as administrator) and go to the folder where you unzip ODBC driver, in my case:
and then – still in cmd, install ODBC:
When successfully, you should get the following info:
So, right now we can configure ODBC connection: choose Control Panel, then Administrative Tools, then Data Sources (ODBC), then System DNS and then Add, on the list, choose instant_client_11, then Finish and then in the configuration window… wait. You don’t have instant_client_11 on the list. That’s the problem I also had – ODBC driver didn’t appear in ODBC data source.
This is because you use 64-bit ODBC administration panel. If you install 32-bit ODBC driver, you’ll need to use 32-bit ODBC administration panel – run odbcad32.exe from c:\windows\SysWOW64.
So, on the list, choose instant_client_11, then Finish and then in the configuration window add proper data in the Data Source Name, Description, TNS service name (from tnsnames.ora) and User ID.
Then, you can test connection and when everything is correct, save the connection, close ODBC window and enjoy your Oracle data in MS Office tools 🙂
Oracle's Instant Client ODBC software is a standalone package that offers the full functionality of the Oracle ODBC driver (except the Oracle service for Microsoft Transaction Server) with a simple install.
The ODBC driver has Oracle's standard client-server version interoperability, see Support Doc ID 207303.1. For example Instant Client ODBC 19c can connect to Oracle Database 11.2 or later.
Installing Oracle Instant Client Basic and Basic Lite
Instant Client ODBC requires the Oracle Instant Client Basic or Basic Light package (depending on your locale requirements) also be installed. Download the desired package from OTN for your operating system and follow the installation instructions on the download page. For example, unzip the package to C:\instantclient_19_3 on Windows, unzip to /opt/oracle/instantclient_19_3 on Linux or other platforms, or use yum to install the RPM packages on Linux.
More details on Oracle Instant Client is in the Oracle Call Interface Programmer's Guide.
Installing Oracle Instant Client ODBC
On Linux and UNIX
1. Download Install the Instant Client Basic or Basic Light package as described above.
2. Download the Instant Client ODBC package. Unzip it in the same directory as your Basic or Basic Light package. Or, if using the RPM package on Linux, install it with yum .
3. Install the unixODBC driver manager if it is not already available. Refer to the "Recommended unixODBC Driver Manager version" section in this document for more information.
4. Execute odbc_update_ini.sh from the Instant Client directory.
5. Set any Oracle Globalization variables required for your locale. See the Oracle Database Globalization Support Guide for more information. For example on Linux you could set export NLS_LANG=JAPANESE_JAPAN.JA16EUC to work in the JA16EUC character in Japanese.
Parameter | Description |
---|---|
ODBCDM_Home | unixODBC driver manager home directory path |
Install_Location* | Oracle Instant Client directory path. The default path is the current directory |
Driver_Name* | Driver name to identify the Oracle ODBC driver residing in current Oracle Instant Client home. The default name will be like "Oracle 19c ODBC driver" |
DSN* | Sets ODBC DSN name. The default name will be "OracleODBC-19c" |
ODBCINI* | Directory path of the .odbc.ini file. The default path is the user's home directory, for example ~/.odbc.ini |
* - Optional parameters
On Windows
- Install the Instant Client Basic or Basic Light package, as described above.
- Download the Instant Client ODBC package. Unzip it in the same directory as your Basic or Basic Light package.
- Execute odbc_install.exe from the Instant Client directory. If Instant Client is 11g or lower, start the command prompt with the Administrator privilege. To install with Japanese language support, execute the command odbc_install.exe JA .
Environment setup for ODBC applications
An ODBC application has to load the Oracle Instant Client ODBC driver's shared library file (see next section) to connect to Oracle Database. On Linux/Unix the directory path of the shared library should be set in the environment variable LD_LIBRARY_PATH, or platform equivalent. It can also be configured in /etc/ld.so.conf. On Windows it should be set in the PATH environment variable.
Refer to the topics Environment Variables for OCI Instant Client and Database Connection Strings in the Oracle Call Interface Programmer's Guide for setting up the Database Connection string and related environment variables like TNS_ADMIN, TWO_TASK, LOCAL etc.
Contents of the Oracle Instant Client ODBC Package
Description | Linux and UNIX | Windows |
---|---|---|
Oracle ODBC driver shared library | libsqora.so.XX.Y. For example libsqora.so.19.1 | sqora32.dll |
Installation file | odbc_update_ini.sh | odbc_install.exe, odbc_uninstall.exe |
Oracle ODBC driver configuration dialog window (GUI) | Not available | sqoras32.dll, sqresus.dll, sqresja.dll |
Help System | help/ | help/ |
Patching Oracle Instant Client ODBC
Note: Backup the Oracle ODBC driver shared library and other files before patching them.
On Linux and UNIX Patching the Instant Client ODBC driver on Linux/UNIX can be done by generating the Instant Client ODBC package and Basic or Basic Light package in a patched ORACLE_HOME. The procedure for patching and generating Instant Client ODBC, Basic and Basic Light packages is given in the Oracle Call Interface Programmer's Guide. These new packages should then be unzipped into the Instant Client directory that needs to be patched. This method of patching is recommended.
Alternatively the Oracle Instant Client ODBC driver can be patched by copying the files that are listed below from a patched ORACLE_HOME:
- ODBC driver shared library file:
For 19c: libsqora.so.19.1
For 18c: libsqora.so.18.1
For 12c: libsqora.so.12.1
For 11g: libsqora.so.11.1 - Required additional files when using Oracle Instant Client Basic:
For 19c: libociei.so, libclntshcore.so.19.1, libclntsh.so.19.1, libnnz19.so, libons.so
For 18c: libociei.so, libclntshcore.so.18.1, libclntsh.so.18.1, libnnz18.so, libons.so
For 12c: libociei.so, libclntshcore.so.12.1, libclntsh.so.12.1, libnnz12.so, libons.so
For 11g: libclntsh.so.11.1, libociei.so, libnnz11.so - Required additional files when using Oracle Instant Client Basic Light:
For 19c: libclntsh.so.19.1, libclntshcore.so.19.1, libociicus.so, libnnz19.so, libons.so
For 18c: libclntsh.so.18.1, libclntshcore.so.18.1, libociicus.so, libnnz18.so, libons.so
For 12c: libclntsh.so.12.1, libclntshcore.so.12.1, libociicus.so, libnnz12.so, libons.so
For 11g: libclntsh.so.11.1, libociicus.so, libnnz11.so
On Windows Patching the Instant Client ODBC driver on Windows can be done only by manually copying the ODBC driver shared library files and supporting library files from a patched ORACLE_HOME or from an unpacked Oracle Database Bundle patch. These should be copied into the Instant Client directory. Generating an Instant Client ODBC package is not available on Windows.
The files that must be copied to the Instant Client directory:
- ODBC driver shared library files: sqora32.dll, sqoras32.dll, sqresus.dll, sqresja.dll
- Required additional files when using Oracle Basic Instant Client:
For 19c: oraociei19.dll, orannzsbb19.dll, oci.dll, oraons.dll, ociw32.dll, oraociei19.sym, orannzsbb19.sym, oci.sym, ociw32.sym
For 18c: oraociei18.dll, orannzsbb18.dll, oci.dll, oraons.dll, ociw32.dll, oraociei18.sym, orannzsbb18.sym, oci.sym, ociw32.sym
For 12c: oraociei12.dll, orannzsbb12.dll, oci.dll, oraons.dll, ociw32.dll, oraociei12.sym, orannzsbb12.sym, oci.sym, ociw32.sym
For 11g: oraociei11.dll, orannzsbb11.dll, oci.dll, ociw32.dll, oraociei11.sym, orannzsbb11.sym, oci.sym, ociw32.sym - Required additional files when using Oracle Basic Light Instant Client:
For 19c: oraociicus19.dll, orannzsbb19.dll, oci.dll, oraons.dll, ociw32.dll, oraociicus19.sym, orannzsbb19.sym, oci.sym, ociw32.sym
For 18c: oraociicus18.dll, orannzsbb18.dll, oci.dll, oraons.dll, ociw32.dll, oraociicus18.sym, orannzsbb18.sym, oci.sym, ociw32.sym
For 12c: oraociicus12.dll, orannzsbb12.dll, oci.dll, oraons.dll, ociw32.dll, oraociicus12.sym, orannzsbb12.sym, oci.sym, ociw32.sym
For 11g: oraociicus11.dll, orannzsbb11.dll, oci.dll, ociw32.dll, oraociicus11.sym, orannzsbb11.sym, oci.sym, ociw32.sym
Note: While copying from the Oracle Database Bundle patch, some of the files above may be missing. This implies that those files are unchanged and do not need to be patched.
На сервере с операционной системой Windows Server 2016 потребовалось установить Oracle Instant Client 12.2 с драйвером ODBC.
Ничего сложного в установке нет, но почему-то не всегда это получается сделать с первого раза.
Установка Oracle Instant Client 12.2
Внимательно читаем инструкцию:
Скачиваем пакет instantclient-basic-windows.x64-12.2.0.1.0.zip. Распаковываем. Внутри находится папка instantclient_12_2. Копируем её на диск C:\ в папку oracle так, чтобы получился путь:
Для работы пакета 12.2 требуется Microsoft Visual Studio 2013 Redistributable. Скачиваем и устанавливаем.
В переменную PATH добавляем путь C:\oracle\instantclient_12_2. Для этого в свойствах компьютера нажимаем Environment Variables.
В нижней секции System variables находим переменную Path и редактируем, Edit.
Добавляем путь C:\oracle\instantclient_12_2 и кнопкой Move Up перемещаем его вверх.
Проверим что получилось. В командной строке под администратором выполняем команду:
В пути долен присутствовать элемент "C:\oracle\instantclient_12_2;".
Если нам нужно использовать собственные конфигурационные файлы: tnsnames.ora, sqlnet.ora, ldap.ora, или oraaccess.xml; то создаём директорию:
и копируем эти файлы в неё. Если необходимо использовать конфигурационные файлы в другой директории, то укажите путь к ней в серверной переменной TNS_ADMIN.
Установка ODBC
Там же где скачивали Oracle Instant Client 12.2, находим для той же версии пакет ODBC instantclient-odbc-windows.x64-12.2.0.1.0-2.zip. Скачиваем, распаковываем.
Содержимое копируем в C:\oracle\instantclient_12_2.
ODBC устанавливается путём запуска файла odbc_install.exe.
Для удобства запускаем его из командной строки под администратором.
Oracle ODBC Driver is installed successfully.
При запуске можно увидеть ошибку:
Oracle ODBC driver with same name already exists
В этом случае добавляем пользователя под которым работаем в группу локальных администраторов и бэкап-операторов. После чего снова запускаем командную строку под администратором и устанавливаем ODBC. Недостаточно просто быть в доменной группе, которая добавлена в локальные администраторы. Пользователь должен явно числиться локальным админом.
Select the correct architecture of your system either 32 bit or 64 bit. In this example I am going to install the 32 bit version since I have MS Office 32 bit version.
Next, download the Instant Client Basic package as shown on the picture above. Also, you will need to download the Instant Client SDK and Instant Client ODBC as shown below
So now we have three packages:
- instantclient-basic-nt-12.2.0.1.0.zip
- instantclient-sdk-nt-12.2.0.1.0.zip
- instantclient-odbc-nt-12.2.0.1.0-2.zip
Now Create a new folder for the Oracle client. For example, in this case I created a new folder : C:\Oracle\Product\client. Put all those three zip packages in that new directory. Next, extract them all. It will produce a new directory called: instantclient_12_2. If you download the different version, for example version 18.3, it will create new directory instantclient_18_3.
Step 2. Add the Oracle Client directory to the PATH environment
Now we need to add our Oracle Client directory to the Windows PATH environment. Right-click My Computer >> Advanced System Settings >> Environment Variables
On the System Variables, click New and then enter the following details:
Change C:\Oracle\Product\client\instantclient_12_2 with your actual Oracle Client folder.
Next step is to add the Oracle Client directory to system path. Double click the path and create a new entry as follow
Step 3. Install the Oracle ODBC Client
Now open the folder instantclient_12_2, in your Oracle Client folder created on the Step 1 and double click the file odbc_install.
Please note that there is no GUI installation wizard when you open this file.
Step 4. Create a new ODBC Data Source on Windows 10
To create a new Oracle ODBC Data Source, open Control Panel >> Administrative Tools >> ODBC Data Sources (32bit). If you are using 64 bit version, choose the ODBC Data Sources (64bit).
Click Add to add a new DSN. Select “Oracle in instantclient_12_2” from the list. Click Finish. Next, enter the Oracle ODBC Driver Configuration. Give the new Data Source Name, select TNS Service Name and User ID. Click Test Connection to validate the connection to the Oracle Database Server.
Troubleshooting
On my first attempt, I cannot get the list of the TNS Service when creating a new ODBC Connection. To solve this, we need to copy or create the tnsnames.ora in the Oracle Client Directory. Open Notepad and then copy and paste these lines and then save it as tnsname.ora. Save the file in the Oracle Client directory (C:\Oracle\Product\client\instantclient_12_2).
This is the content of my tnsnames.ora which is works well
After that, we need to tell the system to use this tnsnames.ora config file. Open the Environment Variable again and add a new entry with the following value:
- Variable Name: TNS_ADMIN
- Variable Value: C:\Oracle\Product\client\instantclient_12_2
Don’t forget to change the variable value with your Oracle Client Directory.
Now you should be able to pick or select the TNS service. Thanks for reading this how to install Oracle ODBC driver on Windows 10. Cheers
Share this:
Like this:
9 Comments
Mejor explicado no puedo quedar, mil gracias.
You have saved my life!!
Have a good one.
Hi,
I have trouble with install ODBC driver /client on Windows 10 64 bit machine. I have installed Oracle 19c Db on one PC, and from my PC I want connect to it , My PC is Windows 10 64 bit, but because my develeloping application is 32 bit I install ODBC 32 bit. ALso Visual C++ redist.is 32 bit. From ODBC Administrator data source I can create DSN, and also test connection, all works without problems.
But when I run my test program (programmed in Alaska Xbase++ and SQLExpress++ library) connect first after instalation, and no anymore, still get error
= SQL ERROR ===========
Date: 20200528 11:33:55
C:\TESTORACLE\SQLBROW.EXE, Thread: 1
Windows 8 06.02 Build 09200, SQLXpp: 3.3.00, Runtime: 2.00.554
SQLState: IM003, ErrorCode:160
Specified driver could not be loaded due to system error 1114: A dynamic link library (DLL) initialization routine failed. (Oracle in instantclient_19_6, C:\Oracle\Product\Client\instantclient_19_6\SQORA32.dll).
I tryedl everything, and no solution. This looks to first “some” open SQLORA32.DLL and not release it, or I do not know something stay blocked and cannot connect again.
No help reinstall driver, unistal/install, nothing. Interesting is that test connection from ODBC driver assistent still work,only my app cannot connect more than once, sometimes after some delay I connect but again still blocked.
Interesting is that on third PC with also W10 64 bit this works for me that I mean some can be in Windows configuration, some process or dll open and stay opened, or rewrite some parameter in registry, do not know.
Please thanks for any advice.
Эта функция будет удалена в следующей версии Windows. Избегайте использования этого компонента в новых разработках и запланируйте изменение существующих приложений, в которых он применяется. Вместо этого используйте драйвер ODBC, предоставляемый Oracle.
Драйвер Microsoft® ODBC для Oracle позволяет подключать приложение, совместимое с ODBC, к базе данных Oracle. Драйвер ODBC для Oracle соответствует спецификации ODBC, описанной в справочнике программиста по ODBC. он обеспечивает доступ к пакетам PL/SQL, интеграции XA/DTC и Oracle access из службы IIS (IIS).
Oracle РСУБД — это многопользовательская система управления реляционными базами данных, которая работает с различными операционными системами рабочей станции и миникомпутер. IBM-совместимые компьютеры с Microsoft Windows могут взаимодействовать с серверами базы данных Oracle по сети. Поддерживаются следующие сети: Microsoft LAN Manager, NetWare, VINEs, DECNet и любая сеть, поддерживающая TCP/IP.
Драйвер ODBC для Oracle позволяет приложению получать доступ к данным в базе данных Oracle через интерфейс ODBC. драйвер может обращаться к локальным базам данных Oracle или может взаимодействовать с сетью через SQL * Net. На схеме ниже представлены сведения об архитектуре приложения и драйвера.
драйвер ODBC для Oracle соответствует уровню соответствия API 1 и SQL уровня соответствия "ядро". он также поддерживает некоторые функции в уровне соответствия API 2 и большую часть грамматики в ядре и расширенных SQL уровнях соответствия. Драйвер совместим с ODBC 2,5 и поддерживает 32-разрядные системы. Oracle 7.3 x поддерживается полностью; Oracle8 имеет ограниченную поддержку. Драйвер ODBC для Oracle не поддерживает новые типы данных Oracle8 — типы данных в Юникоде, большие двоичные объекты, CLOB и т. д. не поддерживает новую модель реляционных объектов Oracle. Дополнительные сведения о поддерживаемых типах данных см. в разделе Поддерживаемые типы данных этого руководством.
Для доступа к данным Oracle требуются следующие компоненты:
Драйвер ODBC для Oracle
База данных Oracle RDBMS
Клиентское по Oracle
Кроме того, для удаленных подключений:
- Сеть, соединяющая компьютеры, на которых выполняется драйвер и база данных. сеть должна поддерживать подключения SQL * Net.
Документация по компонентам
Это краткое описание содержит подробные сведения о настройке и настройке драйвера Microsoft ODBC для Oracle, а также о добавлении программных функций. Он также содержит технические справочные материалы.
Сведения о конкретном поведении продукта Oracle см. в документации, прилагаемой к продукту Oracle.
Сведения о настройке и настройке драйвера Microsoft ODBC для Oracle с помощью администратора источников данных ODBC см. в документации администратора источников данных ODBC .
Читайте также: