Java 从 Oracle 存储过程访问 Web 服务
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/299002/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Access Web service from Oracle stored procedure
提问by rich
Is there anybody who has successfully accessed a Web service from an Oracle stored procedure? If so, was it a Java stored procedure? A PL/SQL stored procedure?
是否有人从 Oracle 存储过程成功访问了 Web 服务？如果是，它是 Java 存储过程吗？PL/SQL 存储过程？
Is there any reason why I should not be trying to access a WS from a stored proc?
有什么理由我不应该尝试从存储过程访问 WS？
Here are a couple refs that I found so far
这是我到目前为止找到的几个参考
..Just to clarify, this is for SOAP calls
..澄清一下，这是针对 SOAP 调用的
采纳答案by Justin Cave
First off, what sort of web service are you calling? I am assuming either SOAP or REST.
首先，您要调用哪种 Web 服务？我假设是 SOAP 或 REST。
For REST web services, UTL_HTTP is often more than sufficient, combined with a bit of XPath in a simple PL/SQL stored procedure.
对于 REST Web 服务，UTL_HTTP 通常绰绰有余，在简单的 PL/SQL 存储过程中结合一些 XPath。
For SOAP web services, it depends on how sophisticated you need (or want) to be. You can certainly use XQuery to create an XML document that meets the spec for the web service, use UTL_HTTP to post the document and get the response, and then use some XPath to parse the response all in PL/SQL. This is a relatively manual and relatively brute-force solution, but if you are talking about a handful of web services, it involves a minimum of infrastructure and the calls can get knocked together pretty quickly.
对于 SOAP Web 服务，这取决于您需要（或想要）的复杂程度。您当然可以使用 XQuery 创建符合 Web 服务规范的 XML 文档，使用 UTL_HTTP 发布文档并获取响应，然后使用一些 XPath 解析所有在 PL/SQL 中的响应。这是一个相对手动且相对蛮力的解决方案，但如果您谈论的是少数 Web 服务，它只涉及最少的基础设施，并且调用可以很快地组合在一起。
If you expect the calls to evolve over time or you expect there to be a number of procedures calling a number of web services, it probably makes sense to invest time in something like UTL_DBWS (this isn't something, though, that you generally get working in a couple hours).
如果您希望调用会随着时间的推移而演变，或者您希望有多个过程调用多个 Web 服务，那么在 UTL_DBWS 之类的东西上投入时间可能是有意义的（尽管这不是您通常会得到的东西）在几个小时内工作）。
回答by kurosch
It's fairly simple to wrap UTL_HTTP in a convenience function:
将 UTL_HTTP 包装在一个方便的函数中是相当简单的：
FUNCTION post
(
p_url IN VARCHAR2,
p_data IN CLOB,
p_timeout IN BINARY_INTEGER DEFAULT 60
)
RETURN CLOB
IS
--
v_request utl_http.req;
v_response utl_http.resp;
v_buffer CLOB;
v_chunk VARCHAR2(4000);
v_length NUMBER;
v_index NUMBER;
BEGIN
v_index := 1;
v_length := nvl(length(p_data), 0);
-- configure HTTP
utl_http.set_response_error_check(enable => FALSE);
utl_http.set_detailed_excp_support(enable => FALSE);
utl_http.set_transfer_timeout(p_timeout);
-- send request
v_request := utl_http.begin_request(p_url, 'POST','HTTP/1.0');
utl_http.set_header(v_request, 'Content-Type', 'text/xml');
utl_http.set_header(v_request, 'Content-Length', v_length);
WHILE v_index <= v_length LOOP
utl_http.write_text(v_request, substr(p_data, v_index, 4000));
v_index := v_index + 4000;
END LOOP;
-- check HTTP status code for error
IF v_response.status_code <> utl_http.http_ok THEN
raise_application_error(
cn_http_error,
v_response.status_code || ' - ' || v_response.reason_phrase
);
END IF;
-- get response
dbms_lob.createtemporary(v_buffer, FALSE);
v_response := utl_http.get_response(v_request);
BEGIN
LOOP
utl_http.read_text(v_response, v_chunk, 4000);
dbms_lob.writeappend(v_buffer, length(v_chunk), v_chunk);
END LOOP;
EXCEPTION
WHEN utl_http.end_of_body THEN NULL;
END;
utl_http.end_response(v_response);
RETURN v_buffer;
END;
Then you just need something to POST a SOAP envelope:
然后你只需要一些东西来发布一个 SOAP 信封：
FUNCTION invoke
(
p_url IN VARCHAR2,
p_method IN XMLTYPE,
p_timeout IN NUMBER := 60
)
RETURN XMLTYPE
IS
-- calls the given SOAP service
cn_procedure_name CONSTANT VARCHAR2(30) := 'invoke';
--
v_envelope XMLTYPE;
v_response CLOB;
v_fault XMLTYPE;
v_sqlerrm VARCHAR2(2000);
BEGIN
-- wrap method in SOAP envelope
SELECT
XMLElement(
"soap:Envelope",
XMLAttributes(
'http://schemas.xmlsoap.org/soap/envelope/' AS "xmlns:soap"
),
XMLElement(
"soap:Body",
p_method
)
)
INTO
v_envelope
FROM
dual;
-- POST request
v_response := post(
p_url,
'<?xml version="1.0" encoding="ISO-8859-1"?>' || chr(10) || v_envelope.getClobVal(),
p_timeout
);
IF v_response IS NULL THEN
RAISE null_response;
END IF;
-- parse response
BEGIN
v_envelope := XMLType(v_response);
EXCEPTION
WHEN OTHERS THEN
v_sqlerrm := SQLERRM;
RAISE xml_parse_error;
END;
-- check for a fault
v_fault := v_envelope.extract(
'/soap:Envelope/soap:Body/soap:Fault',
'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"'
);
IF v_fault IS NOT NULL THEN
v_sqlerrm := v_fault.extract('.//faultstring/text()').getStringVal();
RAISE soap_fault;
END IF;
-- the actual response is the child of the <soap:Body /> element
RETURN v_envelope.extract(
'/soap:Envelope/soap:Body/*[position() = 1]',
'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"'
);
END;
Note that I removed our exception handling block as it's not particularly relevant to the example.
请注意，我删除了我们的异常处理块，因为它与示例无关。
With that, you can have any other procedure generate the XML necessary to call a service, pass it through invoke, and parse the return value.
有了它，您可以让任何其他过程生成调用服务所需的 XML，通过 invoke 传递它，并解析返回值。
We developed this solution on a 9i database so we haven't looked into UTL_DBWS yet. It works great, though.
我们在 9i 数据库上开发了这个解决方案，所以我们还没有研究 UTL_DBWS。不过，它效果很好。
