Oracle Database Discussions

Hi

I am calling Microsoft 365 graph API using UTL_HTTP and sometimes randomly I am getting

ORA-29270: too many open HTTP requests
ORA-06512: at "SYS.UTL_HTTP", line 380
ORA-06512: at "SYS.UTL_HTTP", line 1148

We were getting this error in 18c, after that recently when we upgraded DB to 19c, still we are getting the same issue. Could anyone please help me? I have tried many different approaches but still facing this issue.

Here is my code

create or replace procedure sendMS365Email_UTLHTTP(P_CLIENT_ID IN NUMBER,
P_CONFIG_ID IN NUMBER,
P_MAIL_TO IN VARCHAR2,
P_CC IN VARCHAR2,
P_BCC IN VARCHAR2,
P_MAIL_SUBJECT IN VARCHAR2,
P_MAIL_BODY IN CLOB,
P_ATT_ARRAY IN ITV_MAIL_ATTACHMENT_TABLE default null,
P_RESULT OUT NUMBER)
as 
-- UTL_HTTP Variables
V_REQ UTL_HTTP.REQ;
V_RESP UTL_HTTP.RESP;
V_URL VARCHAR2(4000);

-- Config Variables
V_EMAIL ITV_CLIENT_EMAIL_CONFIG.EMAIL%TYPE;
V_TENANT_ID VARCHAR2(4000);
V_AUTH_CLIENT_ID VARCHAR2(4000);
V_AUTH_CLIENT_SECRET VARCHAR2(4000);
V_AUTH_TOKEN VARCHAR2(32000); 

-- Payload Variables
V_EMAIL_BODY CLOB;
G_WALLET_PATH VARCHAR2(255) := 'file:/home/oracle/wallet/mywalletFolder';
G_WALLET_PASSWORD VARCHAR2(50) := 'Qw2wb59]t2fC';

-- Response Processing
V_RESP_BUFFER VARCHAR2(32000);
V_RESPONSE CLOB;
V_STATUS_CODE NUMBER;
V_WS_ERROR_CHECK VARCHAR2(200);
V_ATT_CNT PLS_INTEGER := 0;

-- Chunking Variables
V_BUFFER VARCHAR2(32767);
V_AMOUNT PLS_INTEGER;
V_CHUNK_SIZE CONSTANT PLS_INTEGER := 8000;
V_OFFSET PLS_INTEGER := 1;
V_LENGTH NUMBER;

V_OPEN_CONN PLS_INTEGER;
begin 
-- 1. CLEANUP START: Safety Net
V_OPEN_CONN := UTL_HTTP.GET_PERSISTENT_CONN_COUNT;
UTL_HTTP.CLOSE_PERSISTENT_CONNS;
UTL_HTTP.SET_PERSISTENT_CONN_SUPPORT(FALSE, 0);

-- 2. Set Wallet (Required for HTTPS)
UTL_HTTP.SET_WALLET(G_WALLET_PATH, G_WALLET_PASSWORD);

-- 3. Get Configuration
select email, client_tenant_id, auth_client_id, auth_client_secret
into V_EMAIL, V_TENANT_ID, V_AUTH_CLIENT_ID, V_AUTH_CLIENT_SECRET
from itv_client_email_config
where config_id = P_CONFIG_ID
and client_id = P_CLIENT_ID;

IF V_AUTH_CLIENT_ID IS NOT NULL AND V_AUTH_CLIENT_SECRET IS NOT NULL AND V_TENANT_ID IS NOT NULL THEN

-- 4. Get Auth Token (Your existing procedure)
V_AUTH_TOKEN := ITV_MAIL_TO_SR_PKG.getMS365AuthToken(P_CLIENT_TENANT_ID => V_TENANT_ID,
P_AUTH_CLIENT_ID => V_AUTH_CLIENT_ID,
P_AUTH_CLIENT_SECRET => V_AUTH_CLIENT_SECRET);

IF V_AUTH_TOKEN IS NOT NULL THEN
V_URL := 'https://graph.microsoft.com/v1.0/users/'||V_EMAIL||'/sendMail';

-- 5. Prepare JSON Body (Your existing logic)
select count(1) into V_ATT_CNT from table (P_ATT_ARRAY);

