Open Source PL/SQL решение для работы с Веб-Службами

Столкнулся с требованием отправлять и получать SOAP сообщения из базы данных Oracle.
Также это решение должно быть универсальным и легко интегрируемым с другими модулями.
В интернете ни чего подобного не нашел. Есть статьи рассказывающие о том как посылать SOAP сообщения исползуя UTL_HTTP пакет, но ни чего более.

Решил написать универсальный продукт на PL/SQL для отправки SOAP сообщений из базы данных Oracle который легко настраивается и интегрируется.

Итак, приступим.

Данное решение исползует следующие обьекты Базы Данных:

• User-Defined Datatypes
• Table
• Package

Предполагается что читателю не нужно объяснять что такое SOAP, XML или объекты Базы Данных Oracle.

Установка

Для установки данного решения необходимо установить следующие объекты

• Тип PARAMS_RECORD
• Тип PARAMS_ARRAY
• Таблица WS_SERVER
• Таблица WS_TEMPLATE
• Таблица WS_LOG
• Пакет WS

Исходный код тут:
www.dropbox.com/sh/tx9mknh9cm8pvfw/AADWCcP8v5uFU7Nd8l3f2t-fa

Инструкции

Рассмотрим структуру таблиц

Рассмотрим каждую из них более подробно

Таблица WS_SERVER

Хранит список Серверов куда будут отправлятся SOAP/XML сообщения.

Столбец SERVER_ID – Логический идентификатор сервера. Является Primary Key
Столбец URL – Путь к сервису
STATUS – Статус сервера. 1 – работает. 0 – выключен. По умолчанию 1

Таблица WS_TEMPLATE

Хранит шаблоны и конфигурационную информацию SOAP/XML сообщений.

TEMPLATE_ID – Логический идентификатор Шаблона. Является Primary Key
TEMPLATE_XML – Шаблона (Формат будет рассмотрен далее)
SERVER_ID – Логический идентификатор сервера. Является Foreign Key ссылающийся на таблицу WS_SERVER
REQUEST_PARAMS – Параметры запроса (Формат будет рассмотрен далее)
RESPONSE_PARAMS – Параметры ответа (Формат будет рассмотрен далее)
XMLNS – Пространство имён
PATH – XML Путь (Будет рассмотрен подробнее на примере далее)
STATUS – Статус сервера. 1 – работает. 0 – выключен. По умолчанию 1

Таблица WS_LOG

Хранит логи об операциях.

EVENT_TIME – Время операции
XML_REQUEST – XML/SOAP запрос
XML_RESPONSE – XML/SOAP ответ
REQUEST_PARAMS – Параметры запроса
RESPONSE_PARAMS – Параметры ответа
RETVAL – Информация о статусе выполненного Запроса. Удачно если >0
RETMSG – Информация о выполненном Запросе. Код ошибки в случае неудачного выполнения Запроса
EXECUTE_TIME – Время в секундах и милисекундах потраченное на выполнение Запроса

Как заполнять Шаблон TEMPLATE_XML

Сюда вписывается сам XML файл при этом заменив необходимые для ввода параметры в следующем формате %PARAMETER_NAME%

Например:

<?xml version="1.0"?> <soap:Envelope xmlns:soap="http://www.w3.org/2001/12/soap-envelope" soap:encodingStyle="http://www.w3.org/2001/12/soap-encoding"> <soap:Body xmlns:m="http://www.example.org/stock">   <m:GetStockPrice>     <m:StockName>%NAME%</m:StockName>   </m:GetStockPrice> </soap:Body> </soap:Envelope> 

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

<m:StockName>%NAME%</m:StockName> 

Если соответственно Значений несколько ни чего не мешает их тут же указать:

