Oracle stored procedure calls Web Service

1. Overview

Most recently, in an ESB project, the customer's service calls between the various systems were mostly performed in oracle stored procedures, and this article describes the oracle stored procedure invocation Web service . Other mainstream databases, such as MySQL and SQL Service, call the Web service method is not introduced here, this article is mainly used to describe the oracle stored procedure Call The method of the Web Service .

As we all know, when the Web Service sends the request and receives the result through the HTTP protocol, the request content and the result content sent are encapsulated in XML format, and some specific HTTP message headers are added. To illustrate the content format of HTTP messages, which are specific HTTP message headers and XML content formats that are SOAP protocols. The SOAP protocol is based on the HTTP protocol, and the relationship between the two is similar to the highway is based on the ordinary highway transformation, on a highway added to the fence is a highway.

Similarly, can a Web Service be invoked through the creation of XML -formatted message +http protocols in Oracle 's stored procedures? The answer is yes, there is a toolkit called utl_http in ORACLE that we can use to implement a stored procedure call to a Web Service.

The Aeai ESB mentioned above is one of the core products of a number of patency, can realize the function of Web service development and Web Service Registration, this article's interface sample, this consideration uses the ESB to create the Web service, but because of the need to introduce the content and the topic of this article is too far, so the Aeai DP development platform comes with a sample of Web services to illustrate. Readers interested in Aeai ESB can find out through the relevant links at the end of this article.

2. Prospective Readers

• Number of new employees
• The vast number of technology enthusiasts

3. Environmental Information

Operating System: Windows7

Oracle: version is oracle11g

Mysql : version is mysql5.1

JDK : jdk1.6.0_10

4. Noun interpretation

Aeai ESB: Application integration platform is mainly used as the " keel " of enterprise information system to integrate each business system, commonly calledEnterprise Service Bus, ESB), the application integration platform is named Aeai ESBin the product family of several smooth-linking software.

Aeai DP : The aeai DP Application development platform is dedicated to the development of MIS -Class Java Web applications, also known as miscdp(Misc develope Platform) Integrated application development platform. The aeai DP Application development platform also serves as a supporting tool for extended development in the number of unobstructed software product families, such as the development of Portlet components for the AEAI Portal Portal platform,Web Service and Http service, developing business process forms and functions for AEAI BPM Process Integration platform extensions.

stored Procedures : A large database system, a set of SQL statements to complete a specific function, stored in the database, after the first compilation after the call does not need to compile again, The user executes it by specifying the name of the stored procedure and giving the parameter (if the stored procedure has parameters).

utl_http:Oracle 's own HTTP Protocol Toolkit, which can be used to send post requests.

PL/SQL Developer: an integrated development environment , developed by Allround Automations Company, specifically for Oracle database storage Development of program units

5. Operation Procedure 5.1 Creating the Sample interface

Use the Aeai DP development platform to create applications with WS services, such as:

Select database Information

After you deploy your app, view the WS service that your app has just created

5.2 Creating a stored procedure 5.2.1 Basic Syntax

The following is the basic syntax for creating stored procedures

create OR REPLACE PROCEDURE stored procedure name (-- ) is define variable begin start pl/sql end description pl/sql

5.2.2 Creating Steps

1. Open PL/ SQL , and open a window

2 . Put the statement that created the stored procedure into it and execute the

This example of a stored procedure that invokes a Web service is created, and the following is a detailed sample SQL body

