Подключение oracle к ms sql

Обновлено: 25.06.2024

Необходимо обращаться к данным SQL Server из PL/SQL с применением технологии DB Link.

Есть два варианта создания подключения: использовать Oracle Transparent Gateway или ODBC. Мы используем ODBC.

Установка Microsoft ODBC Driver for SQL Server сводится к добавлению репозитория, установке пакетов и настройке переменных окружения

sudo ACCEPT_EULA=Y yum install msodbcsql17

sudo ACCEPT_EULA=Y yum install mssql-tools

В нашем случае есть специальный файл с переменными окружениями пользователя oracle

echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> /etc/profile.d/oraee.sh

Файл /etc/odbcinst.ini с описанием установленных в систему драйверов ODBC обновится автоматически при установке. Имя драйвера изменено на MSODBC специально для удобства использования.

Файл c описанием DSN (имя источника данных) должен содержать описание каждой базы данных к которой необходимо подключаться. На этом этапе мы определяем имя подключения как GALSQL, используем адрес сервера и имя базы данных

[GALSQL]
Driver = MSODBC
Description = GAL MSSQL Server
Trace = No
Server = 10.2.1.108
Database = GALERP
Port = 1433
QuotedId=YES
AnsiNPW=YES

Источник данных определён и настроен для использования в операционной системе, можно подключиться консольным клиентом.

Для Oracle необходимо настроить Heterogeneous Services, tnsname и listener.

Heterogeneous Services настраивается в $ORACLE_HOME/hs/admin/ Необходимо создать файл описания к каждой базе, к которой создается подключения. Именование файла вида init[ODBC Data Sources].ora, пример: initGALSQL.ora

listener.ora tnsnames.ora размещены в $ORACLE_HOME/network/admin

Необходимо добавить сервисы и перезапустить listener

***
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = GALSQL)
(PROGRAM = dg4odbc)
(ORACLE_HOME = /opt/oracle/product/19c/dbhome_1)
)
)

***
GALSQL =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=oradb18)(PORT=1521))
(CONNECT_DATA=(SID=GALSQL))
(HS=OK)
)

Перезапуск и вся остальная работа в Oracle с правами пользователя CREATE DATABASE LINK

CREATE DATABASE LINK "DBLINK_GALSQL"
CONNECT TO "gal-api-user" IDENTIFIED BY "SECRET_PASSWORD"
USING 'GALSQL';

В этом разделе показано, как подключаться к источникам данных Oracle со страницы Выбор источника данных или Выбор назначения в мастере импорта и экспорта SQL Server. Для подключения к Oracle можно использовать ряд поставщиков данных.

Подробные требования и необходимые условия для подключения к базе данных Oracle выходят за рамки этой статьи Майкрософт. В ней предполагается, что у вас уже установлено клиентское программное обеспечение Oracle и вы можете успешно подключиться к целевой базе данных Oracle. Для получения дополнительных сведений обратитесь к администратору базы данных Oracle или к документации по Oracle.

Параметры подключения для этого поставщика данных одинаковы независимо от того, является ли Oracle источником или назначением. Таким образом, на страницах Выбор источника данных и Выбор назначения мастера отображаются одинаковые параметры.

Вам не нужно вводить строку подключения в поле ConnectionString списка. После ввода отдельных значений для имени сервера Oracle (источника данных) и информации для входа мастер собирает строку подключения из отдельных свойств и их значений.

Подключение к Oracle с помощью драйвера Microsoft ODBC для Oracle

Указываемые параметры (драйвер ODBC для Oracle)

Параметры подключения для этого поставщика данных и драйвера ODBC одинаковы независимо от того, является ли сервер Oracle источником или назначением. Таким образом, на страницах Выбор источника данных и Выбор назначения мастера отображаются одинаковые параметры.

Чтобы подключиться к Oracle с помощью драйвера ODBC для Oracle, соберите строку подключения, используя указанные ниже параметры и их значения. Полный формат строки подключения приведен после списка параметров.

Вы можете получить помощь в построении строки подключения. Кроме того, вместо указания строки подключения вы можете предоставить существующее имя DSN (имя источника данных) или создать новое. Дополнительные сведения об этих возможностях см. в разделе Подключение к источнику данных ODBC.

Драйвер
Имя драйвера ODBC — Microsoft ODBC for Oracle.

Server
Имя сервера Oracle.

Uid и Pwd
Идентификатор пользователя и пароль для подключения.

Формат строки подключения

Ниже приведен формат типичной строки подключения.

Ввод строки подключения

Введите строку подключения в поле ConnectionString либо введите имя DSN в поле Dsn на странице Выбор источника данных или Выбор назначения. После того как вы введете строку подключения, мастер проанализирует ее и отобразит отдельные свойства и их значения в списке.

Ниже показан экран, который появляется после ввода строки подключения.

Какое имя у моего сервера Oracle?

Выполните один из следующих запросов, чтобы узнать имя сервера Oracle.

