Hi,
It is observed that if the data sending in webservice call using UTL_HTTP from Oracle has accented characters(like given below), the webservice call is not getting successful from Oracle. But the same call is successful from Postman tool.
Example data:
SACRAÑMENTO101
The content-length in postman and Oracle script includes the multibyte length. Character set is UTF-8.
Most blogs like below highlighted sending the correct length and character set when multibyte characters are involved in the data, which is not working in our system.
https://community.oracle.com/tech/apps-infra/discussion/4296483/unable-to-handle-brazilian-characters-in-a-webservice-call-through-plsql?
Also, when accented characters already exists in front end data, it is received in Oracle as below (for SACRAÑMENTO101, received as SACRAÑMENTO101). In postman response, no issue observed.
Can you please suggest on how to fix the issue. Below is the script. Receiving error after running for 10-15mins at the statement l_http_response := utl_http.get_response(l_http_request);
Errors:
ORA-29273: HTTP request failed
ORA-29263: HTTP protocol error
Script:
set serveroutput on;
DECLARE
l_http_request utl_http.req;
l_http_response utl_http.resp;
v_chars_per_chunk integer;
v_current_chars INTEGER;
v_clob_offset integer;
v_chunk_string VARCHAR2(32767 BYTE);
v_clob_length integer;
v_db_charset VARCHAR2(30 BYTE);
l_text VARCHAR2(32767);
enable BOOLEAN;
name VARCHAR2(1024);
value VARCHAR2(1024);
v_err VARCHAR2(4000);
v_substring_msg VARCHAR2(512);
v_raw_data RAW(512);
v_buffer_size NUMBER(10) := 512;
v_string_request VARCHAR2(4000);
v_clob_response CLOB;
l_buffer VARCHAR2(32767);
V_XML_LENGTH NUMBER;
BEGIN
utl_http.set_proxy('http://testproxy.app.test.com:80', 'test.com');
utl_http.set_wallet('file://orabin/gl/oracle/product/12.1.0.2/owm/wallets/test', 'test12');
L_HTTP_REQUEST := UTL_HTTP.BEGIN_REQUEST(URL => 'https://dev.testhost.com/api/suppliers/3298/', METHOD => 'PUT', HTTP_VERSION => 'HTTP/1.1');
v_string_request := '<?xml version="1.0" encoding="UTF-8"?><supplier><supplier-addresses><supplier-address><id>6919</id><name>SACRAÑMENTO101|287021</name><street1>1515</street1><street2></street2><city>Sacramento</city><state>CA</state><postal-code>44456</postal-code><active type="boolean">true</active><country><code>US</code></country></supplier-address></supplier-addresses></supplier>' ;
DBMS_LOB.createtemporary(v_clob_response, FALSE);
UTL_HTTP.SET_BODY_CHARSET('UTF-8');
utl_http.set_header(l_http_request, 'CONTENT-TYPE', 'application/xml;charset=UTF-8');
utl_http.set_header(l_http_request, 'Accept', 'application/xml');
utl_http.set_header(l_http_request, 'CONTENT-LENGTH', 369); --length includes multibyte character length also
utl_http.set_header(l_http_request, 'Authorization', 'Bearer 5fa4dc54b859e22ed27773a6ff8fc67be89ebaa04a25510864d3a8ba98491bf6');
v_raw_data := utl_raw.cast_to_raw(v_string_request);
utl_http.write_raw(r => l_http_request, data => v_raw_data);
--utl_http.write_text(l_http_request, v_string_request );
dbms_output.put_line('before get_response');
INSERT INTO TESTTAB VALUES('before get_response');
COMMIT;
l_http_response := utl_http.get_response(l_http_request); -- it is running forever here
INSERT INTO TESTTAB VALUES('AFTER get_response');
COMMIT;
FOR i IN 1..utl_http.get_header_count(l_http_response) LOOP
utl_http.get_header(l_http_response, i, name, value);
dbms_output.put_line(name
|| ': '
|| value);
END LOOP;
-- Loop through the response.
BEGIN
dbms_output.put_line('Response> status_code: "'
|| l_http_response.status_code
|| '"');
dbms_output.put_line('Response> reason_phrase: "'
|| l_http_response.reason_phrase
|| '"');
dbms_output.put_line('Response> http_version: "'
|| l_http_response.http_version
|| '"');
IF ( l_http_response.status_code = utl_http.http_ok ) THEN
dbms_output.put_line('response is okay');
ELSE
dbms_output.put_line('RESPONSE CODE IS ' || l_http_response.status_code);
/* BEGIN
LOOP
utl_http.read_line(l_http_response, l_text, true);
DBMS_LOB.writeappend (v_clob_response, LENGTH(l_text), l_text);
-- v_clob_response := v_clob_response||l_text;
dbms\_output.put\_line('buffer-->' || l\_text);
END LOOP;
END;\*/
END IF;
LOOP
utl\_http.read\_text(l\_http\_response, l\_text, 32766);
DBMS\_LOB.writeappend (v\_clob\_response, LENGTH(l\_text), l\_text);
-- v\_clob\_response := l\_clob\_response||l\_text;
dbms\_output.put\_line('after1');
DBMS\_OUTPUT.put\_line (l\_text);
dbms\_output.put\_line('after2');
END LOOP;
utl_http.end_response(l_http_response);
EXCEPTION
WHEN utl_http.end_of_body THEN
dbms_output.put_line('after end_of_body');
utl_http.end_response(l_http_response);
END;
--p_xml_type := XMLTYPE(v_clob_response);
--Insert into XXSYF_COUPA_XML_RESPONSE values (XXSYF_COUPA_XML_RESPONSE_SEQ.Nextval, p_xml_type);
commit;
DBMS_LOB.freetemporary(v_clob_response);
EXCEPTION
WHEN utl_http.end_of_body THEN
utl_http.end_response(l_http_response);
dbms_output.put_line('Exception');
WHEN UTL_HTTP.TOO_MANY_REQUESTS THEN
UTL_HTTP.END_RESPONSE(l_http_response);
WHEN OTHERS THEN
dbms\_output.put\_line('OTHERS ' || v\_clob\_response);
dbms\_output.put\_line('EXCEPTION' || sqlerrm);
dbms\_output.put\_line('v\_err:' || v\_err);
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;
/