PL / SQL Web Services Solution

Faced the requirement to send and receive SOAP messages from the Oracle database.
Also, this solution should be universal and easily integrable with other modules.
I didn’t find anything like this on the Internet. There are articles about how to send SOAP messages using the UTL_HTTP packet, but nothing more.

I decided to write a universal PL / SQL product for sending SOAP messages from an Oracle database that is easy to configure and integrate.

So let's get started.

This solution uses the following Database objects:

• User-Defined Datatypes
• Table
• Package

It is assumed that the reader does not need to explain what SOAP, XML, or Oracle Database objects are.

Installation

To install this solution, you must install the following objects

• Type PARAMS_RECORD
• Type PARAMS_ARRAY
• Table WS_SERVER
• Table WS_TEMPLATE
• Table WS_LOG
• WS package

Source

Instructions

Consider the structure of tables.



Consider each of them in more detail.

Table WS_SERVER

Stores a list of Servers where SOAP / XML messages will be sent.

Column SERVER_ID - The logical identifier of the server. Is the Primary Key
Column URL - Service Path
STATUS - Status. 1 - works. 0 - off. Default 1

Table WS_TEMPLATE

Stores SOAP / XML message templates and configuration information.

TEMPLATE_ID - The logical identifier of the Template. Is Primary Key
TEMPLATE_XML - Template (Format will be discussed later)
SERVER_ID - Logical identifier of the server. It is a Foreign Key referring to the table WS_SERVER
REQUEST_PARAMS - Request parameters (The format will be discussed later)
RESPONSE_PARAMS - Response parameters (The format will be discussed later)
XMLNS - Namespace
PATH - XML ​​Path (Will be discussed in more detail using the example below)
STATUS - Status. 1 - works. 0 - off. Default 1

Table WS_LOG

Keeps logs about operations.

EVENT_TIME - Operation time
XML_REQUEST - XML ​​/ SOAP request
XML_RESPONSE - XML ​​/ SOAP response
REQUEST_PARAMS - Request parameters
RESPONSE_PARAMS - Response parameters
RETVAL - Information about the status of the completed Request. Successfully if> 0
RETMSG - Information about the completed Request. Error code in case of unsuccessful execution of the Request
EXECUTE_TIME - Time in seconds and milliseconds spent on the execution of the Request

How to populate the TEMPLATE_XML Template

The XML file itself fits into this while replacing the parameters necessary for input in the following format %PARAMETER_NAME%

For example:

%NAME%

In this case, to send this request, we need to write a value in this format in this column. The program itself further replace this with the corresponding parameter (the parameters are discussed below).

%NAME%

If, accordingly, there are several values ​​that do not interfere, they should be immediately indicated:

%NAME%%COUNT%

As you can see, 2 variables NAMEandCOUNT

%NAME%%COUNT%

Parameter filling rule (REQUEST_PARAMS and RESPONSE_PARAMS columns)

This column is filled in the following format.
PARAMETER_NAME_1={VALUE_1}|PARAMETER_NAME_2={VALUE_2}|…PARAMETER_NAME_N={VALUE_N}

Query Parameter (REQUEST_PARAMS Column)

This column is filled in if there are constant variables regardless of the request. Basically, it can be left blank. This value is set when the main procedure starts. About this a little further.

Column PATH

To configure work with the Response from the server, the PATH column must be filled in which indicates the path where in XML (between which tags) the necessary answer is chroned.

When sending a SOAP / XML message, a possible response that will come from the server is known in advance.
For example, the answer might be the following SOAP / XML

34.5

In this case, the PATH column should be written as:
/soap:Envelope/soap:Body/m:GetStockPriceResponse

As you can see from the Answer, the necessary value is in this path

34.5

Response Parameter (RESPONSE_PARAMS Column)

This column is required. The format remains the same (above).

Knowing the response format in advance, it is necessary to write the parameters in this column.

34.5

Already indicating the path we need in the PATH column, we enter the necessary values ​​here in the following format:
RESULT_PRICE={m:Price}

This means assigning the RESULT_PRICE variable to the value m:Pricereceived from the SOAP / XML response. Further on an example it will be considered in more detail.

XMLNS Column

This column is a namespace. It is filled in the same way from the SOAP / XML Request.

%NAME%%COUNT%

This column must be filled in by entering there all xmlnsof this query. From this example, you need to fill it with the following value:
xmlns:soap="http://www.w3.org/2001/12/soap-envelope" xmlns:m="http://www.example.org/stock"

Procedure start

Now consider the structure of the package and the rules for running.
The package specification is as follows:

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;

Let's consider each function in more detail.
Using each of them as an example will be discussed in the Integration section.

