Oracle data access components for windows 64 bit

В Microsoft SQL Server есть возможность обращения к различным источникам данных, которые расположены вне SQL сервера, это возможно благодаря технологии связанных серверов и сегодня мы с Вами рассмотрим пример настройки связанного сервера с СУБД Oracle.

Подробно о том, что такое связанный сервер, а также простые примеры настройки связанных серверов мы с Вами рассматривали в материале – Связанные серверы в MS SQL Server 2014.

Сейчас как я уже сказал, мы будем настраивать связанный сервер, источником данных которого будет выступать СУБД Oracle.

Примечание! Для примера я буду использовать Microsoft SQL Server 2016 Express, а версия Oracle у меня Oracle Database Express Edition 11g.

Прежде чем начать хотелось бы сказать, что для настройки связанных серверов используются так называемые «Провайдеры» или «Поставщики», которых на самом деле много. Для связи с Oracle существует несколько поставщиков, например:

• Microsoft OLE DB Provider for Oracle (MSDAORA) – как не трудно догадаться его разработчиком является компания Microsoft;
• Oracle Provider for OLE DB (OraOLEDB.Oracle) – данный провайдер разработала сама компания Oracle для связи со своей СУБД.

Все рекомендуют использовать провайдер от Oracle, в том числе и я, основываясь на собственном опыте. Так как OraOLEDB.Oracle более оптимизирован для работы с Oracle, например, как-то раз при использовании провайдера MSDAORA текстовые данные на русском языке (VARCHAR2) с сервера Oracle приходили мне в виде «?????», после недолгого шаманства пришлось сменить провайдера.

Поэтому сегодня мы будем рассматривать пример настройки связанного сервера с использованием Oracle Provider for OLE DB (OraOLEDB.Oracle) от компании Oracle.

Практически для любого поставщика требуются какие-то инструменты, чтобы он функционировал, в том числе и для OraOLEDB.Oracle.

Для того чтобы провайдер OraOLEDB.Oracle работал его необходимо установить, предварительно скачав с официального сайта Oracle.

Как скачать Oracle Provider for OLE DB (OraOLEDB.Oracle)?

Oracle Provider for OLE DB на текущий момент распространяется в составе инструмента для разработчиков ODAC.

Oracle Data Access Components (ODAC) – это набор компонентов для разработчиков, который предоставляет им доступ к данным Oracle.

Чтобы его скачать, необходимо зарегистрироваться на сайте Oracle или если Вы уже зарегистрированы, то войти в аккаунт. Затем переходите на страницу загрузки ODAC вот она.

Потом в зависимости от Вашей системы (т.е. на которой установлен MS SQL Server), Вы выбираете 32 битную версию или 64 битную. У меня система 64 битная, поэтому я нажимаю на ссылку 64-bit ODAC Downloads.

После перехода на следующую страницу сразу соглашаетесь с условиями лицензионного соглашения, т.е. отмечаете пункт «Accept License Agreement».

Далее Вы выбираете версию ODAC, которая соответствует версии СУБД Oracle.

При этом название файлов с префиксом «Xcopy» означает, что установка будет осуществляться посредством пакетных файлов (батников). Если такого префикса нет, то в данном архиве присутствует некий дистрибутив с графическим интерфейсом.

Итак, как я уже сказал у меня версия Oracle Express Edition 11g, поэтому я выбираю 64-bit ODAC 11.2 Release 5 (11.2.0.3.20) for Windows x64 – это вариант с установкой через графический интерфейс.

В итоге у меня загрузился файл ODAC1120320_x64.zip

Установка Oracle Data Access Components (ODAC)

Распаковываем архив и запускам файл setup.exe.

Шаг 1

Запустится программа установки ODAC, на первом окне мы нажимаем «Next».

Шаг 2

Затем оставляем по умолчанию, т.е. нам нужна установка клиентской части, жмем «Next».

Шаг 3

Далее, если хотите, можете указать каталог для установки ODAC, я предварительно создал папку «OracleOleDB» на диске C специально для этих целей, поэтому я ее и выбираю, жмем «Next».

Шаг 4