select json_object(
'message' value json_object(
'subject' value P_MAIL_SUBJECT,
'body' value json_object('contentType' value 'HTML',
'content' value P_MAIL_BODY
returning clob),
'toRecipients' value (
select json_arrayagg(json_object(
'emailAddress' value json_object('address' value t.column_value returning clob)
returning clob)
returning clob)
from table(itv_util.str_to_tab(P_MAIL_TO, ',')) t
where P_MAIL_TO IS NOT NULL and t.column_value is not null
),
'ccRecipients' value (
select json_arrayagg(json_object(
'emailAddress' value json_object('address' value t.column_value returning clob)
returning clob)
returning clob)
from table(itv_util.str_to_tab(P_CC, ',')) t
where P_CC IS NOT NULL and t.column_value is not null
),
'bccRecipients' value (
select json_arrayagg(json_object(
'emailAddress' value json_object('address' value t.column_value returning clob)
returning clob)
returning clob)
from table(itv_util.str_to_tab(P_BCC, ',')) t
where P_BCC IS NOT NULL and t.column_value is not null
),
'attachments' value (
select json_arrayagg(json_object(
'@odata.type' value '#microsoft.graph.fileAttachment',
'name' value rf.file_name,
'contentType' value rf.file_mime_type,
'contentBytes' value itv_util.convertBLOBToBASE64(rf.BLOB_CONTENT)
returning clob)
returning clob)
from TABLE(P_ATT_ARRAY) rf
where P_ATT_ARRAY IS NOT NULL and rf.BLOB_CONTENT IS NOT NULL
)
absent on null returning clob),
'saveToSendItems' value 'true'
absent on null returning clob)
into V_EMAIL_BODY
from dual;

---------------------------------------------------------------
-- NATIVE HTTP REQUEST LOGIC START
---------------------------------------------------------------
BEGIN
-- A. Start Request
V_REQ := UTL_HTTP.BEGIN_REQUEST(V_URL, 'POST', 'HTTP/1.1');

-- B. Set Headers
UTL_HTTP.SET_HEADER(V_REQ, 'Content-Type', 'application/json');
UTL_HTTP.SET_HEADER(V_REQ, 'Authorization', 'Bearer ' || V_AUTH_TOKEN);

-- C. Handle Body Length & Chunking
V_LENGTH := DBMS_LOB.GETLENGTH(V_EMAIL_BODY);
UTL_HTTP.SET_HEADER(V_REQ, 'Content-Length', V_LENGTH);

IF V_LENGTH <= 32767 THEN
UTL_HTTP.WRITE_TEXT(V_REQ, V_EMAIL_BODY);
ELSE
UTL_HTTP.SET_HEADER(V_REQ, 'Transfer-Encoding', 'chunked');
WHILE(V_OFFSET <= V_LENGTH)
LOOP
V_AMOUNT := LEAST(V_CHUNK_SIZE, V_LENGTH - V_OFFSET + 1);
DBMS_LOB.READ(V_EMAIL_BODY, V_AMOUNT, V_OFFSET, V_BUFFER);
UTL_HTTP.WRITE_TEXT(V_REQ, V_BUFFER);
V_OFFSET := V_OFFSET + V_AMOUNT;
END LOOP;
END IF;

-- D. Get Response
V_RESP := UTL_HTTP.GET_RESPONSE(V_REQ);
V_STATUS_CODE := V_RESP.STATUS_CODE;

-- E. Read Response Body into CLOB
DBMS_LOB.CREATETEMPORARY(V_RESPONSE, TRUE);
BEGIN
LOOP
UTL_HTTP.READ_TEXT(V_RESP, V_RESP_BUFFER, 32000);
DBMS_LOB.WRITEAPPEND(V_RESPONSE, LENGTH(V_RESP_BUFFER), V_RESP_BUFFER);
END LOOP;
EXCEPTION 
WHEN UTL_HTTP.END_OF_BODY THEN NULL; 
END;

-- F. Close Response (CRITICAL STEP)
UTL_HTTP.END_RESPONSE(V_RESP);

/*EXCEPTION WHEN OTHERS THEN
-- If error happens during HTTP call, ensure we close response if open
-- We wrap this in its own block to safely ignore if V_RESP is not open
BEGIN 
UTL_HTTP.END_RESPONSE(V_RESP); 
EXCEPTION WHEN OTHERS THEN 
NULL; 
END;
RAISE; -- Re-raise to be caught by outer handler
*/
END;
---------------------------------------------------------------
-- NATIVE HTTP REQUEST LOGIC END
---------------------------------------------------------------

-- 6. Log Success/Failure (Your existing logic)
BEGIN
select json_value(V_RESPONSE, '$.error.code') INTO V_WS_ERROR_CHECK from dual;