Add_param procedure

Used to add / form a parameter.

Parameters
pi_params - Parameter string variable
pi_parameter_name - Name of the added parameter
pi_parameter_value - Value of the added parameter

Get_param function

Used to retrieve a parameter from the parameter string.

Parameters
pi_params - Parameter string variable
pi_parameter_name - Name of the parameter to retrieve

Call procedure

It is the main one and starts the process itself.

Parameters
pi_template_id - Template identifier from the WS_TEMPLATE table pi_params
- Parameter string variable needed to send
po_params - Parameter string variable received in response from
po_data_response server - XML ​​response from the server (This variable can be omitted)

In the next section, we will use the package procedures .

Integration

In this section, we will consider the integration of this solution using an example of a fictional project.

Suppose there is a Task:

Build an Interface for interaction with the Server for an end user who should be able to perform the following operations

• Getting Product Information
• Add Product

The implementation scheme is as follows:


I note that the Interface between the End User and the Database can be any. The end user can start the procedure directly through SQL, or it can be called by a third-party application (for example, Java SE, Java EE, etc.).

The following information is provided:

Web Service itself
http://10.10.1.100:8080/GoodsManagementWS/Goods

It should be noted that before sending SOAP / XML messages to the server, the latter must be added to the ACL. To do this, contact the Database Administrator. Also on the Internet there is information about this. I think you should not consider this in this article.

Request Examples

Product information

Request:

1

Answer:

PrinterHPPrinterPrinter

Adding a Product

Request:

PrinterHPPrinterPrinter

Answer:

1

We received the necessary data from the customer. Getting started with setup and integration.

First of all, you need to record information about the server:

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

Next, you need to write information about the query patterns in the table WS_TEMPLATE

Product information

INSERT INTO WS_TEMPLATE
  (TEMPLATE_ID,
   TEMPLATE_XML,
   SERVER_ID,
   REQUEST_PARAMS,
   RESPONSE_PARAMS,
   XMLNS,
   PATH,
   STATUS)
VALUES
  ('GetInfo', --TEMPLATE_ID
   '
%ID%
', --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

Adding a Product

INSERT INTO WS_TEMPLATE
  (TEMPLATE_ID,
   TEMPLATE_XML,
   SERVER_ID,
   REQUEST_PARAMS,
   RESPONSE_PARAMS,
   XMLNS,
   PATH,
   STATUS)
VALUES
  ('AddInfo', --TEMPLATE_ID
   '
%NAME%%VENDOR%%PRICE%%COUNT%
', --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

And now, adding all the necessary Information, the Procedure can be started. But for this it is necessary to write the procedures for this project, which in turn uses the procedures from the WS package.

Retrieving Product Information

For this task, the final procedure will look as follows

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;

The package will prepare a SOAP message for sending, send, receive the result, and as a result, the resulting response of the final procedure will be the values ​​received by the get_param procedure. You can get any parameter from the parameter list RESPONSE_PARAMS and return as a result.

Adding a Product

For this task, the final procedure will look as follows

PROCEDURE ADD_INFO(PI_NAME   VARCHAR2,
                   PI_VENDOR VARCHAR2,
                   PI_PRICE  NUMBER,
                   PI_COUNT  NUMBER,
                   PO_ID     OUT VARCHAR2) IS
  v_template_id     VARCHAR2(100) := 'AddInfo';
  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;

There are several input parameters in this procedure, and the resulting variable is one.

And so, in the end we got 2 procedures that perform the task. Query results are logged in a table.WS_LOG

Additional questions

What if the required data in the response is in different ways?

111

In this case, PATH writes как /soap:Envelope/soap:Body. Since the necessary answer is between the tags и . And already RESPONSE_PARAMS will need to be written in a little more detail.

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

What if the SOAP / XML Request and Response are the simplest?

Request

Test

Answer

DONE

In this case, everything is configured in a similar way.
Accordingly, XMLNS is empty, PATH is equal, Response and RESPONSE_PARAMS is equal RES={Result}. I note that the variable name is specified arbitrarily, but it will be used for the request in the procedureget_param

If I enter the REQUEST_PARAMS row while the procedure starts, then why do I need the REQUEST_PARAMS column in the WS_TEMPLATE table?

This column is needed if there are values ​​in the SOAP / XML request that are not changed. By specifying them in this column at the time the procedure starts, there is no need to add these parameters (add_param procedure) since they are already added by default.

That's all

I tried to lay out enough information.
I will be glad to hear and answer the questions that arise. As well as criticism, suggestions and tips.
The decision was written recently. So there are things that you can refine.

Thanks. Hope the article has been helpful.