<?xml version="1.0"?> <soap:Envelope xmlns:soap="http://www.w3.org/2001/12/soap-envelope" soap:encodingStyle="http://www.w3.org/2001/12/soap-encoding"> <soap:Body xmlns:m="http://www.example.org/stock">   <m:GetStockPrice>     <m:StockName>%NAME%</m:StockName>     <m:StockCount>%COUNT%</m:StockCount>   </m:GetStockPrice> </soap:Body> </soap:Envelope> 

Как видно указаны 2 переменные NAMEи COUNT

<m:StockName>%NAME%</m:StockName> <m:StockCount>%COUNT%</m:StockCount> 

Правило заполнения Параметров (Столбцы REQUEST_PARAMS и RESPONSE_PARAMS )

Данный столбец заполняется в следующем формате.
PARAMETER_NAME_1={ VALUE_1}|PARAMETER_NAME_2={ VALUE_2}|…PARAMETER_NAME_N={ VALUE_N}

Параметр Запроса (Столбец REQUEST_PARAMS)

Данный столбец заполняется в том случае если в не зависимости от запроса есть константные переменные. В основном его можно оставить пустым. Данное значение задается при запуске основной процедуры. Об этом чуть далее.

Столбец PATH

Чтобы настроить работу с Ответом от сервера должен быть заполнен столбец PATH который указывает на путь где в XML (между какими тагами) хронится необходимый ответ.

При отправке SOAP/XML сообщения заранее известно возможный ответ который придет от сервера.
Например ответом может быть следующий SOAP/XML

<?xml version="1.0"?> <soap:Envelope xmlns:soap="http://www.w3.org/2001/12/soap-envelope" soap:encodingStyle="http://www.w3.org/2001/12/soap-encoding"> <soap:Body xmlns:m="http://www.example.org/stock">   <m:GetStockPriceResponse>     <m:Price>34.5</m:Price>   </m:GetStockPriceResponse> </soap:Body> </soap:Envelope> 

В данном случае столбец PATH нужно записать как:
/soap:Envelope/soap:Body/m:GetStockPriceResponse

Как видно из Ответа именно в этом пути находится необходимое значение

<m:Price>34.5</m:Price> 

Параметр Ответа (Столбец RESPONSE_PARAMS)

Даный Столбец обязателен для заполнения. Формат остается тот же (указанный выше).

Зная заранее формат ответа, необходимо записать в этот столбец параметры.

<?xml version="1.0"?> <soap:Envelope xmlns:soap="http://www.w3.org/2001/12/soap-envelope" soap:encodingStyle="http://www.w3.org/2001/12/soap-encoding"> <soap:Body xmlns:m="http://www.example.org/stock">   <m:GetStockPriceResponse>     <m:Price>34.5</m:Price>   </m:GetStockPriceResponse> </soap:Body> </soap:Envelope> 

Уже указав в столбце PATH необходимый нам путь вписываем сюда необходимые значения в след формате:
RESULT_PRICE={m:Price}

Это означает переменной RESULT_PRICE присвоить занчение m:Price полученного из SOAP/XML Ответа. Далее на примере это будет подробнее рассмотрено.

Столбец XMLNS

Этот столбец пространств имен. Заполняется анологично из Запроса SOAP/XML.

<?xml version="1.0"?> <soap:Envelope xmlns:soap="http://www.w3.org/2001/12/soap-envelope" soap:encodingStyle="http://www.w3.org/2001/12/soap-encoding"> <soap:Body xmlns:m="http://www.example.org/stock">   <m:GetStockPrice>     <m:StockName>%NAME%</m:StockName> <m:StockCount>%COUNT%</m:StockCount>   </m:GetStockPrice> </soap:Body> </soap:Envelope> 

Этот столбец нужно заполнить вписав туда все xmlns из этого запроса. Из данного примера его нужно заполнить следующим значением:
xmlns:soap="http://www.w3.org/2001/12/soap-envelope" xmlns:m="http://www.example.org/stock"

Запуск процедуры

Теперь рассмотрим Структуру пакета и правила запуска.
Спецификация пакета следующая:

