背景：ebs系统和其他系统通过utl_http包调用接口，使用log方法记录日志。

某次调用接口，执行到记录日志行报字符或值错误。

查找原因，发现是p_str的长度超过的32767的限制。

解决办法：

PROCEDURE log(p_str VARCHAR2) IS  BEGIN    fnd_file.put_line(fnd_file.log, p_str);    dbms_output.put_line(p_str);  END;

--解决l_messge_clob长度超过32767导致的溢出问题，字符或值错误FOR i IN 0 .. trunc((dbms_lob.getlength(l_messge_clob) - 1) / l_step) LOOP  log(dbms_lob.substr(l_messge_clob, l_step, i * l_step + 1));END LOOP;--同样 utl_http.write_text 长度不能超过32767，buffer       VARCHAR2(2000);offset       NUMBER := 1;amount       NUMBER := 1024;--解决l_messge_clob长度超过3276导致的溢出问题，字符或值错误---utl_http.write_text(l_req, p_content);

UTL_HTTP.SET_BODY_CHARSET('UTF-8');

WHILE (offset < v_req_length) LOOP  dbms_lob.read(p_content, amount, offset, buffer);  utl_http.write_text(r => l_req, data => buffer);  offset := offset + amount;END LOOP;

 

---

DECLARE  v_doc_fin    CLOB := '';  req          utl_http.req;  res          utl_http.resp;  url          VARCHAR2(1900) := 'url xxx';  v_value      VARCHAR2(4000);  vchunkdata   VARCHAR2(2000);  v_req_length NUMBER;  buffer       VARCHAR2(32767);  offset       NUMBER := 1;  amount       NUMBER := 32767;  utl_err      VARCHAR2(1000);BEGIN  IF v_doc_fin IS NOT NULL THEN    --v_doc_fin is JSON DOC of CLOB data type from a procedure    v_req_length := dbms_lob.getlength(v_doc_fin);    dbms_output.put_line(v_req_length);    req := utl_http.begin_request(url, 'POST', 'HTTP/1.1');    utl_http.set_header(req, 'Content-Length', v_req_length);    utl_http.set_header(req, 'user-agent', 'mozilla/4.0');    utl_http.set_header(req,                        'content-type',                        'application/json;charset=UTF-8');    utl_http.set_header(req, 'Transfer-Encoding', 'chunked');    utl_http.set_body_charset('UTF-8');    WHILE (offset < v_req_length) LOOP      dbms_lob.read(v_doc_fin, amount, offset, buffer);      utl_http.write_text(r => req, data => buffer);      offset := offset + amount;    END LOOP;    res := utl_http.get_response(req);    BEGIN      LOOP        utl_http.read_line(res, v_value);        dbms_output.put_line(v_value);      END LOOP;      utl_http.end_response(res);    EXCEPTION      WHEN utl_http.end_of_body THEN        utl_http.end_response(res);      WHEN utl_http.too_many_requests THEN        utl_http.end_response(res);      WHEN OTHERS THEN        dbms_output.put_line(utl_http.get_detailed_sqlerrm);        dbms_output.put_line(dbms_utility.format_error_stack);        dbms_output.put_line(dbms_utility.format_error_backtrace);        dbms_output.put_line(dbms_utility.format_call_stack);    END;  END IF;EXCEPTION  WHEN OTHERS THEN    utl_http.end_response(res);    utl_err := utl_http.get_detailed_sqlerrm;END;

 

 

 

参考：

UTTL_HTTP to POST CLOB request

http://www.orafaq.com/forum/t/202946/