---Create a stored procedure, define four parameters, enter the parameter: userid,code,name; parameter: Resmark CREATE OR REPLACE PROCEDURE pro_test_ws (name in Varchar2,resmark off VARCHAR2) is --Define four variables, HTTP request, HTTP return, request message, return message Http_req utl_http. REQ; Http_resp utl_http. RESP; Request_env VARCHAR2 (32767); L_replyline VARCHAR2 (1000); BEGIN --Start PL/SQL body Request_env: = ' <soapenv:envelope xmlns:soapenv= "http://schemas.xmlsoap.org/soap/envelope/" xmlns:demo= "http// demo.service.wstest.agileai.com/"> <soapenv:Header/> <soapenv:Body> <demo:sayHi> <!--optional:--> <theGirlName> ' | | name | | ' </theGirlName> </demo:sayHi> </soapenv:Body> </soapenv:Envelope> ‘; --Print Request message Dbms_output.put_line (REQUEST_ENV); --Request WS Address Http_req: = Utl_http. Begin_request (' http://localhost:6060/cam/services/UserSync?wsdl ', ' POST ', Utl_http.http_version_1_1); -- Maintain Connection Status Utl_http.set_persistent_conn_support (Http_req, TRUE); -- Set Encoding Utl_http.set_header (http_req, ' content-type ', ' text/xml;charset=utf-8 '); Utl_http.set_header (http_req, ' soapaction ', '); --Set character sets Utl_http.set_body_charset (http_req, ' utf-8 '); --This parameter represents how long I send the post message, not less Utl_http.set_header (http_req, ' Content-length ', LENGTHB (request_env)); Utl_http.write_line (Http_req, request_env); --Assignment HTTP return HTTP_RESP: = Utl_http.get_response (Http_req); --Assigning the request message to L_replyline Utl_http.read_text (Http_resp, l_replyline); Dbms_output.put_line (L_replyline); --Pay the stored procedure out of the argument Resmark:=l_replyline; END Pro_test_ws;

5.2.3 Key points Description

Instructions for how to use the Utl_http Toolkit to invoke Web services in stored procedures in several key ways

1. Get the HTTP Request object by setting the request address, mode, protocol version

Http_req: = Utl_http. Begin_request (' http://localhost:6060/wstest_project/services/HelloWorld?wsdl ', ' POST ', Utl_http.http_version_1_1);

2. Set the protocol to remain in a connected state

Utl_http.set_persistent_conn_support (Http_req, TRUE);

3. Set the request encoding, the value of the SOAPAction header is a null string ("") indicating that the destination of the SOAP message is identified by the URI of the HTTP request, and no value indicates the destination for which the message was not specified.

Utl_http.set_header (http_req, ' content-type ', ' text/xml;charset=utf-8 '); Utl_http.set_header (http_req, ' soapaction ', ');

4. Setting the character set

Utl_http.set_body_charset (http_req, ' utf-8 ');

5. Message length

Utl_http.set_header (http_req, ' Content-length ', LENGTHB (request_env));

6. Invoking the service, sending the message

Utl_http.write_line (Http_req, request_env);

7. Get the return body

HTTP_RESP: = Utl_http.get_response (Http_req);

8. Assigning a return message to a variable

Utl_http.read_text (Http_resp, l_replyline);

5.3 Calling a stored procedure 5.3.1 using PL/SQL Developer testing

1) Select the name of the stored procedure, right-click the test, go to the test page

2) Add the parameter value of the response, F9 or click the button to start execution, you can see the return value after execution

3) switch to the DBMS output page to see what is printed

5.3.2 Using SQL code to call

DECLARE Resmark VARCHAR2 (1000); BEGIN Pro_test_ws (' Xiao Zheng ', resmark); Dbms_output. Put_Line (Resmark); END;

1) Open the SQL window and execute the SQL statement above

2) View output information

1 information printed for the stored procedure, 2 for printed output at call time

6. Summary Notes

This article describes the use of the UTL_HTTP Toolkit in Oracle stored procedures to invoke Web Service by creating request messages and using the HTTP protocol, from the configuration of creating Oracle stored procedures and UTL_HTTP-related parameters, through PL/SQL Developer test calls and SQL code are used for detailed instructions.

Attachments create SQL, invoke SQL, and interface programs and related database files for stored procedures.

7. RELATED LINKS

AEAI DP Development Platform/AEAI ESB integrated Platform related media and documentation address: http://www.agileai.com/portal/website/01/res-share.ptml

Documentation and code attachments download :http://pan.baidu.com/s/1kVyMVQn

Oracle stored procedure calls Web Service