Вызов процедур, функций и других SQL-команд PostgreSQL из базы данных Oracle

    Oracle Heterogeneous Services позволяет легко организовать доступ из базы данных Oracle к информации в базах данных других СУБД. Настройка Oracle Database Gateway и создание DATABASE LINK занимает несколько минут. Но использование DATABASE LINK и Oracle Database Gateway накладывает на SQL-команду ряд существенных ограничений:

• можно выполнять только команды DML (SELECT, INSERT, UPDATE и DELETE);
• синтаксис команд должен быть эквивалентным синтаксису СУБД Oracle;
• нельзя из удаленной базы данных вызвать процедуры и функции.

    Все перечисленные выше проблемы можно решить с помощью PL/SQL пакета DBMS_HS_PASSTHROUGH.

Этот пакет предоставляет API для отправки SQL-команд через Heterogeneous Services непосредственно в базу данных отличную от Oracle без интерпретации их сервером Oracle.

Процедура/Функция	Описание
OPEN_CURSOR	Открывает курсор
CLOSE_CURSOR	Закрывает курсор и освобождает связанную с ним память
PARSE	Анализирует SQL-команду в удаленной базе данных
BIND_VARIABLE	Связывает IN параметр SQL-команды с константой/переменной PL/SQL
BIND_VARIABLE_RAW	Версия BIND_VARIABLE для типа RAW
BIND_OUT_VARIABLE	Связывает OUT параметр SQL-команды с переменной PL/SQL
BIND_OUT_VARIABLE_RAW	Версия BIND_OUT_VARIABLE для типа RAW
BIND_INOUT_VARIABLE	Связывает IN OUT параметр SQL-команды с переменной PL/SQL
BIND_INOUT_VARIABLE_RAW	Версия BIND_INOUT_VARIABLE для типа RAW
FETCH_ROW	Извлекает строки из результатов SQL-команды
GET_VALUE	Извлекает значение столбца из строки запроса или значение OUT параметра
GET_VALUE_RAW	Версия GET_VALUE для типа RAW
EXECUTE_NON_QUERY	Выполняет SQL-команду не являющуюся запросом
EXECUTE_IMMEDIATE	Выполняет SQL-команду не являющуюся запросом без связывания параметров

Процедуры и функции пакета DBMS_HS_PASSTHROUGH

    Рассмотрим использование пакета DBMS_HS_PASSTHROUGH на примерах. В качестве удаленной базы данных у меня будет база данных PostgreSQL, подключение к которой организованно через Oracle Database Gateway for ODBC. Настройку шлюза и создание DATABASE LINK я описывал в предыдущих статьях.

    Начнем с самой простой функции – EXECUTE_IMMEDIATE. Можно сказать, что эта функция – синтаксический сахар. Она объединяет в себе сразу несколько команд пакета DBMS_HS_PASSTHROUGH, которые необходимо вызвать: OPEN_CURSOR, PARSE, EXECUTE_NON_QUERY и CLOSE_CURSOR. Создадим с ее использованием в базе данных PostgreSQL таблицу "new_table".

DECLARE
  v_result BINARY_INTEGER;
BEGIN
  v_result := DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@PG_DB('CREATE TABLE new_table (id SERIAL, name TEXT not null, CONSTRAINT pk_new_table PRIMARY KEY (id))');
  COMMIT;
END;

Пояснения к коду:

1. PG_DB – это наименование DATABASE LINK.
2. Типы полей SERIAL и TEXT специфичны для PostgreSQL, поэтому позволяют продемонстрировать, что Oracle не обрабатывает посылаемую через DBMS_HS_PASSTHROUGH SQL-команду.
3. Без вызова COMMIT пользователь в базе данных PostgreSQL не увидит созданную таблицу.

Команда DESCRIBE позволяет в SQL*Plus через DATABASE LINK посмотреть метаданные таблицы в удаленной базе данных, как же легко, как если бы это была таблица в базе данных Oracle.

DESC[RIBE] {[schema.]object[@db_link]}

При этом типы данных полей таблицы из базы данных PostgreSQL отображаются, так как Heterogeneous Services их маппирует в типы данных Oracle. В нашем примере SERIAL (целое число) маппируется в NUMBER(10), а TEXT ("безразмерный" текст) в LONG.

    Заполним созданную таблицу данными. Сначала добавим строку напрямую через DATABASE LINK:

INSERT INTO "new_table"@PG_DB ("name") VALUES ('Вставлена через DATABASE LINK');
COMMIT;

Теперь добавим строку используя пакет DBMS_HS_PASSTHROUGH:

DECLARE
  v_cursor BINARY_INTEGER;
  v_result BINARY_INTEGER;