Теперь нам необходимо выбрать компоненты, которые мы хотим установить, в нашем случае нам нужен только Oracle Provider for OLE DB и, конечно же, Oracle Instant Client, с остальных компонентов снимаем галочку и жмем «Next».

Шаг 5

Проверяем все параметры установки и жмем «Install».

В итоге начнется процесс установки.

Он будет завершен, когда появится следующее сообщение, жмем «Exit».

Шаг 6

После того как установился провайдер Oracle Provider for OLE DB нам необходимо в каталог «C:OracleOleDBproduct11.2.0client_1NetworkAdmin» (в моем случае) скопировать файл tnsnames.ora с сервера Oracle (из каталога …networkADMIN) или создать самим такой файлик и вписать в него настройки подключения, которые нам сообщит администратор Oracle, например, у меня для Express Edition они вот такие.

   
       XE =
          (DESCRIPTION =
                (ADDRESS = (PROTOCOL = TCP)(HOST = TestComp)(PORT = 1521))
                (CONNECT_DATA =
                  (SERVER = DEDICATED)
                  (SERVICE_NAME = XE)
                )
          )

        EXTPROC_CONNECTION_DATA =
          (DESCRIPTION =
                (ADDRESS_LIST =
                  (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
                )
                (CONNECT_DATA =
                  (SID = PLSExtProc)
                  (PRESENTATION = RO)
                )
          )

        ORACLR_CONNECTION_DATA = 
          (DESCRIPTION = 
                (ADDRESS_LIST = 
                  (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1)) 
                ) 
                (CONNECT_DATA = 
                  (SID = CLRExtProc) 
                  (PRESENTATION = RO) 
                ) 
          )

В данном каталоге есть папка Sample, в которой есть пример заполнения файла tnsnames.ora.

После установки Oracle Provider for OLE DB необходимо перезагрузить сервер.

Проверка работы провайдера OraOLEDB.Oracle

Перед тем как переходить к созданию связанного сервера на Microsoft SQL Server необходимо проверить правильно ли мы установили провайдера. Это можно сделать следующим образом. Создайте на рабочем столе простой текстовый файл, например, TestConnect.txt, затем измените его расширение на TestConnect.udl

Запустите его, и у Вас откроется окно «Свойства канала передачи данных». Далее Вам необходимо на вкладке «Поставщик данных» выбрать поставщика, т.е. в нашем случае это «Oracle Provider for OLE DB» и нажать «Далее».

После чего Вы перейдете на вкладку «Соединение», где нужно заполнить параметры подключения:

• Источник данных — адрес сервера Oracle;
• Пользователь – это пользователь для подключения к СУБД Oracle;
• Пароль – соответственно пароль для подключения к СУБД Oracle.

И для проверки связи нажимаем «Проверить соединение».

Если у Вас появилось сообщение «Проверка соединения выполнена», то можете переходить к созданию связанного сервера, если нет, то у Вас что-то с провайдером или с сервером Oracle.

Создание связанного сервера с использованием провайдера OraOLEDB.Oracle

Для того чтобы создать связанный сервер в Microsoft SQL Server запустите среду Management Studio.

Сначала давайте у провайдера изменим один параметр под названием «AllowInProcess» или «Допускать в ходе процесса», так как этого требует провайдер OraOLEDB.Oracle. В остальных случаях не рекомендуется использовать данную опцию, так как в этом случае если произойдет сбой провайдера, то и весь SQL сервер даст сбой.

Для этого в обозревателе объектов найдите контейнер «Объекты сервера -> Связанные серверы-> Поставщики», откройте его и найдите наш провайдер OraOLEDB.Oracle. Щелкните правой кнопкой мыши и откройте «Свойства».

Отметьте вышеуказанный параметр, т.е. «Допускать в ходе процесса» и нажмите «ОК».

Теперь переходим непосредственно к созданию так называемого Linked-сервера.

Щелкаем правой кнопкой мыши по контейнеру «Связанные серверы» и выбираем «Создать связанный сервер».

Затем вводим параметры связанного сервера, а именно:

• Связанный сервер – имя сервера, т.е. название объекта к которому мы будем обращаться из своих SQL инструкций;
• Поставщик – провайдер, т.е. Oracle Provider for OLE DB;
• Название продукта – Oracle;
• Источник данных – это SID, к которому мы будем подключаться на сервере Oracle (должен быть прописан в файле TNSNAMES.ORA);