SELECT host_name FROM v$instance

SELECT sys_context('USERENV','SERVER_HOST') FROM dual

Другие поставщики данных и дополнительные сведения

Сведения о подключении к Oracle с помощью поставщика данных, не представленного в этом списке, см. в разделе Строки подключения Oracle. Этот сторонний сайт также содержит дополнительные сведения о поставщиках данных и параметрах подключения, описанных на этой странице.

Для соединения с БД необходимо использовать класс Connection пакета java.sql. После установления соединения можно выполнять различные SQL-запросы и получать результаты их обработки сервером. Приложение может открыть одно или несколько соединений с одной или несколькими различными СУБД.

Стандартный способ установления соединения - это вызов метода getConnection класса DriverManager. Методу getConnection необходимо передать строку URL (Uniform Resource Locator). Стандарт подключения к серверу базы данных позволяет использовать следующие методы getConnection с параметрами:

При использовании первого варианта, все параметры подключения указываются в url.

В следующей таблице представлен JDBC driver и синтаксис строки URL для подключения к СУБД.

СУБД	Драйвер JDBC	URL
Oracle	oracle.jdbc.OracleDriver	jdbc:oracle:oci[OCI_VERSION]:@[HOST_NAME]
Oracle	oracle.jdbc.OracleDriver	jdbc:oracle:thin:@ [HOST_NAME]:[PORT_NUMBER]:[DATABASE_NAME]
MSSQL	com.microsoft.jdbc.sqlserver.SQLServerDriver	jdbc:microsoft:sqlserver: //[HOST_NAME]:[PORT_NUMBER]
PostgreSQL	org.postgresql.Driver	jdbc:postgresql: //[HOST_NAME]:[PORT_NUMBER] /[DATABASE_NAME]
MySQL	com.mysql.jdbc.Driver	jdbc:mysql://[HOST_NAME]:[PORT_NUMBER] /[DATABASE_NAME]

Подключение к СУБД Oracle, jdbc oracle

К серверу БД Oracle можно подключиться как через тонкий клиент, так и через толстый.

Пример подключения через тонкий клиент:

При подключении через толстый клиент, host и port не указываются :

Подключение к СУБД MS SQL, jdbc sql

Строка URL-адреса подключения имеет следующий вид:

jdbc:sqlserver:// - обязательно, подпротокол.
serverName - необязательно, адресом сервера, с которым выполняется соединение. Это может быть DNS, IP-адрес, локальный узел или 127.0.0.1 локального компьютера. Имя сервера необходимо указать в коллекции свойств, если оно не указано в URL-адресе соединения.
instanceName - необязательно, наименование базы данных сервера, с которым выполняется соединение. Подключение выполняется к базе данных по умолчанию, если не указана другая.
portNumber - необязательно, порт, который слушает сервер. Значение по умолчанию — 1433. Если соединение выполняется с портом по умолчанию, то в URL-адресе необязательно указывать порт.

Дополнительные свойства указывается в формате ключ=значение.

Примеры установления соединения с сервером БД

Подключение к СУБД PostgreSQL, jdbc postgresql

Для подключения к серверу БД PostgreSQL необходимо использовать один из следующих URL:

host - имя сервера, по умолчанию "localhost".

port - порт, который слушает сервер. По умолчанию используется порт 5432.

database - наименование базы данных. По умолчанию используется база данных postgres.

Примеры jdbc postgresql :

Подключение к СУБД MySQL, jdbc mysql

Пример динамической загрузки JDBC-драйвера и подключение к серверу рассмотрен здесь.

Задачу по загрузке данных из Oracle и их записи в таблицу на MS SQL Server можно решить большим количеством способов. Как правило, даже для выполнения простого запроса приходится потратить большое количество времени, особенно, когда надо передать в Oracle перечень ID для поиска информации. Для решения задачи можно загрузить данные в файлы, затем написать код создания таблицы и в нее с помощью BCP загрузить информацию, либо использовать Мастер импорта и экспорта.

На самом деле, получить данные и создать для них таблицу на MS SQL Server можно используя Python, при этом весь процесс возможно заложить в одну функцию. В такую функцию необходимо заложить следующие алгоритмы:

загрузка данных с Oracle;
автоматическое определение типов столбцов для таблицы на MS SQL Server на основании типов столбцов Oracle;
создание таблицы и вставка в нее полученной с Oracle информации.

Назовем эту функцию oracle_download и определим для нее перечень входящих переменных:

table_name – наименование таблицы, в которой мы хотим видеть полученные из Oracle данные;
ids_sql – запрос для получения перечня ID, которые будут переданы в запросе к Oracle;
ids_column – наименование столбца, содержащего ID из запроса в переменной ids_sql;
sql_mask – запрос к Oracle.

В рассматриваемом примере в Oracle будет передаваться перечень ID, содержащий числовые значения. Ниже представлен код для импорта необходимых библиотек, определения строк подключения и указания входных переменных для функции.

