I am facing an issue with a PL/SQL function that makes HTTP(S) requests using UTL_HTTP. The function is supposed to send a POST request to a specific URL. It's working almost as expected, but with a peculiar issue.
Here is the function I'm using:
create or replace function send_post_request (p_url in varchar2,
p_content in clob default null)
return clob
is
v_url varchar2 (255);
req utl_http.req;
resp utl_http.resp;
buffer varchar2 (32767);
response clob;
begin
v_url := p_url;
utl_http.set_wallet (
'file:/wallet_path',
'password');
utl_http.set_transfer_timeout (300);
req :=
utl_http.begin_request (v_url, 'post', utl_http.http_version_1_1);
utl_http.set_authentication (req, '<hidden>', '<hidden>');
utl_http.set_header (req, 'user-agent', 'mozilla/4.0');
utl_http.set_header (req, 'content-type', 'application/json');
if p_content is not null
then
utl_http.set_body_charset ('utf-8');
utl_http.set_header (req,
'content-length',
lengthb (to_char (p_content)));
utl_http.write_text (req, p_content);
end if;
resp := utl_http.get_response (req);
utl_http.set_body_charset (r => resp, charset => 'utf-8');
if resp.status_code != utl_http.http_ok
then
raise_application_error (
-20001,
'response status code: ' || resp.status_code);
end if;
begin
loop
utl_http.read_text (resp, buffer);
response := response || buffer;
end loop;
utl_http.end_request (req);
utl_http.end_response (resp);
return response;
exception
when utl_http.end_of_body
then
utl_http.end_request (req);
utl_http.end_response (resp);
return response;
end;
exception
when others
then
utl_http.end_request (req);
utl_http.end_response (resp);
raise_application_error (
-20002,
sqlerrm || ' / ' || utl_http.get_detailed_sqlerrm);
end;
The problem I am encountering is that the first time I call this function, it throws an exception with the following error messages: ORA-29273: HTTP request failed / ORA-29259: end-of-input reached.
The strange part is that, when I call the function a second time immediately after encountering the error, it executes without any problem and gives the expected result.
I've already checked the Oracle Wallet setup, HTTPS endpoint, and the request payload, and they seem to be configured correctly, especially considering that the function works on the second and subsequent executions.
Has anyone else encountered this situation or have any ideas on why the first execution might fail while subsequent executions run without any issues? Any insights would be appreciated.
Note: set_authentication parameters hidden for security purpose
So far, I've tried the following steps to debug and resolve this issue:
- Checked the wallet file path and password provided to
UTL_HTTP.SET_WALLETto ensure they're correct. - Verified that the HTTP version specified in
UTL_HTTP.BEGIN_REQUESTis appropriate for the server I'm communicating with. - Investigated whether the issue could be related to a network delay or timing out. However, the problem seems to persist even when there's no apparent network issue.
- Explored whether the error could be due to an incorrectly calculated Content-Length header. However, the content length seems to be set correctly according to the byte length of the JSON payload.
- Checked if there are any issues with the HTTP headers being set, such as
'Content-Type'. They seem to be set correctly for my use case. Despite these attempts, the problem persists. The peculiar issue is that the error occurs only on the first execution of the function, but not on subsequent executions, which I'm struggling to explain.
I'm looking for insights into why this might be happening, and how I can ensure the function works correctly even on its first execution.
