Работа с информацией в базе данных PostgreSQL через базу данных Oracle

    Информационная система многих крупных предприятий имеет целый "зоопарк" приложений написанных с использованием различных технологий. У каждого приложения может быть не только своя база данных, но они могут использовать даже различные СУБД. Это ставит вопрос доступа к информации другого приложения. Для его решения Oracle предоставляет общую технологию для подключения из баз данных Oracle к базам данных других СУБД - Heterogeneous Services (HS). Для каждой СУБД, отличной от Oracle, запускается отдельный процесс, с помощью которого СУБД Oracle подключается к ней – Heterogeneous Services Agent (HS Agent). Этот процесс называется "шлюз" (Oracle Database Gateway). Он состоит из двух частей: общий код агента и драйвер для доступа.

Архитектура гетерогенного процесса подключения Oracle к другим СУБД

Среди драйверов для доступа есть несколько драйверов для конкретных СУБД (MS SQL Server, Sybase, Informix, Teradata, DB2) и универсальный драйвер для доступа через ODBC. Шлюз устанавливается и настраивается только на одном компьютере (и это необязательно компьютер с СУБД Oracle), а клиентское программное обеспечение с различных компьютеров подключается к базе данных Oracle через Oracle Net и работает через это подключение с базой данных других СУБД.
    Давайте пошагово рассмотрим настройку подключения к базе данных PostgreSQL через Oracle Database Gateway for ODBC (DG4ODBC).

Подключение к БД используя шлюз с драйвером для доступа через ODBC

У меня на тестовой виртуальной машине уже был установлен Oracle Database 12c Release 2. Шлюз для ODBC установился с ней в комплекте.

1. Устанавливаем любой 64-х битный ODBC-драйвер для PostgreSQL. Например, можно взять официальный драйвер ODBC для PostgreSQL – psqlODBC (на данный момент для загрузки доступна версия 13.02).
2. Добавляем 64-х битный системный источник данных с драйвером ODBC для PostgreSQL (System DNS).
   
   Имя пользователя и пароль для подключения к базе данных можно не указывать, т.к. его будем указывать позже при создании DATABASE LINK.
   
   На более старых версиях Oracle может потребоваться 32-х битный источник данных, т.к. раньше шлюз был 32-х битным.
3. Настраиваем Oracle Database Gateway for ODBC. В каталоге $ORACLE_HOME\hs\admin создаем файл инициализации для нашего подключения. Его имя должно иметь формат initSID.ora, где SID – это "системный идентификатор шлюза", который будет идентифицировать экземпляр шлюза. Обязательным параметром для PostgreSQL в файле инициализации является только наименование источника данных ODBC – HS_FDS_CONNECT_INFO. Для Linux, в отличие от Windows, еще обязательно необходимо указать путь к файлу ODBC-драйвера (параметр HS_FDS_SHAREABLE_NAME). Что бы не путать с наименованием источника данных ODBC ("PG_TEST_DB"), назовем системный идентификатор шлюза "PG_DB" (хотя мне нравится одинаковое название для всех элементов схемы подключения). В каталоге c:\oracle\product\12.2.0\dbhome_1\hs\admin\ создаем файл initPG_DB.ora:

   HS_FDS_CONNECT_INFO = PG_TEST_DB
   HS_FDS_TRACE_LEVEL = OFF

4. Настраиваем Oracle Net Listener. В параметр SID_LIST_LISTENER конфигурационного файла listener.ora (если не настроена переменная среды TNS_ADMIN, то файл находится в каталоге $ORACLE_HOME\network\admin) необходимо добавить системный идентификатор шлюза. В файл c:\oracle\product\12.2.0\dbhome_1\network\admin\listener.ora добавляем наш SID "PG_DB":

   SID_LIST_LISTENER =
     (SID_LIST =
       (SID_DESC =
         (SID_NAME = CLRExtProc)
         (ORACLE_HOME = C:\oracle\product\12.2.0\dbhome_1)
         (PROGRAM = extproc)
         (ENVS = "EXTPROC_DLLS=ONLY:C:\oracle\product\12.2.0\dbhome_1\bin\oraclr12.dll")
       )
         (SID_DESC=
            (SID_NAME=PG_DB)
            (ORACLE_HOME=c:\oracle\product\12.2.0\dbhome_1)
            (PROGRAM=dg4odbc)
         )
     )

   и перезапускаем listener.
   