Потом переходим на вкладку «Безопасность», выбираем пункт «Устанавливать с использованием следующего контекста безопасности» и вводим логин и пароль от СУБД Oracle. Жмем «ОК».

Все готово, связанный сервер мы создали, и он у нас отобразился в списке связанных серверов в Management Studio. Теперь мы можем обращаться (посылать запросы) к этому связанному серверу, например, у меня на сервере Oracle есть таблица ORACLETABLE, которая содержит просто какой-то список товаров, и для того чтобы осуществить выборку данных из этой таблицы с помощью Management Studio SQL сервера, можно использовать следующие запросы.

   
        SELECT * FROM ORACLEDB..ORACLEUSER.ORACLETABLE
        --Или так
        SELECT * FROM OPENQUERY(ORACLEDB,
                                                        'SELECT * FROM ORACLETABLE')

Если Вы предпочитаете пользоваться SQL инструкциями, то ниже представлена инструкция, которая делает ровно то же самое что и мы чуть ранее в графическом интерфейсе SSMS.

   
        USE [master]
        GO
        EXEC sp_MSset_oledb_prop N'OraOLEDB.Oracle', N'AllowInProcess', 1
        GO
        EXEC sp_addlinkedserver @server = N'ORACLEDB', 
                                                        @srvproduct=N'Oracle', 
                                                        @provider=N'OraOLEDB.Oracle', 
                                                        @datasrc=N'XE'
        GO
        EXEC sp_addlinkedsrvlogin @rmtsrvname = N'ORACLEDB', 
                                                          @locallogin = NULL , 
                                                          @useself = N'False', 
                                                          @rmtuser = N'ORACLEUSER', 
                                                          @rmtpassword = N'Pa$$w0rd'
        GO 
        SELECT * FROM OPENQUERY(ORACLEDB,
                                                        'SELECT * FROM ORACLETABLE')

Ошибка 7302

«Не удалось создать экземпляр поставщика OLE DB «OraOLEDB.Oracle» для связанного сервера …»

Возможно, отсутствует (или некорректный) путь к каталогу с Instant Client (каталог в который Вы установили ODAC) в системной переменной PATH. Необходимо его прописать и перезапустить SQL сервер. Также возможно параметр ORACLE_HOME задан неверно.

Ошибка 7303

Данная ошибка возвращается SQL сервером в случае, когда не удалось проинициализировать объект источника данных поставщика OLE DB, например, в нашем случае «OraOLEDB.Oracle». Причин, по которым она появляется много, поэтому нам необходимо смотреть ошибку, которую вернул сам провайдер.

Ошибки при инициализации провайдера OraOLEDB.Oracle

Описание ошибки	Возможные причины, устранение
ORA-01017: invalid username/password; logon denied	Неправильно указан логин и пароль к БД Oracle.
ORA-12154: TNS:could not resolve the connect identifier specified	Файл tnsnames.ora отсутствует или в нем указан несуществующий SID и SERVICE_NAME. Также возможно Вы неправильно ввели его при создании связанного сервера в строке «Источник данных».
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor	В файле tnsnames.ora неправильно введен SERVICE_NAME
ORA-12533: TNS:illegal ADDRESS parameters	Некорректное название параметров в файле tnsnames.ora (например, ADDRESS).
ORA-12541: TNS:no listener	Ошибка означает, что у Вас в файле tnsnames.ora неправильно указан PORT, на котором работает сервер Oracle. Необходимо узнать на каком порту работает сервер Oracle, и указать его в этом параметре.
ORA-12545: Connect failed because target host or object does not exist	Данная ошибка возвращается тогда, когда в файле tnsnames.ora неправильно указан (или просто недоступен) сервер Oracle, т.е. параметр HOST. В данном случае необходимо: проверить данный параметр, проверить доступность сервера.

В случае если у Вас в каких-нибудь сообщениях выскакивает провайдер OLE DB «MSDAORA» (или любой другой провайдер или вообще он не указан), то это означает, что Вы неправильно выбрали провайдера при создании связанного сервера. MSDAORA – это провайдер от компании Microsoft. OraOLEDB.Oracle – от компании Oracle, как я говорил, рекомендовано использовать провайдер от Oracle.