BEGIN
  v_cursor := DBMS_HS_PASSTHROUGH.OPEN_CURSOR@PG_DB;
  DBMS_HS_PASSTHROUGH.PARSE@PG_DB(v_cursor, 'INSERT INTO new_table (name) VALUES (?)');
  DBMS_HS_PASSTHROUGH.BIND_VARIABLE@PG_DB(v_cursor, 1, 'Вставлена используя DBMS_HS_PASSTHROUGH');
  v_result := DBMS_HS_PASSTHROUGH.EXECUTE_NON_QUERY@PG_DB(v_cursor);
  DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@PG_DB(v_cursor);
  COMMIT;  
END;

Этот пример показывает полную последовательность команд необходимых для выполнения SQL-команды используя пакет DBMS_HS_PASSTHROUGH: открытие курсора, парсинг SQL-команды, связывание параметра номер один со строковой константой (в тексте SQL-команды параметр обозначен символом вопроса), выполнение SQL-команды, закрытие курсора и COMMIT.
    Что делать если необходимо получить сгенерированное в базе данных PostgreSQL значение поля ID новой строки? Попытка добавить строку напрямую через DATABASE LINK используя RETURNING приводит к ошибке "ORA-22816: с фразой RETURNING это средство не поддерживается".

А вот используя пакет DBMS_HS_PASSTHROUGH это сделать можно:

DECLARE
  v_cursor BINARY_INTEGER;
  v_result BINARY_INTEGER;
BEGIN
  v_cursor := DBMS_HS_PASSTHROUGH.OPEN_CURSOR@PG_DB;
  DBMS_HS_PASSTHROUGH.PARSE@PG_DB(v_cursor, 'INSERT INTO new_table (name) VALUES (?) RETURNING id');
  DBMS_HS_PASSTHROUGH.BIND_VARIABLE@PG_DB(v_cursor, 1, 'Вставлена используя DBMS_HS_PASSTHROUGH и RETURNING');
  v_result := DBMS_HS_PASSTHROUGH.FETCH_ROW@PG_DB(v_cursor);
  DBMS_HS_PASSTHROUGH.GET_VALUE@PG_DB(v_cursor, 1, v_result);
  DBMS_OUTPUT.PUT_LINE(v_result);
  DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@PG_DB(v_cursor);
  COMMIT;
END;

Функция FETCH_ROW оказывается работает не только с SELECT, но, если необходимо вернуть значение из удаленной базы данных, то она может заменить EXECUTE_NON_QUERY.

    Давайте посмотрим в базе данных PostgreSQL на результаты нашей работы по добавлению данных в таблицу new_table:

    Следующий пример демонстрирует то, что функции EXECUTE_IMMEDIATE и EXECUTE_NON_QUERY возвращают количество строк, которые затронуты выполнением SQL-команды. Обновим все записи в таблице new_table:

DECLARE
  v_result BINARY_INTEGER;
BEGIN
  v_result := DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@PG_DB('update new_table set name = name || id::text');
  COMMIT;  
  DBMS_OUTPUT.PUT_LINE(v_result);
END;

В результатах выполнения скрипта мы видим, что функция EXECUTE_IMMEDIATE возвратила цифру 3, которая соответствует количеству записей в таблице new_table.

    Создадим в базе данных PostgreSQL последовательность "seq_test":

create sequence seq_test;

и получим ее значение из базы данных Oracle:

declare
  v_cursor BINARY_INTEGER;
  v_result BINARY_INTEGER;
BEGIN
  v_cursor := DBMS_HS_PASSTHROUGH.OPEN_CURSOR@PG_DB;
  DBMS_HS_PASSTHROUGH.PARSE@PG_DB(v_cursor, 'select nextval(''seq_test'')');
  v_result := DBMS_HS_PASSTHROUGH.FETCH_ROW@PG_DB(v_cursor);
  DBMS_HS_PASSTHROUGH.GET_VALUE@PG_DB(v_cursor, 1, v_result);
  DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@PG_DB(v_cursor);
  DBMS_OUTPUT.PUT_LINE(v_result);
END;

    Создадим в базе данных PostgreSQL процедуру "calc_sum":

CREATE OR REPLACE PROCEDURE calc_sum(a_first INT, a_second INT, a_sum INOUT INT)
AS
$$
BEGIN
  a_sum := a_first + a_second;
END;
$$
LANGUAGE plpgsql SECURITY DEFINER;

и вызовем ее из базы данных Oracle:

DECLARE
  v_cursor BINARY_INTEGER;
  v_result BINARY_INTEGER;
  v_second PLS_INTEGER := 5;
  v_sum    PLS_INTEGER := 0;
BEGIN
  v_cursor := DBMS_HS_PASSTHROUGH.OPEN_CURSOR@PG_DB;
  DBMS_HS_PASSTHROUGH.PARSE@PG_DB(v_cursor, 'call calc_sum(?,?,?)');
  DBMS_HS_PASSTHROUGH.BIND_VARIABLE@PG_DB(v_cursor, 1, 20);
  DBMS_HS_PASSTHROUGH.BIND_VARIABLE@PG_DB(v_cursor, 2, v_second);
  DBMS_HS_PASSTHROUGH.BIND_INOUT_VARIABLE@PG_DB(v_cursor, 3, v_sum);
  v_result := DBMS_HS_PASSTHROUGH.EXECUTE_NON_QUERY@PG_DB(v_cursor);
  DBMS_HS_PASSTHROUGH.GET_VALUE@PG_DB(v_cursor, 3, v_sum);
  DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@PG_DB(v_cursor);
  COMMIT;
  DBMS_OUTPUT.PUT_LINE(v_sum);  