IF V_WS_ERROR_CHECK IS NOT NULL THEN
insert into itv_mail365_send_error_logs(err_log_id, client_id, config_id, EMAIL_FROM, MAIL_SUBJECT, MAIL_BODY, MAIL_TO, MAIL_CC,
MAIL_BCC, EMAIL_BODY_JSON, WS_RESPONSE, CREATED_ON, STATUS_CODE, IS_PROCESSED)
values(itv_mail365_send_err_log_s.nextval, P_CLIENT_ID, P_CONFIG_ID, V_EMAIL, P_MAIL_SUBJECT, P_MAIL_BODY, P_MAIL_TO, P_CC,
P_BCC, V_EMAIL_BODY, V_RESPONSE, SYSTIMESTAMP, V_STATUS_CODE, 0);
ELSE
insert into itv_mail365_send_history(mail_hist_log_id, client_id, config_id, EMAIL_FROM, MAIL_SUBJECT, MAIL_BODY, MAIL_TO, MAIL_CC,
MAIL_BCC, WS_RESPONSE, CREATED_ON, CREATED_BY, STATUS_CODE)
values(itv_mail365_send_history_s.nextval, P_CLIENT_ID, P_CONFIG_ID, V_EMAIL, P_MAIL_SUBJECT, P_MAIL_BODY, P_MAIL_TO, P_CC,
P_BCC, V_RESPONSE, SYSTIMESTAMP, 'SYSTEM', V_STATUS_CODE);
END IF;
EXCEPTION WHEN OTHERS THEN
-- Log parsing errors
insert into itv_mail365_send_error_logs(err_log_id, client_id, config_id, EMAIL_FROM, MAIL_SUBJECT, MAIL_BODY, MAIL_TO, MAIL_CC,
MAIL_BCC, EMAIL_BODY_JSON, WS_RESPONSE, CREATED_ON, STATUS_CODE, IS_PROCESSED, ERROR_STACK, ERROR_BACKTRACE)
values(itv_mail365_send_err_log_s.nextval, P_CLIENT_ID, P_CONFIG_ID, V_EMAIL, P_MAIL_SUBJECT, P_MAIL_BODY, P_MAIL_TO, P_CC,
P_BCC, V_EMAIL_BODY, V_RESPONSE, SYSTIMESTAMP, V_STATUS_CODE, 0, 'FROM_STATUS_CHECK: ' || DBMS_UTILITY.FORMAT_ERROR_STACK, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
END;

COMMIT;

-- Final Cleanup
IF DBMS_LOB.ISOPEN(V_RESPONSE) = 1 THEN 
DBMS_LOB.FREETEMPORARY(V_RESPONSE); 
END IF;
UTL_HTTP.CLOSE_PERSISTENT_CONNS;
P_RESULT := 1;

ELSE

select count(1) into V_ATT_CNT from table (P_ATT_ARRAY);
-- itv_debug.log_me('MAIL365_MAIL_0_9_ATT_CNT: ' || V_ATT_CNT);
select json_object(
'message' value json_object(
'subject' value P_MAIL_SUBJECT,
'body' value json_object('contentType' value 'HTML',
'content' value P_MAIL_BODY
returning clob),
'toRecipients' value (
select json_arrayagg(json_object(
'emailAddress' value json_object('address' value t.column_value returning clob) 
returning clob)
returning clob)
from table(itv_util.str_to_tab(P_MAIL_TO, ',')) t
where P_MAIL_TO IS NOT NULL
and t.column_value is not null
),
'ccRecipients' value (
select json_arrayagg(json_object(
'emailAddress' value json_object('address' value t.column_value returning clob) 
returning clob)
returning clob)
from table(itv_util.str_to_tab(P_CC, ',')) t
where P_CC IS NOT NULL
and t.column_value is not null
),
'bccRecipients' value (
select json_arrayagg(json_object(
'emailAddress' value json_object('address' value t.column_value returning clob) 
returning clob)
returning clob)
from table(itv_util.str_to_tab(P_BCC, ',')) t
where P_BCC IS NOT NULL
and t.column_value is not null
),
'attachments' value (
select json_arrayagg(json_object(
'@odata.type' value '#microsoft.graph.fileAttachment',
'name' value rf.file_name,
'contentType' value rf.file_mime_type,
'contentBytes' value itv_util.convertBLOBToBASE64(rf.BLOB_CONTENT)
returning clob)
returning clob)
from TABLE(P_ATT_ARRAY) rf
where P_ATT_ARRAY IS NOT NULL
and rf.BLOB_CONTENT IS NOT NULL
)

absent on null returning clob),
'saveToSendItems' value 'true'
absent on null returning clob)
into V_EMAIL_BODY
from dual;

-- Auth Token Failed Logic
insert into itv_mail365_send_error_logs(err_log_id, client_id, config_id, EMAIL_FROM, MAIL_SUBJECT, MAIL_BODY, MAIL_TO, MAIL_CC,
MAIL_BCC, EMAIL_BODY_JSON, WS_RESPONSE, CREATED_ON, STATUS_CODE, IS_PROCESSED, ERROR_STACK, ERROR_BACKTRACE)
values(itv_mail365_send_err_log_s.nextval, P_CLIENT_ID, P_CONFIG_ID, V_EMAIL, P_MAIL_SUBJECT, P_MAIL_BODY, P_MAIL_TO, P_CC,
P_BCC, V_EMAIL_BODY, V_RESPONSE, SYSTIMESTAMP, V_STATUS_CODE, 0, 'FROM_AUTH_TOKEN_ELSE: ' || DBMS_UTILITY.FORMAT_ERROR_STACK, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
COMMIT;
UTL_HTTP.CLOSE_PERSISTENT_CONNS;
P_RESULT := 0;
END IF;

ELSE
UTL_HTTP.CLOSE_PERSISTENT_CONNS;
P_RESULT := 0;
END IF;
EXCEPTION WHEN OTHERS THEN
-- 1. Close Response (Safety check)
BEGIN
UTL_HTTP.END_RESPONSE(V_RESP);
EXCEPTION WHEN OTHERS THEN NULL;
END;

-- 2. Close Request (CRITICAL FIX FOR LEAKS)
BEGIN
UTL_HTTP.END_REQUEST(V_REQ);
EXCEPTION WHEN OTHERS THEN NULL;
END;

-- 3. Force Close Persistent Connections
BEGIN 
UTL_HTTP.CLOSE_PERSISTENT_CONNS;
EXCEPTION WHEN OTHERS THEN NULL;
END;

insert into itv_mail365_send_error_logs(err_log_id, client_id, config_id, EMAIL_FROM, MAIL_SUBJECT, MAIL_BODY, MAIL_TO, MAIL_CC,
MAIL_BCC, EMAIL_BODY_JSON, WS_RESPONSE, CREATED_ON, STATUS_CODE, IS_PROCESSED, ERROR_STACK, ERROR_BACKTRACE)
values(itv_mail365_send_err_log_s.nextval, P_CLIENT_ID, P_CONFIG_ID, V_EMAIL, P_MAIL_SUBJECT, P_MAIL_BODY, P_MAIL_TO, P_CC,
P_BCC, V_EMAIL_BODY, V_RESPONSE, SYSTIMESTAMP, V_STATUS_CODE, 0,
DBMS_UTILITY.FORMAT_ERROR_STACK, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE || 'Open conn: ' || V_OPEN_CONN);

-- ITV_APP_MAIL.sendErrorMail('Error in STANDALONE sendMS365Email_UTLHTTP', 'P_CLIENT_ID : '|| P_CLIENT_ID||
-- ', P_CONFIG_ID: ' || P_CONFIG_ID || ', P_MAIL_TO: ' || P_MAIL_TO || 'P_MAIL_SUBJECT: ' || P_MAIL_SUBJECT ||
-- ', P_MAIL_BODY_LENGTH : ' || DBMS_LOB.GETLENGTH(P_MAIL_BODY)
-- || '<br/>Error: ' || DBMS_UTILITY.FORMAT_ERROR_STACK || ' - '|| DBMS_UTILITY.FORMAT_ERROR_BACKTRACE

-- , DBMS_UTILITY.FORMAT_ERROR_STACK, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);

ITV_DEBUG.LOG_ERROR (P_PACKAGE_NAME => 'SENDMS365EMAIL_UTLHTTP'
, P_MODULE_NAME => 'SENDMS365EMAIL_UTLHTTP'
, P_IN_JSON => 'Error in STANDALONE sendMS365Email_UTLHTTP for P_CLIENT_ID : '|| P_CLIENT_ID|| ', P_CONFIG_ID: ' || P_CONFIG_ID || ', P_MAIL_TO: ' || P_MAIL_TO || 'P_MAIL_SUBJECT: ' || P_MAIL_SUBJECT ||', P_MAIL_BODY_LENGTH : ' || DBMS_LOB.GETLENGTH(P_MAIL_BODY)|| '<br/>Error: ' || DBMS_UTILITY.FORMAT_ERROR_STACK || ' - '|| DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
, P_SEND_EMAIL => 0);

P_RESULT := 0;

end sendMS365Email_UTLHTTP;

Could anyone verify my logic and help me to find the root cause of this too many requests issue?