ORACLE PL/SQL调用WEBSERVICE多种方式详解

在使用PL/SQL访问HTTP网站时一般使用工具包UTL_HTTP调用webservice。

调用基于json数据传输的restful webservice

以文本方式传输request内容

   l_rest_requst := utl_url.escape(p_requst_data,
                                   TRUE,
                                   'utf8');

   l_http_url := p_http_url;
   -- preparing Request...
   l_http_request := utl_http.begin_request(l_http_url,
                                            'POST',
                                            'HTTP/1.1');

   -- ...set header's attributes
   utl_http.set_header(l_http_request,
                       'Content-Type',
                       'application/json;charset="utf-8"');

   utl_http.set_header(l_http_request,
                       'Content-Length',
                       length(l_rest_requst));

   utl_http.write_text(l_http_request,
                       l_rest_requst);

   l_http_response := utl_http.get_response(l_http_request);
   dbms_output.put_line(l_http_response.status_code);
   utl_http.read_text(l_http_response,
                      l_soap_respond);
   l_soap_respond := utl_url.unescape(l_soap_respond,
                                      'utf8');
   utl_http.end_response(l_http_response);

以二进制方式传输request内容

传输的数据中如果有中文，用文本方式传输通常会返回400错误，这时可以将传输的json串转换成raw类型，并且设置transfer-encoding为chunked。

  l_soap_request := utl_i18n.string_to_raw(p_requst_data,
                                            'UTF8');

   l_http_url     := p_http_url;
   utl_http.set_transfer_timeout(3600);
   -- preparing Request...
   l_http_request := utl_http.begin_request(l_http_url,
                                            'POST',
                                            'HTTP/1.1');
   get_token(x_return_status => x_return_status,
             x_msg_count     => x_msg_count,
             x_msg_data      => x_msg_data,
             p_token_str     => l_token_str,
             p_token_type    => l_token_type);
   
   utl_http.set_header(l_http_request,
                       'Authorization',
                       l_token_type || ' ' || l_token_str
                      );

   utl_http.set_header(l_http_request,
                       'Content-Type',
                       'application/json;charset="utf-8"'
                       );
   UTL_HTTP.set_header(l_http_request, 'Transfer-Encoding', 'chunked');
   UTL_HTTP.SET_HEADER(l_http_request, 'User-Agent', 'Mozilla/4.0');
   utl_http.set_body_charset('UTF-8');
   utl_http.write_text(l_http_request, UTL_TCP.crlf || UTL_TCP.crlf);
   utl_http.write_raw(l_http_request,
                      l_soap_request);

   l_http_response := utl_http.get_response(l_http_request);

   utl_http.read_raw(l_http_response,
                     l_soap_respond);

   utl_http.end_response(l_http_response);
   l_soap_respond_text := utl_raw.cast_to_varchar2(l_soap_respond);

调用基于xml数据传输的soap webservice

// A code block
    l_http_request := utl_http.begin_request(p_http_url,
                                             'POST',
                                             'HTTP/1.1');
    utl_http.set_header(l_http_request,
                        'Content-Type',
                        'text/xml; charset=utf-8');
    utl_http.set_header(l_http_request,
                        'Content-Length',
                        length(p_request_data));
    utl_http.set_header(l_http_request,
                        'SOAPAction',
                        p_http_url);
    utl_http.write_text(l_http_request,
                        p_request_data);
    l_http_response := utl_http.get_response(l_http_request);
    utl_http.read_text(l_http_response,
                       l_buffer);
    utl_http.end_response(l_http_response);

获取token的函数

前面用到的get_token的方法也是用utl_http这个包，关键是要先知道从对方的哪个链接能够获取token,以及对方返回token时格式，代码片段如下：

    l_base64     := utl_encode.base64_encode(utl_i18n.string_to_raw(l_username || ':' ||
                                                                    l_password,
                                                                    'AL32UTF8'));
    l_base64_str := utl_raw.cast_to_varchar2(l_base64);
  
    SELECT REPLACE(REPLACE(l_base64_str,
                           chr(13),
                           ''),
                   chr(10),
                   '')
      INTO l_base64_str1
      FROM dual;
  

    l_http_request := utl_http.begin_request(l_http_url,
                                             'POST',
                                             'HTTP/1.1');
  
    utl_http.set_header(l_http_request,
                        'Authorization',
                        'Basic ' || l_base64_str1);
  
    utl_http.write_raw(l_http_request,
                       l_soap_requst);
  
    l_http_response := utl_http.get_response(l_http_request);
  
    IF l_http_response.status_code = utl_http.http_ok THEN
      NULL;
      dbms_output.put_line(l_http_response.status_code);
    ELSE
      x_return_status := fnd_api.g_ret_sts_error;
      raise_exception(x_return_status);
    END IF;
  
    BEGIN
      utl_http.read_text(l_http_response,
                         l_soap_respond);
      utl_http.end_response(l_http_response);
    
      dbms_output.put_line(l_soap_respond);
    
      p_token_str  := substr(l_soap_respond,
                             instr(l_soap_respond,
                                   '":"',
                                   1,
                                   1) + 3,
                             instr(l_soap_respond,
                                   '","',
                                   1,
                                   1) - instr(l_soap_respond,
                                              '":"',
                                              1,
                                              1) - 3);
      p_token_type := substr(l_soap_respond,
                             instr(l_soap_respond,
                                   '":"',
                                   1,
                                   2) + 3,
                             instr(l_soap_respond,
                                   '","',
                                   1,
                                   2) - instr(l_soap_respond,
                                              '":"',
                                              1,
                                              2) - 3);
    
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;