create or replace package WS is  PROCEDURE add_param(pi_params          in out varchar2,                       pi_parameter_name  varchar2,                       pi_parameter_value varchar2);    FUNCTION get_param(pi_params varchar2, pi_parameter_name varchar2)     return varchar2;    PROCEDURE call(pi_template_id   VARCHAR2,                  pi_params        VARCHAR2,                  po_params        OUT VARCHAR2,                  po_data_response OUT VARCHAR2);  end WS; 

Рассмотрим каждую функцию подробнее.
Использование каждой из них на примере будет рассмотрено в разделе Интеграция.

Процедура add_param

Используется для добавления/формирования параметра.

Параметры
pi_params – Переменная строки параметров
pi_parameter_name – Имя добавляемого параметра
pi_parameter_value – Значение добавляемого параметра

Функция get_param

Используется для извлечения параметра из строки параметров.

Параметры
pi_params – Переменная строки параметров
pi_parameter_name — Имя извлекаемого параметра

Процедура call

Является главной и запускает сам процесс.

Параметры
pi_template_id – Идентификатор шаблона из таблицы WS_TEMPLATE
pi_params — Переменная строки параметров необходимая для отправки
po_params — Переменная строки параметров полученная в ответ от сервера
po_data_response – XML ответ от сервера(Эту переменную можно и не использовать)

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

Интеграция

В это разделе мы рассмотрим интеграцию данного решения на примере выдуманного проекта.

Предположим есть Задача:

Построить Интерфейс для взаимодействия с Сервером для конечного пользователя который должен иметь возможность производить следующие операции

• Получении информации о Товаре
• Добавить Товар

Схема реализации следующая:

Отмечу что Интерфейс между Конечным пользователем и Базой Данных может быть любым. Конечный пользователь может запускать процедуру непосредственно через SQL или же она может вызываться Сторонним приложением (Например Java IE или Java EE).

Предоставлена следующая информация:

Сам Web Service
http://10.10.1.100:8080/GoodsManagementWS/Goods

Следует отметить что перед отправкой SOAP/XML сообщений на сервер, последний необходимо добавить в ACL. Для этого необходимо обратиться к Администратору Базы Данных. Так же в интерне есть информация об этом. Думаю не стоит это рассматривать в данной статье.

Примеры Запросов

Информация о товаре

Запрос:

<?xml version="1.0"?> <soap:Envelope xmlns:soap="http://www.w3.org/2001/12/soap-envelope" soap:encodingStyle="http://www.w3.org/2001/12/soap-encoding"> <soap:Body xmlns:m="http://www.example.org/goods">   <m:GetInfo>     <m:ID>1</m:ID>   </m: GetInfo > </soap:Body> </soap:Envelope> 

Ответ:

<?xml version="1.0"?> <soap:Envelope xmlns:soap="http://www.w3.org/2001/12/soap-envelope" soap:encodingStyle="http://www.w3.org/2001/12/soap-encoding"> <soap:Body xmlns:m="http://www.example.org/goods">   <m:Response>     <m:Name>Printer</m:Name>     <m:Vendor>HP</m:Vendor>     <m:Price>Printer</m:Price>     <m:Count>Printer</m:Count>   </m:Response> </soap:Body> </soap:Envelope> 

Добавление Товара

Запрос:

<?xml version="1.0"?> <soap:Envelope xmlns:soap="http://www.w3.org/2001/12/soap-envelope" soap:encodingStyle="http://www.w3.org/2001/12/soap-encoding"> <soap:Body xmlns:m="http://www.example.org/goods">   <m:Add>     <m:Name>Printer</m:Name>     <m:Vendor>HP</m:Vendor>     <m:Price>Printer</m:Price>     <m:Count>Printer</m:Count>   </m: Add > </soap:Body> </soap:Envelope> 

Ответ:

<?xml version="1.0"?> <soap:Envelope xmlns:soap="http://www.w3.org/2001/12/soap-envelope" soap:encodingStyle="http://www.w3.org/2001/12/soap-encoding"> <soap:Body xmlns:m="http://www.example.org/goods">   <m:Response>     <m:id>1</m:id>   </m:Response> </soap:Body> </soap:Envelope> 

