20 декабря 2022

Вызов процедур, функций и других 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.
Связь между Oracle и PostgreSQL через Heterogeneous Services и 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.
Просмотр метаданных таблицы PostgreSQL из SQL*Plus

    Заполним созданную таблицу данными. Сначала добавим строку напрямую через 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 это средство не поддерживается".
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;
DBMS_HS_PASSTHROUGH - INSERT RETURNING
Функция FETCH_ROW оказывается работает не только с SELECT, но, если необходимо вернуть значение из удаленной базы данных, то она может заменить EXECUTE_NON_QUERY.

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

Результаты работы в базе данных PostgreSQL

    Следующий пример демонстрирует то, что функции 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;
DBMS_HS_PASSTHROUGH - обновление записей в таблице
В результатах выполнения скрипта мы видим, что функция 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;
DBMS_HS_PASSTHROUGH - запрос к таблице в базе PostgreSQL
Пояснения к примеру:
  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;
DBMS_HS_PASSTHROUGH - запрос к таблице в базе PostgreSQL с преобразованием типа поля
Второй способ более "глобальный". В настройках источника данных ODBC (System DNS) необходимо изменить значение параметра "Max LongVarChar" на длину строкового поля, которая будет приемлема для Oracle. Например, на 4000.
ODBC Data Source Advanced Options
Что бы увидеть результат смены значения параметра "Max LongVarChar"создадим новое подключение к базе данных Oracle и проверяем маппинг полей:
Просмотр метаданных таблицы PostgreSQL из SQL*Plus
Поле name сменило тип с LONG на VARCHAR2(4000). Теперь все поля типа TEXT через этот DATABASE LINK мы будем видеть, как VARCHAR2(4000) и наш скрипт вернет корректные данные:
DBMS_HS_PASSTHROUGH - запрос к таблице в базе PostgreSQL после изменения параметра Max LongVarChar
Если кто-то знает еще способ, как в Oracle через DBMS_HS_PASSTHROUGH увидеть полное содержимое поля типа TEXT, то, пожалуйста, напишите о нем в комментарии к этой статье.

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

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

на

Комментариев нет:

Отправить комментарий

Подписаться на: Комментарии к сообщению (Atom)