例子2：数据库传快递信息到外部系统post

 procedure cux_ky(l_om_express_rec cux_om_express_t%rowtype,l_out_ky_number out varchar2,l_out_flag out varchar2) is
    PRAGMA AUTONOMOUS_TRANSACTION ;
    --l_soap_request varchar2(32767);
    l_format varchar2(30) := 'JSON';
    l_method varchar2(50) := 'open.api.openCommon.batchOrder';
    l_sign varchar2(50);
    l_token varchar2(500);
    l_timestamp number;
    d_date date;
    l_resp_code number;
    l_resp_code_qty number;

    l_http varchar2(200) := 'https://openxxxx.com';--prod
    l_leng             NUMBER;
    l_http_req         utl_http.req;
    l_http_resp        utl_http.resp;
    l_soap_respond_val VARCHAR2(32767);
    l_soap_end_val     VARCHAR2(32767);
    L_SJ_TEL VARCHAR2(50);
    l_customerCode varchar2(30) := '075528902030';  
    l_platformFlag varchar2(50) := 'qw312415511'; 
    l_paymentCustomer   varchar2(50) :='075525131031';  

    
  begin
    utl_http.set_wallet('file:/home/oracle/ky', 'password');--测试生产都是 一样的钱包
    l_soap_request := '{';   ---因为中间不能有空格  只能拼接
    l_soap_request := l_soap_request || '"customerCode":"' || l_customerCode || '"';
    l_soap_request := l_soap_request || ',"platformFlag":"' || l_platformFlag || '"';
    l_soap_request := l_soap_request || ',"orderInfos":[{"preWaybillDelivery":{';
    l_soap_request := l_soap_request || '"companyName":"' || l_om_express_rec.jj_company || '"';
    l_soap_request := l_soap_request || ',"person":"' || l_om_express_rec.jj_name || '"';
    l_soap_request := l_soap_request || ',"phone":"' || l_om_express_rec.jj_tel || '"';
    l_soap_request := l_soap_request || ',"address":"' || l_om_express_rec.jj_address || '"}';
    l_soap_request := l_soap_request || ',"preWaybillPickup":{';
    l_soap_request := l_soap_request || '"companyName":"' || l_om_express_rec.sj_company || '"';
    l_soap_request := l_soap_request || ',"person":"' || l_om_express_rec.sj_name || '"';
    SELECT REPLACE(l_om_express_rec.sj_tel,' ') INTO L_SJ_TEL FROM DUAL;
    l_soap_request := l_soap_request || ',"phone":"' || L_SJ_TEL || '"';
    l_soap_request := l_soap_request || ',"address":"' || l_om_express_rec.sj_address || '"}';
    l_soap_request := l_soap_request || ',"serviceMode":' || l_om_express_rec.expresstypeid ;
    l_soap_request := l_soap_request || ',"payMode":' || l_om_express_rec.paymethod ;
    l_soap_request := l_soap_request || ',"paymentCustomer":"' || l_paymentCustomer || '"' ;
    l_soap_request := l_soap_request || ',"dismantling":10' ;
    l_soap_request := l_soap_request || ',"goodsType":"' || l_om_express_rec.product_name || ':' || l_om_express_rec.attribute2 || 'PCS"' ;
    l_soap_request := l_soap_request || ',"count":' || l_om_express_rec.parcelqty   ;
    l_soap_request := l_soap_request || ',"actualWeight":' || l_om_express_rec.attribute6   ;
    l_soap_request := l_soap_request || ',"orderId":"' || l_om_express_rec.pl_delivery_name || '"';
    l_soap_request := l_soap_request || ',"receiptFlag":' || l_om_express_rec.attribute4   ;
    if l_om_express_rec.attribute4 = '10' then
      l_soap_request := l_soap_request || ',"receiptCount":' || l_om_express_rec.attribute5   ;
    end if;
    l_soap_request := l_soap_request || ',"goodsTime":"' ||  to_char(l_om_express_rec.attribute3,'yyyy-mm-dd hh24:mi') || '"' ;
    l_soap_request := l_soap_request || ',"waybillRemark":"' || l_om_express_rec.remark ||  l_om_express_rec.attribute7 || '"' ;
    l_soap_request := l_soap_request || ',"repeatCheck":"10"' ;
    l_soap_request := l_soap_request || '}]}';
    
  

  l_http_req := utl_http.begin_request(l_http,
                                         'POST',
                                         utl_http.http_version_1_1);
  utl_http.set_header(l_http_req, 'Content-Type', 'application/json');     
  utl_http.set_header(l_http_req, 'appkey', l_appkey);
  utl_http.set_header(l_http_req, 'format', l_format);
  SELECT (SYSDATE - TO_DATE('1970-1-1 8', 'YYYY-MM-DD HH24')) * 86400000 + TO_NUMBER(TO_CHAR(SYSTIMESTAMP(3), 'FF')) into l_timestamp FROM DUAL;
  utl_http.set_header(l_http_req, 'timestamp', l_timestamp);
  utl_http.set_header(l_http_req, 'method', l_method);
  l_sign := md5(l_appsecret || l_timestamp || l_soap_request);
  utl_http.set_header(l_http_req, 'sign', l_sign);
  begin
  select t.token
    into l_token
    from cux_ky_token t 
   where t.start_date < sysdate
     and t.end_date > sysdate;
  exception
    when others then
      l_token  := get_token;
  end;  
  utl_http.set_header(l_http_req, 'token', l_token);
  utl_http.set_body_charset(l_http_req, 'UTF-8');
  l_leng := lengthb(l_soap_request);  
  utl_http.set_header(l_http_req, 'Content-Length', l_leng);
  utl_http.write_text(l_http_req, l_soap_request); 
  
  dbms_output.put_line(l_token);
  dbms_output.put_line(l_timestamp);
  dbms_output.put_line(l_sign);
  dbms_output.put_line(l_soap_request);
  
  l_http_resp := utl_http.get_response(l_http_req);  
  IF l_http_resp.status_code = '200' THEN
      utl_http.read_line(l_http_resp, l_soap_respond_val, TRUE);
      l_resp_code := q_json_number(REPLACE(SUBSTR(l_soap_respond_val,1,1000),'\'),'code',1);      
      if l_resp_code = 10000 then
         l_resp_code_qty := q_json_number(REPLACE(SUBSTR(l_soap_respond_val,1,1000),'\'),'successTotal',1);
         if l_resp_code_qty = 1 then
           l_out_flag := 'Y';
           l_out_KY_NUMBER := q_json(REPLACE(SUBSTR(l_soap_respond_val,1,1000),'\'),'waybillNumber',1);
         else
           l_out_flag := 'E';
           l_out_KY_NUMBER := q_json(REPLACE(SUBSTR(l_soap_respond_val,1,1000),'\'),'message',1);
         end if;
      ELSIF l_resp_code IN (6000,6001,6002,6003) THEN  --TOKEN  invalid
        utl_http.end_response(l_http_resp);
        utl_http.end_request(l_http_req);
        cux_ky_pkg.cux_ky_again(l_om_express_rec ,l_out_ky_number ,l_out_flag );
      else
         l_out_flag := 'E';
         l_out_KY_NUMBER := '失败';
      end if;
      dbms_output.put_line(l_soap_respond_val);
      dbms_output.put_line(l_out_ky_number);
  END IF;
  utl_http.end_response(l_http_resp);
  utl_http.end_request(l_http_req);                               
  INSERT INTO CUX_OM_EXPRESS_T_LOG
  VALUES(l_om_express_rec.pl_delivery_name,SUBSTR(l_soap_request ,1,4000),SUBSTR(l_soap_respond_val,1,4000));
  COMMIT;
  
  
  --dbms_output.put_line(l_appsecret || '-' || l_timestamp || '-' || l_soap_request);
  --dbms_output.put_line(md5str(l_appsecret || l_timestamp || l_soap_request));
  exception
    when others then
      utl_http.end_response(l_http_resp);
      utl_http.end_request(l_http_req);  
      INSERT INTO CUX_OM_EXPRESS_T_LOG
      VALUES(l_om_express_rec.pl_delivery_name,SUBSTR(l_soap_request ,1,4000),SUBSTR(l_soap_respond_val,1,4000));
      COMMIT;
  end;

 这种是参数都写在header里面

body-raw

  utl_http.write_text(l_http_req, l_soap_request); 

FUNCTION q_json(p_json   IN VARCHAR2,
                  p_json_z IN VARCHAR2,
                  p_flag   IN NUMBER) RETURN VARCHAR2 IS
  l_end_json varchar2(32767); 
  l_end_json1 varchar2(32767);
  BEGIN ---返回字符
    l_end_json:=substr(p_json, instr(p_json,p_json_z,1,p_flag)+length(p_json_z)+2);
    select substr(l_end_json,2,instr(l_end_json,'"',1,2) - instr(l_end_json,'"',1,1) - 1) into  l_end_json1 from dual;
    return l_end_json1;
  END;
  
  FUNCTION q_json_number(p_json   IN VARCHAR2,
                  p_json_z IN VARCHAR2,
                  p_flag   IN NUMBER) RETURN number IS
  l_end_json varchar2(32767); 
  l_end_json1 number;
  BEGIN--返回数字
    l_end_json:=substr(p_json, instr(p_json,p_json_z,1,p_flag)+length(p_json_z)+2);
    select substr(l_end_json,1,instr(l_end_json,',',1,1) - 1) into  l_end_json1 from dual;
    return l_end_json1;
  END;
  

由于没有json格式，所以只能写function获取json格式返回的结果值