Мы получили необходимые данные от заказчика. Приступаем к настройке и интеграции.

В первую очередь необходимо записать инофрмацию о сервере:

INSERT INTO WS_SERVER (SERVER_ID, URL, STATUS)      VALUES ('Store', 'http://10.10.1.100:8080/GoodsManagementWS/Goods', 1); 

Далее необходимо записать информацию о шаблонах запросов в таблицу WS_TEMPLATE

Информация о товаре

INSERT INTO WS_TEMPLATE   (TEMPLATE_ID,    TEMPLATE_XML,    SERVER_ID,    REQUEST_PARAMS,    RESPONSE_PARAMS,    XMLNS,    PATH,    STATUS) VALUES   ('GetInfo', --TEMPLATE_ID    '<?xml version="1.0"?> <soap:Envelope xmlns:soap="http://www.w3.org/2001/12/soap-envelope" soap:encodingStyle="http://www.w3.org/2001/12/soap-encoding"> <soap:Body xmlns:m="http://www.example.org/goods">   <m:GetInfo>     <m:ID>%ID%</m:ID>   </m: GetInfo > </soap:Body> </soap:Envelope> ', --TEMPLATE_XML    'Store', --SERVER_ID    NULL, --REQUEST_PARAMS    'NAME={m:Name}|VENDOR={m:Vendor}|PRICE={m:Price}|COUNT={m:Count}', --RESPONSE_PARAMS    'xmlns:soap="http://www.w3.org/2001/12/soap-envelope" xmlns:m="http://www.example.org/goods"', --XMLNS    '/soap:Envelope/soap:Body/m:Response', --PATH    1) ;--STATUS 

Добавление Товара

INSERT INTO WS_TEMPLATE   (TEMPLATE_ID,    TEMPLATE_XML,    SERVER_ID,    REQUEST_PARAMS,    RESPONSE_PARAMS,    XMLNS,    PATH,    STATUS) VALUES   ('GetInfo', --TEMPLATE_ID    '<?xml version="1.0"?> <soap:Envelope xmlns:soap="http://www.w3.org/2001/12/soap-envelope" soap:encodingStyle="http://www.w3.org/2001/12/soap-encoding"> <soap:Body xmlns:m="http://www.example.org/goods">   <m:Add>     <m:Name>%NAME%</m:Name>     <m:Vendor>%VENDOR%</m:Vendor>     <m:Price>%PRICE%</m:Price>     <m:Count>%COUNT%</m:Count>   </m: Add > </soap:Body> </soap:Envelope> ', --TEMPLATE_XML    'Store', --SERVER_ID    NULL, --REQUEST_PARAMS    'ID={m:id}', --RESPONSE_PARAMS    'xmlns:soap="http://www.w3.org/2001/12/soap-envelope" xmlns:m="http://www.example.org/goods"', --XMLNS    '/soap:Envelope/soap:Body/m:Response', --PATH    1); --STATUS 

И вот добавив всю необходимую Информацию Процедура может быть запущена. Но для этого необходимо написать процедуры для данного проекта которые в свою очередь использует процедуры из пакета WS.

Получении информации о товаре

Для этой задачи итоговая процедура будет выглядеть следующим образом

CREATE OR REPLACE PROCEDURE GET_INFO(PI_ID     VARCHAR2,                    PO_NAME   OUT VARCHAR2,                    PO_VENDOR OUT VARCHAR2,                    PO_PRICE  OUT NUMBER,                    PO_COUNT  OUT NUMBER) IS   v_template_id     VARCHAR2(100) := 'GetInfo';   v_data_response   VARCHAR2(4000);   v_request_params  VARCHAR2(4000);   v_response_params VARCHAR2(4000); BEGIN   ws.add_param(v_request_params, 'ID', PI_ID);    ws.call(v_template_id,           v_request_params,           v_response_params,           v_data_response);    PO_NAME   := ws.get_param(v_response_params, 'NAME');   PO_VENDOR := ws.get_param(v_response_params, 'VENDOR');   PO_PRICE  := ws.get_param(v_response_params, 'PRICE');   PO_COUNT  := ws.get_param(v_response_params, 'COUNT'); END; 