END;

    Последний пример. Сделаем из базы данных Oracle запрос строк таблицы new_table с id больше или равным 2:

DECLARE
  v_cursor BINARY_INTEGER;
  v_id     PLS_INTEGER;
  v_name   VARCHAR2(1000);
BEGIN
  v_cursor := DBMS_HS_PASSTHROUGH.OPEN_CURSOR@PG_DB;
  DBMS_HS_PASSTHROUGH.PARSE@PG_DB(v_cursor, 'select id, name from new_table where id >= ? order by id');
  DBMS_HS_PASSTHROUGH.BIND_VARIABLE@PG_DB(v_cursor, 1, 2);

  WHILE DBMS_HS_PASSTHROUGH.FETCH_ROW@PG_DB(v_cursor) > 0
  LOOP
    DBMS_HS_PASSTHROUGH.GET_VALUE@PG_DB(v_cursor, 1, v_id);
    DBMS_HS_PASSTHROUGH.GET_VALUE@PG_DB(v_cursor, 2, v_name);
    DBMS_OUTPUT.PUT_LINE('ID#' || v_id || '. ' || v_name);
  END LOOP;

  DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@PG_DB(v_cursor);
  COMMIT;
END;

Пояснения к примеру:

1. Функция FETCH_ROW возвращает количество извлеченных из курсора строк. Если запрос ничего не вернул или в курсоре уже не осталось данных, то она возвращает ноль. Поэтому цикл с условием "DBMS_HS_PASSTHROUGH.FETCH_ROW@PG_DB(v_cursor) > 0" – достаем строки из курсора пока они в нем есть.
2. В результатах запроса в поле name мы видим только первую букву. Объясняется это тем, что Oracle смаппировал тип TEXT в LONG. У DBMS_HS_PASSTHROUGH есть функция GET_VALUE_RAW, но нет функции GET_VALUE_LONG или процедуры аналогичной DBMS_SQL.COLUMN_VALUE_LONG. Я не нашел рекомендаций, как правильно прочитать значение поля типа TEXT, но придумал два своих способа.

Первый способ – это в запросе конвертировать значение поля типа TEXT в другой тип данных. Например, в varchar(1000):

DECLARE
  v_cursor BINARY_INTEGER;
  v_id     PLS_INTEGER;
  v_name   VARCHAR2(1000);
BEGIN
  v_cursor := DBMS_HS_PASSTHROUGH.OPEN_CURSOR@PG_DB;
  DBMS_HS_PASSTHROUGH.PARSE@PG_DB(v_cursor, 'select id, cast(name as varchar(1000)) from new_table where id >= ? order by id');
  DBMS_HS_PASSTHROUGH.BIND_VARIABLE@PG_DB(v_cursor, 1, 2);

  WHILE DBMS_HS_PASSTHROUGH.FETCH_ROW@PG_DB(v_cursor) > 0
  LOOP
    DBMS_HS_PASSTHROUGH.GET_VALUE@PG_DB(v_cursor, 1, v_id);
    DBMS_HS_PASSTHROUGH.GET_VALUE@PG_DB(v_cursor, 2, v_name);
    DBMS_OUTPUT.PUT_LINE('ID#' || v_id || '. ' || v_name);
  END LOOP;

  DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@PG_DB(v_cursor);
  COMMIT;
END;

Второй способ более "глобальный". В настройках источника данных ODBC (System DNS) необходимо изменить значение параметра "Max LongVarChar" на длину строкового поля, которая будет приемлема для Oracle. Например, на 4000.

Что бы увидеть результат смены значения параметра "Max LongVarChar"создадим новое подключение к базе данных Oracle и проверяем маппинг полей:

Поле name сменило тип с LONG на VARCHAR2(4000). Теперь все поля типа TEXT через этот DATABASE LINK мы будем видеть, как VARCHAR2(4000) и наш скрипт вернет корректные данные:

Если кто-то знает еще способ, как в Oracle через DBMS_HS_PASSTHROUGH увидеть полное содержимое поля типа TEXT, то, пожалуйста, напишите о нем в комментарии к этой статье.

    На сайте компании Oracle в справочнике по пакетам и типам PL/SQL можно ознакомиться с описанием DBMS_HS_PASSTHROUGH для:

• Oracle Database 12c Release 1 (12.1)
• Oracle Database 12c Release 2 (12.2)
• Oracle Database 19c
• Oracle Database 21c

    Подробнее о настройке Oracle Database Gateway for ODBC (DG4ODBC) для доступа к информации в базе данных PostgreSQL из базы данных Oracle:

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