PL / SQL Web Services Solution

Faced the requirement to send and receive SOAP messages from an Oracle database.
Also, this solution should be universal and easily integrated with other modules.
On the Internet, nothing of the kind found. There are articles about how to send SOAP messages using the UTL_HTTP packet, but nothing more.

I decided to write a generic 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 is SOAP, XML, or Oracle Database objects.

Installation

To install this solution, you must install the following objects

• Type PARAMS_RECORD
• Type PARAMS_ARRAY
• WS_SERVER table
• WS_TEMPLATE table
• WS_LOG table
• WS Package

Source

Instructions

Consider the structure of tables



Consider each of them in more detail.

WS_SERVER table

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

The SERVER_ID column is a logical server identifier. Is a primary key
URL column - Service path
STATUS - Status. 1 - works. 0 - off. Default 1

WS_TEMPLATE table

Stores patterns and configuration information for SOAP / XML messages.

TEMPLATE_ID - Logical Template ID. Is a primary key
TEMPLATE_XML - Template (The format will be discussed later)
SERVER_ID - The logical server identifier. Is a foreign key referencing the WS_SERVER table
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 (It will be discussed in more detail by the example below)
STATUS - Status. 1 - works. 0 - off. Default 1

WS_LOG table

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 executed request. Successfully if> 0
RETMSG - Information about the executed Request. Error code in case of a failed request
EXECUTE_TIME - Time in seconds and milliseconds spent on the execution of the request

How to fill out the TEMPLATE_XML Template

The XML file itself fits here, replacing the parameters for entering in the following format %PARAMETER_NAME%

For example:

 <?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> 

In this case, to send this request we need to write the value in this format in this column. The program itself will then replace it with the response from the Parameter (the parameters are described below).

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

If, accordingly, there are several Values ​​that do not prevent them from being immediately indicated:

 <?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> 

As you can see, there are 2 variables NAME and COUNT

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

Parameters Filling Rule (Columns REQUEST_PARAMS and RESPONSE_PARAMS)

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

Request Parameter (REQUEST_PARAMS Column)

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

PATH column

To set up working with the Reply from the server, the PATH column must be filled in which indicates the path where in XML (between which tags) the necessary answer is stored.

When sending a SOAP / XML message, we know in advance the possible answer that will come from the server.
For example, the answer might be the following 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> 

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

As can be seen from the Answer, this is the way to find the necessary value.

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

Response Parameter (RESPONSE_PARAMS Column)

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

Knowing in advance the format of the response, you must write the parameters in this column.

 <?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> 

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

This means RESULT_PRICE to assign the m:Price value to the Response obtained from SOAP / XML. Further on the example it will be considered in more detail.

XMLNS column

This column is namespace. Filled similarly from the SOAP / XML Request.

 <?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> 

This column must be filled in by typing in all the xmlns from 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"

Running procedure

Now consider the package structure and launch rules.
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; 

Consider each function in more detail.
Using each of them on the example will be discussed in the Integration section.

Procedure add_param

Used to add / form a parameter.

Options
pi_params - Variable Parameter String
pi_parameter_name - The name of the parameter being added
pi_parameter_value - The value of the parameter being added

Get_param function

Used to extract a parameter from the parameter string.

Options
pi_params - Variable Parameter String
pi_parameter_name - The name of the parameter to retrieve

Call procedure

It is the main and starts the process itself.

Options
pi_template_id - Template identifier from WS_TEMPLATE table
pi_params - Variable of the parameter string required for sending
po_params - Variable of the parameter string received in response from the server
po_data_response - XML ​​response from the server (This variable can not be used)

In the next section, we will use the package procedures as an example.

Integration

In this section, we will look at the integration of this solution using the example of an invented project.

Suppose there is a task:

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

• Getting Product Information
• Add Item

The implementation scheme is as follows:


I note that the interface between the End User and the Database can be anything. The end user can run 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 Administrator of the Database. Also in the intern there is information about this. I think it should not be considered in this article.

Request Examples

Product information

Request:

 <?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> 

Answer:

 <?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> 

Adding a Product

Request:

 <?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> 

Answer:

 <?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> 

We received the necessary data from the customer. Getting started to configure and integrate.

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

 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 templates 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 '<?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 

Adding a Product

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

And by adding all the necessary information the procedure can be run. But for this you need to write procedures for this project which in turn uses the procedures from the WS package.

Receive product information

For this task, the final procedure will be 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 the SOAP message for sending, send, receive the result and as a result, the resulting response of the final procedure will be the values ​​obtained by the work of the get_param procedure. You can get any parameter from the parameter list RESPONSE_PARAMS and return it as a result.

Add product

For this task, the final procedure will be 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; 

In this procedure, there are several input parameters, and the resulting variable is one.

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

Additional questions

What if the necessary data in the answer is in different ways?

 <?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> 

In this case, the PATH writes /soap:Envelope/soap:Body . Since the necessary answer lies between the <soap:Body> </soap:Body> tags. And already RESPONSE_PARAMS will need to be written a little more detailed.

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

What if SOAP / XML Request and Answer are Simple?

Request

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

Answer

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

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

If I enter the string REQUEST_PARAMS during the startup of the procedure, then why do I need the REQUEST_PARAMS column in the WS_TEMPLATE table?

The need for this column arises if there are values ​​in the SOAP / XML Request that are unchanged. Specifying them in this column during the start of the procedure, there is no longer any need to add these parameters (the add_param procedure) since they are already added by default.

That's all

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

Thank. I hope this article was helpful.