Сразу определим функцию для разделения перечня ID на части по несколько штук, так как передавать более 1000 в IN нельзя.

Теперь перейдем непосредственно к написанию кода функции.

Для загрузки данных из таблиц MS SQL Server используется функция read_sql и полученный перечень ID делится на несколько частей, например, по 100 ID (как в коде ниже) для подстановки в запрос к Oracle.

Чтобы было удобнее отслеживать процесс загрузки данных, определим значение переменной step, и для создания таблицы статус ее наличия на MS SQL Server.

Теперь необходимо загрузить данные с Oracle, создать для них таблицу на MS SQL Server и записать в нее данные. Ниже представлен полный код написанной функции (с кодом, представленным ранее):

Разберемся, что же происходит, после получения перечня ID с MS SQL Server. После загрузки первого результата с Oracle проверяется наличие таблицы в MS SQL Server с помощью команды OBJECT_ID. Результат проверки записывается в переменную table_exists. Если таблицы нет, она создается с помощью кода далее.

Получив типы всех столбцов создается таблица для загруженных данных командой create table. Для вставки используется конструкция insert into [table] values (…), (…), (…), при этом вставка данных выполняется частями автоматически, как и загрузка с Oracle.

В результате, с помощью одной небольшой функции можно загрузить данные с Oracle, создать для них таблицу на MS SQL Server и записать их в нее:

За время работы с Oracle Database и Microsoft SQL Server Integration Services я собрал 2 FAQ-а. Первый — по Oracle Client — опубликован предыдущим постом, а здесь следующий — по коннекторам SSIS к Oracle.

Нужен ли Oracle Client?

Да, все описанные ниже коннекторы используют Oracle Client.

Что такое Devart dotConnect?

Какую редакцию dotConnect надо взять, чтобы работать из SSIS?

Возьмите редакцию Express, которая предоставляется бесплатно.

Что за предупреждение про Devart.Data.dll при установке dotConnect for Oracle и dotConnect for MySQL?

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

Это означает, что имеет смысл скачать и установить на машину с интеграционными службами SQL Server коннекторы сразу ко всем СУБД, с которыми в условиях организации возможно интегрироваться, что бы потом (при установке очередного коннектора) не обновлять уже установленные коннекторы и не вносить дополнительные риски.

Что такое Microsoft Connector for Oracle by Attunity?

Attunity выпускает для Microsoft, а Microsoft бесплатно предоставляет для пользователей SQL Server Enterprise Edition компоненты SSIS для извлечения и вставки в СУБД Oracle под названием Microsoft Connector for Oracle by Attunity. Технически, компоненты работают в SSIS во всех редакциях SQL Server, а не только в Enterprise.

Существует ещё Microsoft Connector for Teradata by Attunity.

Что быстрее работает - dotConnect или Connector by Attunity?

Вот результаты тестов в секундах между СУБД Oracle 10g и SQL Server 2012 Developer Edition на таблице, содержащей 126 828 записей (наш Oracle под рабочей нагрузкой):

Разумеется, для строк разного размера и на различающемся оборудовании результаты будут различаться — мои можно использовать как ориентировочные при настройках пакета SSIS по умолчанию (об этих настройках ниже).

О влиянии настроек

В 2015-м году один из моих читателей сообщил, что при тестировании загрузки его данных с помощью Attunity и dotConnect из Oracle 9 (с использованием SSIS из SQL Server 2012) он добился почти паритета Attunity и dotConnect за счёт настроек.

При DefaultBufferMaxRows = 50 000 и BatchSize = 50 000 для источника Oracle от Attunity, на широкой таблице с большим количеством полей, в том числе больших текстовых полей, Attunity проигрывал всего 0,7 %, а на узкой, с большим количество записей и одним текстовым полем в 20, символов Attunity проигрывал менее 8,2 %.

При настройках по умолчанию, Attunity проигрывал dotConnect-у в 6 раз.

Какие ещё есть варианты подключения к Oracle из SSIS?

Все остальные варианты хуже чем dotConnect и Microsoft Connector for Oracle by Attunity — они обладают худшей поддержкой и меньшей скоростью.

Вот весь перечень известных нам компонентов:

Почему русские буквы в данных, извлекаемых из Oracle, заменяются на непонятные символы?

В Devart dotConnect for Oracle, можно в настройках подключения установить Unicode = true и не зависеть от кодировки клиента.

Но, в любом случае, рекомендуется настроить кодировку Oracle Client в ОС Windows на 1251 - смотрите FAQ по Oracle Client.

Использовать прямой режим dotConnect или работать через Oracle Client?

Рекомендуется работать через Oracle Client, в частности потому, что Oracle Client корректно работает с единой транзакцией на пул подключений.

Это означает, что если в процессе работы SSIS использует 100 подключений к Oracle, то все они будут находиться в одной транзакции с точки зрения Oracle, открываемой первым подключением. В прямом режиме, каждое подключение будет в своей собственной транзакции, что ухудшает согласованность данных.

Читайте также: