ORACLE stored procedure calls Web Service, oracle Stored Procedure

ORACLE stored procedure calls Web Service, oracle Stored Procedure
1. Overview

In the recent ESB project, most of the customer's service calls between systems are performed in the oracle stored procedure. This article describes how to call the web service in the oracle stored procedure. Other mainstream databases, such as mysql and SQL service, do not describe how to call web service. This article mainly describes how to call Web Service through oracle stored procedures.

As we all know, when a Web Service sends a request and receives a result through the HTTP protocol, the request content and result content are encapsulated in XML format, and some specific HTTP message headers are added, to describe the content format of HTTP messages, these specific HTTP message headers and XML content formats are the SOAP protocol. The SOAP protocol is based on the HTTP protocol, and the relationship between the two is like that highway is transformed based on a Common Highway. After an isolation column is added to a highway, it becomes a highway.

Likewise, in oracle's stored procedures, can we create XML-format packets + HTTP protocol to call Web Services? The answer is yes. in ORACLE, there is a toolkit named UTL_HTTP. We can use this toolkit to call Web services through stored procedures.

The aeai esb mentioned above is one of the core products of shutong changlian. It can implement WEB service development, WEB Service Registration, and other functions. The interface Example in this article is to use the WEB Service created by ESB, however, the content to be introduced is too far away from the topic of this article, so we use the Web Service sample provided by the aeai dp development platform. If you are interested in aeai esb, you can find it through the link at the end of this article.

2. Expected readers

• Shutong changlian New Employee
• Technology enthusiasts

3. Environment Information

Operating System:Windows 7

Oracle:The version is oracle11g.

Mysql:Version: mysql5.1

Jdk:Jdk1.6.0 _ 10

4. Glossary

AEAI ESB: The application integration platform is mainly used as the "keel" of the Enterprise information system to integrate various business systems. It is generally called the Enterprise Service BUS (ESB ), the application integration platform is named aeai esb in the product family of shutong changlian software.

AEAI DP:The aeai dp application development Platform is specially used to develop MIS-class Java Web applications, also known as Misc Develope Platform integrated application development Platform. The aeai dp Application Development Platform also serves as a supporting tool for extended development in the family of shutong changlian software products, such as the development of Portlet components, Web Services and Http Services for the AEAI Portal platform extension; expand and develop business process forms and functions for the aeai bpm process integration platform.

Stored Procedure: In a large database system, a group of SQL statement sets for specific functions are stored in the database. After the first compilation, it is called again and does not need to be re-compiled, you can run a stored procedure by specifying its name and providing parameters (if the stored procedure has parameters.

UTL_HTTP: The HTTP toolkit that comes with oracle and can be used to send post requests.

PL/SQL Developer:An integrated development environment developed by Allround Automations to develop program units for Oracle database storage

5. Procedure 5.1 create a sample Interface

Use the aeai dp Development Platform to create applications with built-in WS services, such:

 

Select database information

After deploying the application, view the WS service that comes with the created application.

 

5.2 create a stored procedure 5.2.1 basic syntax

The basic syntax for creating a stored procedure is as follows:

Create or replace procedure stored PROCEDURE name (-- Define parameters) IS Define Variables BEGIN Start PL/SQL body END End of PL/SQL

5.2.2 creation steps

1. Open PL/SQL and an SQL window

2. Place and execute the statement used to create a stored procedure

 

In this way, a stored procedure sample for calling web service is created. The following is a detailed example of the SQL body.

-- Create a stored procedure and define four parameters: userid, code, and name; output parameter: resmark Create or replace procedure pro_test_ws (name in varchar2, resmark out varchar2) IS -- Defines four variables: http request, http return, request message, and return message. Http_req UTL_HTTP.REQ; Http_Resp UTL_HTTP.RESP; Request_env VARCHAR2 (32767 ); Rochelle 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 ); --Keep connection 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 indicates how long the POST message is sent. Utl_Http.Set_Header (http_req, 'content-length', Lengthb (request_env )); Utl_Http.Write_Line (http_req, request_env ); -- Return http with a value assigned Http_Resp: = Utl_Http.Get_Response (http_req ); -- Assign the request message to l_Replyline Utl_Http.Read_Text (http_Resp, l_Replyline ); Dbms_output.put_line (l_Replyline ); -- Pay the output parameter of the stored procedure Resmark: = l_Replyline; END pro_test_ws;

5.2.3 key points

How to use several key methods when using the UTL_HTTP toolkit to call web services during storage

1. Get the http request object by setting the request address, method, and 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 Protocol persistence connection

Utl_Http.Set_Persistent_Conn_Support (http_req, TRUE );

3. Set the Request Encoding. The value of the SOAPAction header is an empty string (""), indicating that the SOAP Message destination is identified by the HTTP request URI. If no value is set, the Message destination is not specified.

Utl_Http.Set_Header (http_req, 'content-type', 'text/xml; charset = UTF-8 '); Utl_Http.Set_Header (http_req, 'soapaction ','');

4. Set character sets

Utl_Http.Set_Body_Charset (http_req, 'utf-8 ');

5. Message Length

Utl_Http.Set_Header (http_req, 'content-length', Lengthb (request_env ));

6. Call the service and send packets

Utl_Http.Write_Line (http_req, request_env );

7. Get the response body.

Http_Resp: = Utl_Http.Get_Response (http_req );

8. Assign the returned message to the variable.

Utl_Http.Read_Text (http_Resp, l_Replyline );

5.3 call a stored procedure 5.3.1 Use PL/SQL Developer Test

1) Right-click the name of the stored procedure and choose test to go to the test page.

 

2) Add the response parameter value. F9 or click the button to start execution. The returned value is displayed after execution.

 

3) switch to the DBMS output page to view the printed content.

5.3.2 use SQL code to call

DECLARE Resmark varchar2 (1000 ); BEGIN Pro_test_ws ('zheng ', resmark ); DBMS_OUTPUT.PUT_LINE (resmark ); END;

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

2) view output information

1 is the information printed in the stored procedure, 2 is the information printed during the call.

6. Summary

This article describes how to use the UTL_HTTP toolkit to create request packets and call Web services using the HTTP protocol in the oracle stored procedure, and how to configure the UTL_HTTP parameters in the oracle stored procedure, to call through PL/SQL Developer Test Call and SQL code for detailed instructions.

The attachment creates SQL statements, calls SQL statements, interface programs, and related database files for stored procedures.

7. Related Links

Aeai dp Development Platform/aeai esb integration platform related media and documentation address: http://www.agileai.com/portal/website/01/res-share.ptml

 

Download documents and code attachments: Http://pan.baidu.com/s/1kVyMVQn