Теперь давайте рассмотрим несколько ошибок, которые могут возникнуть уже после создания связанного сервера, т.е. связанный сервер создан, но при обращении к его объектам возникает ошибка.

Ошибка 7314

«Поставщик OLE DB «OraOLEDB.Oracle» для связанного сервера «Название сервера» не содержит таблицы «Название таблицы». Таблица либо не существует, либо текущий пользователь не имеет разрешения на доступ к ней.»

Здесь Вы, наверное, ошиблись в написании самого объекта, например, написали имя объекта в неправильном регистре. Oracle чувствителен к регистру, если к нему обращаться через ссылку связанного сервера, например

   
    SELECT * FROM ORACLEDB..ORACLEUSER.OracleTable

В этом случае если таблица OracleTable на сервере Oracle создана как ORACLETABLE, у Вас возникнет ошибка. Чтобы этого избежать, можно указать правильный регистр или использовать конструкцию OPENQUERY, например

   
    SELECT * FROM OPENQUERY(ORACLEDB, 'SELECT * FROM OracleTable')

Также не исключено что объекта действительно нет или у Вас отсутствуют необходимые разрешения.

Для того чтобы посмотреть, какие объекты и в каком регистре есть на сервере (т.е. узнать точное имя) можно использовать процедуру sp_tables_ex, например

   
    EXEC sp_tables_ex @table_server=«Имя связанного сервера», @table_schema='«Имя пользователя»'

Ошибка 7356

«Поставщик OLE DB «MSDAORA» для связанного сервера «Название сервера» предоставил несогласованные метаданные для столбца.»

Данную ошибку я встречал у поставщика MSDAORA, когда пытался обратиться к таблице, в которой нет данных, т.е. она пустая (нет строк). Эту ошибку можно избежать, если использовать конструкцию OPENQUERY.

Ошибка 7357

«Не удалось обработать объект «Название объекта». Поставщик OLE DB «OraOLEDB.Oracle» для связанного сервера «Название сервера» обнаружил, что у объекта либо нет ни одного столбца, либо текущий пользователь не имеет разрешения на доступ к объекту.»

В данном случае скорей всего Вы пытаетесь обратиться к таблице (или другому объекту) от имени пользователя, который не имеет разрешения на доступ к ней. Другими словами, у пользователя, которого Вы указывали при создании связанного сервера, нет необходимых прав.

Заметка! Если Вас интересует язык SQL, рекомендую почитать мою книгу «SQL код», которая ориентирована на изучение SQL как стандарта, после прочтения книги Вы сможете писать SQL запросы в любой системе управления базами данных.

На этом у меня все, пока!

Installing the Oracle Instant Client will save you ~600 megabyes compared to the ‘full’ Oracle Client.

Installation steps:

1. Download Basic Package from the Oracle site
2. Unzip to directory x (your ORACLE_HOME)
3. Set PATH (Windows) or LD_LIBRARY_PATH (*nix) to the Instant Client directory x
4. Move TNSNAMES.ora to directory x/network/admin (or set TNS_ADMIN and ORACLE_HOME).
5. Extend with SQL*Plus Package (optional)
6. Start your application

Read my full article here: Whiteblog – Installing the Oracle Instant Client. (links in referred blog might not work!)

ODAC

OLE DB, .NET and ASP.NET drivers are not included in the instant client (first Oracle link might suggest it is). These are included in the ODAC package, download them separately at these locations:

• 64-bit: 64-bit Oracle Data Access Components (ODAC) Downloads
• 32-bit: 32-bit Oracle Data Access Components (ODAC) Downloads

Instant Client vs Full Client (Administrator and Runtime)

What Are The Different Oracle Client Components Installed With Different Installation Types (too much to sum up):

• 11.2.0.2 or 11.2.0.3 (Doc ID 1397346.1)
• 12.1.0.1.0 (Doc ID 1610994.1)

Client master note

Master Note For Oracle Database Client Installation (Doc ID 1157463.1)

Happy consulting!

Checked for relevance on November 4th, 2019.