从Oracle存储过程访问Web服务

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

这是我到目前为止发现的一些参考文献

Database Web Services
数据库Web服务
Calling external Web Service from a Java Stored Procedure
从Java存储过程调用外部Web服务

..Just to clarify, this is for SOAP calls

..澄清一下，这是针对SOAP调用的

2 个解决方案

#1

First off, what sort of web service are you calling? I am assuming either SOAP or REST.

首先，你打电话给什么样的网络服务？我假设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这样的东西上可能是有意义的（但这不是什么，但是，你通常会得到在几个小时内工作）。

#2

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，通过调用传递它，并解析返回值。

We developed this solution on a 9i database so we haven't looked into UTL_DBWS yet. It works great, though.

我们在9i数据库上开发了这个解决方案，所以我们还没有研究过UTL_DBWS。但它很棒。

#1