We have a routine that calls a webservice and retrieves the webservice response to a CLOB.
Scenario :
1) call webservice
2) get webservice response
The webservice response containt a BASE64 document encoding (so is a large response).
We use UTL_HTTP.read_text in a LOOP to read 32767 characters at a time and concatinates to a CLOB variable.
This routine works fine on 11G and 12C.
| The code: |
|---|
DECLARE
v_part VARCHAR2 (32767);
v_positie NUMBER;
v_offset NUMBER := 1;
v_start NUMBER;
v_einde NUMBER;
v_xml CLOB;
v_soaprequest CLOB;
v_soapresponse CLOB;
v_base64 CLOB;
v_httprequest UTL_HTTP.req;
v_httpresponse UTL_HTTP.resp;
endpointurl VARCHAR2(250) := 'LEEG';
namespace VARCHAR2(250) := 'LEEG';
username VARCHAR2(100) := 'LEEG';
password VARCHAR2(100) := 'LEEG';
BEGIN
UTL_HTTP.SET_PERSISTENT_CONN_SUPPORT(TRUE);
v_soaprequest := <soaprequest placeholder>;
endpointurl := <endpointurl placeholder>;
username := <username placeholder>;
password := <password placeholder>; v_httprequest := UTL_HTTP.begin_request ( endpointurl
, 'POST'
, utl_http.http_version_1_1
); UTL_HTTP.Set_Authentication (v_httprequest,username,password);
UTL_HTTP.set_header (v_httprequest, 'Content-Type', 'text/xml');
UTL_HTTP.set_header (v_httprequest, 'Content-Length', length(v_soaprequest));
UTL_HTTP.set_header (v_httprequest, 'SOAPAction', ''); v_offset := 1;
WHILE v_offset <= LENGTH(v_soaprequest) LOOP
UTL_HTTP.write_text (v_httprequest, DBMS_LOB.substr(v_soaprequest, 30000, v_offset));
v_offset := v_offset + 30000;
END LOOP; v_httpresponse := UTL_HTTP.get_response (v_httprequest);
DBMS_LOB.createtemporary (v_soapresponse, TRUE); BEGIN
LOOP
dbms_output.put_line('=============================================');
UTL_HTTP.read_text (v_httpresponse, v_part, 32767);
dbms_output.put_line(length(v_part));
DBMS_LOB.writeappend (v_soapresponse, dbms_lob.getLength(v_part), v_part);
END LOOP;
UTL_HTTP.end_response (v_httpresponse);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('detailed_sqlcode ['||utl_http.get_detailed_sqlcode||']');
dbms_output.put_line('detailed_sqlerrm ['||utl_http.get_detailed_sqlerrm||']');
UTL_HTTP.end_response (v_httpresponse);
END;
END;
/ |
On 19C we get the UTL_HTTP.protocol_error exception when retrieving the second part (32767) of the webservice response when UTL_HTTP.SET_PERSISTENT_CONN_SUPPORT is set to TRUE.
The code works fine when UTL_HTTP.SET_PERSISTENT_CONN_SUPPORT is set to FALSE.
| Output on 19C with UTL_HTTP.SET_PERSISTENT_CONN_SUPPORT set to TRUE : |
|---|
SQL> /
=============================================
32767 (part 1)
=============================================
detailed_sqlcode [-29263]
detailed_sqlerrm [ORA-29263: HTTP protocol error] PL/SQL procedure successfully completed. SQL> |
| Output on 19C with UTL_HTTP.SET_PERSISTENT_CONN_SUPPORT set to FALSE (same output when testing on 11G and 12C): |
|---|
SQL> /
=============================================
32767 (part 1)
=============================================
32767 (part 2)
=============================================
25154 (part 3)
=============================================
detailed_sqlcode [-29266]
detailed_sqlerrm [ORA-29266: end-of-body reached] PL/SQL procedure successfully completed. SQL> |
For testing we used :
- Oracle Database 19c Enterprise Edition Release 19.0.0.0.0
- Oracle Database 12c Enterprise Edition Release 12.2.0.1.0
- Oracle Database 11g Enterprise Edition Release 11.2.0.2.0
Does anyone have a clue why this happens on 19C and not on 12C/11G?
Regards,
Nico