5. Создаем DATABASE LINK. Первый вариант – с настройкой SQL*Net подключения. В конфигурационный файл tnsnames.ora (если не настроена переменная среды TNS_ADMIN, то файл находится в каталоге $ORACLE_HOME\network\admin) необходимо добавить маппирование имени для системного идентификатора шлюза. В файл c:\oracle\product\12.2.0\dbhome_1\network\admin\tnsnames.ora добавляем TNS-псевдоним TNS_PG_DB связанный с PG_DB (префикс "TNS" снова для того, чтобы было нагляднее).

   TNS_PG_DB =
     (DESCRIPTION =
       (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
       (CONNECT_DATA = (SERVICE_NAME = PG_DB))
       (HS = OK)
     )

   Обратите внимание на "(HS = OK)" – это указывает на то, что подключение использует Oracle Heterogeneous Services. Создаем DATABASE LINK выполнив команду с любого компьютера, на котором настроен доступ к базе данных Oracle:

   create database link LINK_TO_PG
     connect to "pg_user"
     identified by pg_user_password 
     using 'TNS_PG_DB';

   где LINK_TO_PG – наименование DATABASE LINK'а, pg_user – имя пользователя PostgreSQL имеющего доступ к нужным таблицам, pg_user_password – пароль пользователя PostgreSQL, TNS_PG_DB – TNS-псевдоним указанный в tnsnames.ora компьютера с настроенным Oracle Database Gateway.
   Второй вариант проще, так как не требует настройки tnsnames.ora. В команде создания DATABASE LINK'а просто пропишем параметры TNS-псевдонима:

   create database link LINK_TO_PG
     connect to "pg_user"
     identified by pg_user_password
     using '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=PG_DB))(HS=OK))';

6. Проверяем работу. В базе данных PostgreSQL от имени pg_user создаем тестовую таблицу.

   CREATE TABLE users
   (
      id         INT           not null,
      fio        VARCHAR(200)  not null,
      login_name VARCHAR(25)   not  null,
      note       VARCHAR(2000) null,
      CONSTRAINT pk_users PRIMARY KEY (id)
   );
   
   INSERT INTO users (id, fio, login_name, note) 
   VALUES 
     (1, 'Администратор', 'admin', NULL),
     (2, 'Иванов Иван Иванович', 'ivanov', 'Директор'),
     (3, 'Петров Петр Петрович', 'petrov', 'Программист');

   На компьютере, на котором настроен доступ к базе данных Oracle, выполняем запрос к таблице в базе данных PostgreSQL:
   
   Как вы видите, у нас проблема с кодировкой. Ее причина кроется не в настройках NLS_LANG или других параметров Oracle Database, даже не в PostgreSQL. Как обычно, виновата MS Windows. Для решения проблемы с кодировкой в региональных настройках MS Windows для приложений, которые не используют unicode, необходимо установить русский язык. Заходим в Control Panel => Clock, Language, and Region => Region => Administrative => Language for non-Unicode programs и меняем локаль с "English" на "Russian".
   
   После перезагрузки компьютера выполняем запрос снова.
   
   Обратите внимание, что имя пользователя при создании DATABASE LINK и все идентификаторы в запросе взяты в двойные кавычки. Это связано с тем, что согласно стандарту SQL, идентификаторы по умолчанию должны приводятся к верхнему регистру. Но разработчики PostgreSQL нарушили это правило и приводят их к нижнему. Поэтому, если не взять идентификатор в двойные кавычки, то СУБД преобразует его наименование, и мы получим ошибку "ORA-00904: недопустимый идентификатор".

    Как вы видите, организация доступа к информации в базе данных PostgreSQL (как и в любой другой СУБД) через Oracle Database Gateway требует минимальных настроек. При этом разработчики получают в свои руки мощный инструмент, который берет на себя не только организацию выполнения простых SQL-запросов на получение данных из других баз, но и возможность выполнения гетерогенных запросов (когда таблицы запроса находятся в разных базах данных), а также позволяет модифицировать данные с поддержкой механизма транзакций.

---

Продолжение:

• Установка и настройка Oracle Database Gateway for ODBC (DG4ODBC) для доступа к БД PostgreSQL на компьютере, на котором не установлена СУБД Oracle