Пакет подготовит SOAP сообщение для отправки, отправит, получит результат и в результате результирующим ответом работы итоговой процедуры будет значения полученные работой процедуры get_param. Можно получить любой параметр из списка параметров RESPONSE_PARAMS и вернуть в качестве результата.

Добавление товара

Для этой задачи итоговая процедура будет выглядеть следующим образом

PROCEDURE GET_INFO(PI_NAME   VARCHAR2,                    PI_VENDOR VARCHAR2,                    PI_PRICE  NUMBER,                    PI_COUNT  NUMBER,                    PO_ID     OUT VARCHAR2) IS   v_template_id     VARCHAR2(100) := 'GetInfo';   v_data_response   VARCHAR2(4000);   v_request_params  VARCHAR2(4000);   v_response_params VARCHAR2(4000); BEGIN   ws.add_param(v_request_params, 'NAME', PI_NAME);   ws.add_param(v_request_params, 'VENDOR', PI_VENDOR);   ws.add_param(v_request_params, 'PRICE', PI_PRICE);   ws.add_param(v_request_params, 'COUNT', PI_COUNT);    ws.call(v_template_id,           v_request_params,           v_response_params,           v_data_response);    PO_ID := ws.get_param(v_response_params, 'ID'); END; 

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

И так, в итоге получились 2 процедуры которые выполняют поставленную задачу. Результаты запросов логируются в таблицу WS_LOG

Дополнительные вопросы

Что если необходимые данные в ответе находятся в разных путях?

<?xml version="1.0"?> <soap:Envelope xmlns:soap="http://www.w3.org/2001/12/soap-envelope" soap:encodingStyle="http://www.w3.org/2001/12/soap-encoding"> <soap:Body xmlns:m="http://www.example.org/goods">   <m:Response1>     <m:id>1</m:id>   </m:Response1>   <m:Response2>     <m:id>1</m:id>   </m:Response2>   <m:Response3>     <m:id>1</m:id>   </m:Response3> </soap:Body> </soap:Envelope> 

В таком случае PATH записывает как /soap:Envelope/soap:Body. Так как необходимый ответ находится между тагами <soap:Body> и </soap:Body>. А уже RESPONSE_PARAMS нужно будет записать немного детальней.

ID1={ m:Response1/m:id}| ID2={ m:Response2/m:id}| ID3={ m:Response3/m:id}

Что если SOAP/XML Запрос и Ответ простейшие ?

Запрос

<Request>     <Data>Test</Data> </Request> 

Ответ

<Response>    <Result>DONE</Result> <Response> 

В таком случае все настраивается аналагичным образом.
Соответственно XMLNS пустой, PATH равен Response и RESPONSE_PARAMS равен RES={Result}. Отмечу что имя переменной указывается произвольно, но именно оно будет использоватся для запроса в процедре get_param

Если я ввожу строку REQUEST_PARAMS во время запуска процедры, то зачем нужен столбец REQUEST_PARAMS в таблице WS_TEMPLATE ?

Надобность в данном столбце возникает в том случае если в Запросе SOAP/XML есть значения которые не изменны. Указав их в данном столбце во время запуска процедуры уже нет надобности добавлять эти параметры(процедура add_param) так как они уже добавлены по умолчанию.

Вот и все.

Старался выложить достаточно информации.
Буду рад услышать и ответить на вопросы которые возникнут. А также критику, предложения и советы.
Решение было написано недавно. Так что есть вещи которые можно доработать.

Спасибо. Надеюсь статья оказалась полезной.