oracle 存储过程 发送WebServie 请求

create or replace procedure CMS_TO_ESB_INTEGRAL is
--调用ESB积分错误是重发接口 
  http_req    utl_http.req;
  http_resp   utl_http.resp;
  request_env clob;
  l_replyline varchar2(2000);
  response_xml varchar2(2000);
  r_ret_status  varchar2(20);

  errorException exception;
  errorCode number;
  errorMsg  varchar2(1000);

  l_body_len number;
  l_offset number;
  l_max_buffer_len number := 255;
  l_buffer varchar2(500);

begin
--查询业务数据  可不要
 FOR REMIN_LIST IN(
       select a.again_id,a.mobile,a.point_operation,to_char(a.create_date,'yyyy-MM-dd hh24:mi:ss') createTime,
              a.points,a.order_no,a.again_num
            FROM  表名 a
           where a.isenabled = 3 and a.again_num < 5
       )
   LOOP
--请求报文
 request_env := '<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"  xmlns:v1="http://www.sxqc.com/osb/Sale/DSBonus/Ds/Schema/v1.0-Get">
                  <soapenv:Header/>
                  <soapenv:Body>
                   <v1:Request>
                     <v1:RequestCollections> 
                         <v1:RequestCollection>
                              <v1:MbrMobile>'|| REMIN_LIST.mobile  ||'</v1:MbrMobile>
                              <v1:PointOperation>'|| REMIN_LIST.point_operation ||'</v1:PointOperation>
                              <v1:CreateTime>'|| REMIN_LIST.createTime ||'</v1:CreateTime>
                              <v1:Points>'|| REMIN_LIST.points ||'</v1:Points>
                              <v1:OrderNo>'|| REMIN_LIST.order_no ||'</v1:OrderNo>
                         </v1:RequestCollection>
                      </v1:RequestCollections>
                    </v1:Request>
                 </soapenv:Body>
                 </soapenv:Envelope>';

      http_req := utl_http.begin_request('http:/.......yncProxy?wsdl',
                                         'POST',
                                         utl_http.HTTP_VERSION_1_1);

      utl_http.set_authentication(http_req, '账号', '密码');
      utl_http.set_persistent_conn_support(http_req, TRUE);
      utl_http.set_header(http_req, 'Content-Type', 'text/xml;charset=utf-8');
      utl_http.set_header(http_req, 'SOAPAction', '');
      utl_http.set_body_charset(http_req, 'utf-8');
      utl_http.set_header(http_req, 'Content-Length',dbms_lob.getlength(request_env));
      l_body_len := dbms_lob.getlength(request_env);

--打印请求报文信息
      l_offset := 1;
      l_buffer := NULL;
      WHILE l_offset < l_body_len loop
        dbms_lob.read(lob_loc => request_env
                      ,amount => l_max_buffer_len
                      ,offset => l_offset
                      ,buffer => l_buffer);
        l_offset := l_offset + l_max_buffer_len;
        utl_http.write_text(http_req, l_buffer);
        dbms_output.put_line(l_buffer);
      END LOOP;

      http_resp := utl_http.get_response(http_req);
      utl_http.read_text(http_resp, l_replyline);
      utl_http.end_response(http_resp);

      response_xml := l_replyline;
      response_xml := replace(response_xml,'soapenv:','');
      response_xml := replace(response_xml,'soap:','');
      response_xml := replace(response_xml,'ns2:','');

--解析返回报文
      SELECT RET_STATUS INTO r_ret_status
        FROM XMLTABLE('$B/Envelope/Body/Response' PASSING
                       XMLTYPE(response_xml) AS B COLUMNS
                       RET_STATUS VARCHAR2(50) PATH '/Response/L_RET_STATUS',
　　　　　　　　　　　　　　RET_MESSAGE VARCHAR2(50) PATH '/locationResp/L_RET_MESSAGE');
--处理业务
      IF r_ret_status = 'E' THEN
           UPDATE 表名 SET AGAIN_NUM = REMIN_LIST.again_num+1 WHERE AGAIN_ID = REMIN_LIST.again_Id;
      ELSE
           DELETE FROM 表名 WHERE AGAIN_ID = REMIN_LIST.again_Id;
      END IF; 
      response_xml := l_replyline;
     COMMIT; 
  END LOOP;

EXCEPTION
  when errorException then
    utl_http.end_response(http_resp);
    errorCode    := SQLCODE;
    errorMsg     := SUBSTR(SQLERRM, 1, 200);
    r_ret_status := 'E';
    response_xml := 'l_ret_status=' || r_ret_status || ',errorCode=' ||
                    errorCode || ',errorMsg=' || errorMsg;
  when others then
    utl_http.end_response(http_resp);
    errorCode    := SQLCODE;
    errorMsg     := SUBSTR(SQLERRM, 1, 200);
    r_ret_status := 'E';
    response_xml := 'l_ret_status=' || r_ret_status || ',errorCode=' ||
                    errorCode || ',errorMsg=' || errorMsg;

end CMS_TO_ESB_INTEGRAL;