SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ORA-29270: Too many open HTTP requests error in Procedure even after using UTL_HTTP.END_RESPONSE(Res

PeterB123Nov 6 2014 — edited Nov 7 2014

Hi, I am trying to make API calls to 3rd party system to get appropriate information thru several calls and in the last transaction i will post it to 3rd party system to assign contracts to employee. In total i am calling 6 API's in this stored procedure and i am calling this stored procedure from trigger which will pass appropriate input values to this procedure and this procedure is intent to run as a job. There are few other packages which will eventually run after this program. If there are no exceptions this program is handling 600-700 users without any errors. When ever the program encounters any exceptions it is erroring out with the errors ORA-29273: HTTP request failed and ORA-29270: too many open HTTP requests at 5th transaction.

I researched about these exceptions and added UTL_HTTP.END_RESPONSE(Resp) for all 6 api calls after getting response from the 3rd party system and also added the same in Exceptions after seeing few posts in this community. Also tried using UTL_TCP.close_all_connections; in the begin of the program to close active connections from the previous iteration, still no luck.

Please find the code and Error log below.

we are on Oracle Database version 11.2.0.4.0 and using Oracle Gateway to interact with Sql Server database on 3rd party side. The URL's that i am trying to GET and POST are http (not https)

Can anyone please help me to figure out why i am getting these errors even af@@ter ending responses for each API call?

Is there a way to check in the Database how many active http open requests are active ?

Any help would be greatly appreciated. Thanks in Advance.

Peter

[code]

CREATE OR REPLACE PROCEDURE SYSADM.CREATE_CON(Contract_Name IN CHAR,EmplId IN CHAR,Effdt IN DATE) IS

cCOUNT Number(2);

Auth_Req UTL_HTTP.REQ;

Auth_Resp UTL_HTTP.RESP;

name VARCHAR2(256);

value VARCHAR2(1024);

CookieId VARCHAR2(256);

ControlID VARCHAR2(256);

ConID_Req UTL_HTTP.REQ;

ConID_Resp UTL_HTTP.RESP;

ConName_Req UTL_HTTP.REQ;

ConName_Resp UTL_HTTP.RESP;

DConName_Req UTL_HTTP.REQ;

DConName_Resp UTL_HTTP.RESP;

Resp_Name VARCHAR2(100);

Resp_Value VARCHAR2(100);

ConAssign_Req UTL_HTTP.REQ;

ConAssign_Resp UTL_HTTP.RESP;

ContId_Req UTL_HTTP.REQ;

ContId_Resp UTL_HTTP.RESP;

WW_JSON_REQ VARCHAR2(500);

Contract_Id VARCHAR2(200);

Contract_Name_Dflt VARCHAR2(100);

resp_string VARCHAR2(32767);

Auth_Url varchar2(400):='URL1';

ConID_Url varchar2(400):='URL2;

ConName_Url varchar2(400):='URL3';

InternalID_Url varchar2(400):='URL4';

ConAssign_Url varchar2(400):='URL5';

p_msg varchar(500);

BEGIN

UTL_TCP.close_all_connections;

Auth_Req := UTL_HTTP.BEGIN_REQUEST(Auth_Url,'POST');

UTL_HTTP.SET_HEADER(Auth_Req,'Transfer-Encoding','chunked');

Auth_Resp := UTL_HTTP.GET_RESPONSE(Auth_Req);

FOR i IN 1..UTL_HTTP.GET_HEADER_COUNT(Auth_Resp) LOOP

UTL_HTTP.GET_HEADER(Auth_Resp, i, name, value);

IF INSTR(value,'CookieId') <> 0 then

CookieId := Substr(value,INSTR(value,'=')+1,(INSTR(value,';')-INSTR(value,'='))-1);

end if;

END LOOP;

UTL_HTTP.END_RESPONSE(Auth_Resp);

EXCEPTION

WHEN UTL_HTTP.END_OF_BODY THEN

UTL_HTTP.END_RESPONSE(Auth_Resp);

-- UTL_HTTP.END_REQUEST (Auth_Req);

DBMS_OUTPUT.PUT_LINE('Exception section-end response');

WHEN UTL_HTTP.REQUEST_FAILED THEN

UTL_HTTP.END_RESPONSE(Auth_Resp);

WHEN OTHERS THEN

p_msg := Utl_Http.Get_Detailed_Sqlerrm ;

DBMS_OUTPUT.PUT_LINE(p_msg);

DBMS_OUTPUT.PUT_LINE('Exception section Auth in OTHERS');

UTL_HTTP.END_RESPONSE(Auth_Resp);

-- UTL_HTTP.END_REQUEST (Auth_Req);

END;

DBMS_OUTPUT.put_line('API Call-2');

BEGIN

ConName_Req := UTL_HTTP.BEGIN_REQUEST(ConName_Url||Contract_Name,'GET');

--DBMS_OUTPUT.put_line('URL: '||ConName_Url||Contract_Name);

UTL_HTTP.SET_HEADER(ConName_Req,'Cookie','CookieId: '||CookieId);

--UTL_HTTP.SET_HEADER(req2,'CookieId',CookieId);

UTL_HTTP.SET_HEADER ( ConName_Req,'Content-Type','application/json');

UTL_HTTP.SET_HEADER(ConName_Req,'User-Agent','Mozilla/5.0');

ConName_Resp := UTL_HTTP.GET_RESPONSE(ConName_Req);

UTL_HTTP.READ_TEXT(ConName_Resp,resp_string);

DBMS_OUTPUT.put_line(resp_string);

IF (ConName_Resp.status_code = UTL_HTTP.http_ok) THEN

DBMS_OUTPUT.put_line('HTTP response status code: ' || ConName_Resp.status_code);

Resp_Name:=SUBSTR(SUBSTR(resp_string,INSTR(resp_string,',"',1, 1)+1,INSTR(resp_string,',',1,2)-INSTR(resp_string,'"', 1,5)),INSTR(SUBSTR(resp_string,INSTR(resp_string,',"',1, 1)+1,INSTR(resp_string,',',1,2)-INSTR(resp_string,'"', 1,5)),'"',1)+1,INSTR(SUBSTR(resp_string,INSTR(resp_string,',"',1, 1)+1,INSTR(resp_string,',',1,2)-INSTR(resp_string,'"', 1,5)),'":',1)-2);

Contract_Id:=SUBSTR(SUBSTR(resp_string,INSTR(resp_string,',"',1, 1)+1,INSTR(resp_string,',',1,2)-INSTR(resp_string,'"', 1,5)),INSTR(SUBSTR(resp_string,INSTR(resp_string,',"',1, 1)+1,INSTR(resp_string,',',1,2)-INSTR(resp_string,'"', 1,5)),':',1)+1,LENGTH(SUBSTR(resp_string,INSTR(resp_string,',"',1, 1)+1,INSTR(resp_string,',',1,2)-INSTR(resp_string,'"', 1,5))));

DBMS_OUTPUT.put_line('Contract '||Resp_Name||':'||Contract_Id);

/**else****/

Else

If (ConName_Resp.status_code = UTL_HTTP.HTTP_NOT_FOUND) then

DBMS_OUTPUT.put_line('HTTP response status code: ' || ConName_Resp.status_code||'. Assigning Default Contract);

UTL_HTTP.END_RESPONSE(ConName_Resp);

BEGIN

Contract_Name_Dflt:='DEFAULT';

DConName_Req := UTL_HTTP.BEGIN_REQUEST(ConName_Url||Contract_Name_Dflt,'GET');

UTL_HTTP.SET_HEADER(DConName_Req,'Cookie','CookieId: '||CookieId);

UTL_HTTP.SET_HEADER ( DConName_Req,'Content-Type','application/json');

UTL_HTTP.SET_HEADER(DConName_Req,'User-Agent','Mozilla/5.0');

DConName_Resp := UTL_HTTP.GET_RESPONSE(DConName_Req);

UTL_HTTP.READ_TEXT(DConName_Resp,resp_string);

DBMS_OUTPUT.put_line(resp_string);

IF (DConName_Resp.status_code = UTL_HTTP.http_ok) THEN

DBMS_OUTPUT.put_line('HTTP response status code: ' || DConName_Resp.status_code);

DBMS_OUTPUT.put_line('Contract '||Resp_Name||':'||Contract_Id);

ELSE

DBMS_OUTPUT.put_line(Resp_Name||':'||Contract_Id);

UTL_HTTP.END_RESPONSE(DConName_Resp);

END IF;

EXCEPTION

WHEN UTL_HTTP.END_OF_BODY THEN

UTL_HTTP.END_RESPONSE(DConName_Resp);

WHEN UTL_HTTP.REQUEST_FAILED THEN

UTL_HTTP.END_RESPONSE(DConName_Resp);

WHEN OTHERS THEN

p_msg := Utl_Http.Get_Detailed_Sqlerrm ;

DBMS_OUTPUT.PUT_LINE(p_msg);

DBMS_OUTPUT.PUT_LINE('Exception section Get Default Contract Id-OTHERS2');

UTL_HTTP.END_RESPONSE(DConName_Resp);

END;

END IF;

EXCEPTION

WHEN UTL_HTTP.END_OF_BODY THEN

UTL_HTTP.END_RESPONSE(ConName_Resp);

WHEN UTL_HTTP.REQUEST_FAILED THEN

UTL_HTTP.END_RESPONSE(ConName_Resp);

WHEN OTHERS THEN

p_msg := Utl_Http.Get_Detailed_Sqlerrm ;

DBMS_OUTPUT.PUT_LINE(p_msg);

DBMS_OUTPUT.PUT_LINE('Exception section Get Contract Id-OTHERS2');

UTL_HTTP.END_RESPONSE(ConName_Resp);

END;

/************************Begining of API call to get Contract Name from 3rd party************************************************/

BEGIN

ConID_Req := UTL_HTTP.BEGIN_REQUEST(ConID_Url||Contract_Id,'GET');

UTL_HTTP.SET_HEADER(ConID_Req,'Cookie','CookieId: '||CookieId);

UTL_HTTP.SET_HEADER ( ConID_Req,'Content-Type','application/json');

UTL_HTTP.SET_HEADER(ConID_Req,'User-Agent','Mozilla/5.0');

ConID_Resp := UTL_HTTP.GET_RESPONSE(ConID_Req);

UTL_HTTP.READ_TEXT(ConID_Resp,resp_string);

DBMS_OUTPUT.put_line(resp_string);

IF (ConID_Resp.status_code = UTL_HTTP.http_ok) THEN

DBMS_OUTPUT.put_line('HTTP response status code: ' || ConID_Resp.status_code);

Resp_Name:=SUBSTR(SUBSTR(resp_string,INSTR(resp_string,'"', 1, 1),INSTR(resp_string,',',3, 1)-2),2,INSTR(SUBSTR(resp_string,INSTR(resp_string,'"', 1, 1),INSTR(resp_string,',',3, 1)-2),':',1)-3);

Resp_value:=SUBSTR(SUBSTR(resp_string,INSTR(resp_string,'"', 1, 1),INSTR(resp_string,',',3, 1)-3),INSTR(SUBSTR(resp_string,INSTR(resp_string,'"', 1, 1),INSTR(resp_string,',',3, 1)-2),'"',1,3)+1,INSTR(SUBSTR(resp_string,INSTR(resp_string,'"', 1, 1),INSTR(resp_string,',',3, 1)-2),'"',1,4));

DBMS_OUTPUT.put_line('Contract '||Resp_Name||':'||Resp_value);

UTL_HTTP.END_RESPONSE(ConID_Resp);

ELSE

DBMS_OUTPUT.put_line('Contract Name Not Found '||Contract_id);

UTL_HTTP.END_RESPONSE(ConID_Resp);

END IF;

EXCEPTION

WHEN UTL_HTTP.END_OF_BODY THEN

UTL_HTTP.END_RESPONSE(ConID_Resp);

WHEN UTL_HTTP.REQUEST_FAILED THEN

UTL_HTTP.END_RESPONSE(ConID_Resp);

WHEN OTHERS THEN

p_msg := Utl_Http.Get_Detailed_Sqlerrm ;

DBMS_OUTPUT.PUT_LINE(p_msg);

DBMS_OUTPUT.PUT_LINE('Exception section Get Contract Name-OTHERS3');

UTL_HTTP.END_RESPONSE(ConID_Resp);

UTL_TCP.close_all_connections;

END;

/************************Begining of API call to post to 3rd party************************************************/

BEGIN

ContID_Req := UTL_HTTP.BEGIN_REQUEST(InternalID_Url||EmplId,'GET');

UTL_HTTP.SET_HEADER(ContID_Req,'Cookie','CookieId: '||CookieId);

UTL_HTTP.SET_HEADER ( ContID_Req,'Content-Type','application/json');

UTL_HTTP.SET_HEADER(ContID_Req,'User-Agent','Mozilla/5.0');

ContID_Resp := UTL_HTTP.GET_RESPONSE(ContID_Req);

UTL_HTTP.READ_TEXT(ContID_Resp,resp_string);

DBMS_OUTPUT.put_line(resp_string);

IF (ContID_Resp.status_code = UTL_HTTP.http_ok) THEN

DBMS_OUTPUT.put_line('HTTP response status code: ' || ContID_Resp.status_code);

ControlID:=SUBSTR(SUBSTR(resp_string,1,INSTR(resp_string,'","Rel":"user"}',1,1)-1),INSTR(SUBSTR(resp_string,1,INSTR(resp_string,'","Rel":"user"}',1,1)-1),'/',1,1)+1,INSTR(resp_string,'","Rel":"user"}',1,1)-1);

DBMS_OUTPUT.put_line('Control ID: '||ControlID);

UTL_HTTP.END_RESPONSE(ContID_Resp);

END IF;

EXCEPTION

WHEN UTL_HTTP.END_OF_BODY THEN

UTL_HTTP.END_RESPONSE(ContID_Resp);

WHEN UTL_HTTP.REQUEST_FAILED THEN

UTL_HTTP.END_RESPONSE(ContID_Resp);

WHEN OTHERS THEN

p_msg := Utl_Http.Get_Detailed_Sqlerrm ;

DBMS_OUTPUT.PUT_LINE(p_msg);

DBMS_OUTPUT.PUT_LINE('Exception section Get Control Id-OTHERS3');

UTL_HTTP.END_RESPONSE(ContID_Resp);

END;

/************************Begin of API call to post contract Id to rd party************************************************/

DBMS_OUTPUT.put_line('Employee Contract Assignment POST API Call:');

BEGIN

ConAssign_Req := UTL_HTTP.BEGIN_REQUEST(ConAssign_Url,'POST');

WW_JSON_REQ:=WW_BUILD_JSON_REQ(ControlID,Contract_Id,Effdt);

DBMS_OUTPUT.put_line('Request:'||WW_JSON_REQ);

UTL_HTTP.SET_HEADER(ConAssign_Req,'Cookie','CookieId: '||CookieId);

UTL_HTTP.SET_HEADER(ConAssign_Req, 'Content-Length', LENGTH(WW_JSON_REQ));

UTL_HTTP.SET_HEADER(ConAssign_Req,'Content-Type','application/json');

UTL_HTTP.SET_HEADER(ConAssign_Req,'User-Agent','Mozilla/5.0');

UTL_HTTP.WRITE_TEXT(ConAssign_Req, WW_JSON_REQ);

ConAssign_Resp := UTL_HTTP.GET_RESPONSE(ConAssign_Req);

UTL_HTTP.READ_TEXT(ConAssign_Resp,resp_string);

DBMS_OUTPUT.put_line('Response: '||resp_string);

IF (ConAssign_Resp.status_code = UTL_HTTP.HTTP_CREATED) THEN

DBMS_OUTPUT.put_line('HTTP response status code: ' || ConAssign_Resp.status_code ||'. Contract Assigned Successfully for EMPLID: '||EmplId);

Resp_value:=SUBSTR(SUBSTR(resp_string,INSTR(resp_string,',"',1, 1)+1,INSTR(resp_string,',',1,2)-INSTR(resp_string,'"', 1,5)),INSTR(SUBSTR(resp_string,INSTR(resp_string,',"',1, 1)+1,INSTR(resp_string,',',1,2)-INSTR(resp_string,'"', 1,5)),':',1)+1,LENGTH(SUBSTR(resp_string,INSTR(resp_string,',"',1, 1)+1,INSTR(resp_string,',',1,2)-INSTR(resp_string,'"', 1,5))));

UTL_HTTP.END_RESPONSE(ConAssign_Resp);

ELSE

DBMS_OUTPUT.put_line('Contract NOT ASSIGNED '||Resp_Name||':'||Resp_value);

UTL_HTTP.END_RESPONSE(ConAssign_Resp);

END IF;

EXCEPTION

WHEN UTL_HTTP.END_OF_BODY THEN

UTL_HTTP.END_RESPONSE(ConAssign_Resp);

WHEN UTL_HTTP.REQUEST_FAILED THEN

UTL_HTTP.END_RESPONSE(ConAssign_Resp);

WHEN OTHERS THEN

p_msg := Utl_Http.Get_Detailed_Sqlerrm ;

DBMS_OUTPUT.PUT_LINE(p_msg);

DBMS_OUTPUT.PUT_LINE('Exception section -OTHERS3');

UTL_HTTP.END_RESPONSE(ConAssign_Resp);

END;

/************************End of API call to post contract Id to an EE in 3rd party************************************************/

END CREATE_CON;

[/code]

Error Log:

Exception section Get Contract Name-OTHERS3

BEGIN sysadm.emp_trig.empl_trigg; END;

ERROR at line 1:

ORA-02290: check constraint (SYSADM.SYS_C00168801) violated

ORA-06512: at "SYSADM.PGM_ERR_LOG", line 19

ORA-06512: at "SYSADM.EMP_TRIGGERS", line 175

ORA-29273: HTTP request failed

ORA-06512: at "SYS.UTL_HTTP", line 1527

ORA-29261: bad argument

ORA-06512: at "SYSADM.CREATE_CON;", line 201

ORA-29273: HTTP request failed

ORA-06512: at "SYS.UTL_HTTP", line 1369

ORA-29270: too many open HTTP requests

ORA-06512: at line 1

Locked Post

New comments cannot be posted to this locked post.

Locked on Dec 5 2014

Added on Nov 6 2014

3 comments

5,176 views