Buenas días, alguien me puede ayudar, tengo un procedimiento en el cual consumo un WS HTTP en un oracle 11g, lo que me esta pasando es que después de 4 consultas me devuelve el error de muchas conexiones abiertas. (ORA-29270: too many open HTTP requests)
este es mi codigo
CREATE OR REPLACE PROCEDURE MI_PROCEDIMIENTO(
--paramtros de entrada
Pv_Uno IN VARCHAR2,
Pv_Dos IN VARCHAR2,
--paramtros de salida
Pn_ResultEncrypted OUT VARCHAR2,
Pv_Codigo_Error OUT VARCHAR2,
Pv_mensaje_error OUT VARCHAR2
) AS
/\* Definición de variables para la creacion del Request y Response de los servicios de Hondutel\*/
Lu\_Request UTL\_HTTP.REQ := NULL; -- Solicitud.
Lu\_Response UTL\_HTTP.RESP := NULL; -- Respuesta.
Lc\_Request\_Data CLOB;
Lc\_Response\_Data CLOB := NULL;
L\_Clob CLOB := NULL;
Lx\_XML XMLTYPE := NULL;
LX\_XML\_HEADER XMLTYPE;
LX\_XML\_BODY XMLTYPE;
Lv\_MensajeError VARCHAR2(2000) := '';
Lv\_Req\_Body VARCHAR2(32000) := '';
Lv\_Codigo\_Error VARCHAR2(10);
Lv\_success\_indicator VARCHAR2(100);
Lv\_Url\_Consulta VARCHAR2(500);
Lv\_resultEncrypted VARCHAR2(700);
BEGIN
Lv\_Url\_Consulta := 'http:miurl?wsdl';
BEGIN
Lu\_Request := UTL\_HTTP.BEGIN\_REQUEST(Lv\_Url\_Consulta, 'POST','HTTP/1.1');
EXCEPTION
WHEN UTL\_HTTP.REQUEST\_FAILED THEN
Pv\_Codigo\_Error :='-1';
Pv\_Mensaje\_Error := 'ERROR AL CREAR CONEXION ' || SUBSTR(SQLERRM,1,100);
RETURN;
WHEN OTHERS THEN
Pv\_Codigo\_Error :='-1';
Pv\_Mensaje\_Error := 'ERROR DESCONOCIDO GENERANDO CONSULTA '|| SUBSTR(SQLERRM,1,100);
RETURN;
END;
Lc\_Request\_Data :='\<soap:Envelope xmlns:soap="[http://www.w3.org/2003/05/soap-envelope">](http://www.w3.org/2003/05/soap-envelope">)
\<soap:Header/>
\<soap:Body>
\<fic:EncryptorValue>
\<fic:RequestEncryptor>
\<fic1:keyEncryptor>'||Pv\_Uno||'\</fic1:keyEncryptor>
\<fic1:value>'||Pv\_Dos||'\</fic1:value>
\</fic:RequestEncryptor>
\</fic:EncryptorValue>
\</soap:Body>
\</soap:Envelope>';
BEGIN
UTL\_HTTP.SET\_RESPONSE\_ERROR\_CHECK(ENABLE => FALSE);
UTL\_HTTP.SET\_DETAILED\_EXCP\_SUPPORT(ENABLE => TRUE);
Lu\_Request := UTL\_HTTP.BEGIN\_REQUEST(Lv\_Url\_Consulta, 'POST');
EXCEPTION
WHEN UTL\_HTTP.BAD\_URL THEN
Pv\_Codigo\_Error :='-1';
Pv\_mensaje\_error := 'ERROR Msg: ' || (UTL\_HTTP.GET\_DETAILED\_SQLCODE) || '\[' ||SUBSTR(UTL\_HTTP.GET\_DETAILED\_SQLERRM, 1, 100) || '\]';
WHEN UTL\_HTTP.BAD\_ARGUMENT THEN
Pv\_Codigo\_Error :='-1';
Pv\_mensaje\_error := 'ERROR Msg: ' || UTL\_HTTP.GET\_DETAILED\_SQLCODE || '\[' ||SUBSTR(UTL\_HTTP.GET\_DETAILED\_SQLERRM, 1, 100) || '\]';
WHEN UTL\_HTTP.HTTP\_CLIENT\_ERROR THEN
Pv\_Codigo\_Error :='-1';
Pv\_mensaje\_error := 'ERROR Msg: ' || UTL\_HTTP.GET\_DETAILED\_SQLCODE || '\[' ||SUBSTR(UTL\_HTTP.GET\_DETAILED\_SQLERRM, 1, 100) || '\]';
WHEN UTL\_HTTP.HTTP\_SERVER\_ERROR THEN
Pv\_Codigo\_Error :='-1';
Pv\_mensaje\_error := 'ERROR Msg: ' || UTL\_HTTP.GET\_DETAILED\_SQLCODE || '\[' ||SUBSTR(UTL\_HTTP.GET\_DETAILED\_SQLERRM, 1, 30) || '\]';
WHEN UTL\_HTTP.ILLEGAL\_CALL THEN
Pv\_Codigo\_Error :='-1';
Pv\_mensaje\_error := 'ERROR Msg: ' || UTL\_HTTP.GET\_DETAILED\_SQLCODE || '\[' ||SUBSTR(UTL\_HTTP.GET\_DETAILED\_SQLERRM, 1, 100) || '\]';
WHEN UTL\_HTTP.INIT\_FAILED THEN
Pv\_Codigo\_Error :='-1';
Pv\_mensaje\_error:= 'ERROR Msg: ' || UTL\_HTTP.GET\_DETAILED\_SQLCODE || '\[' ||SUBSTR(UTL\_HTTP.GET\_DETAILED\_SQLERRM, 1, 100) || '\]';
WHEN UTL\_HTTP.PROTOCOL\_ERROR THEN
Pv\_Codigo\_Error :='-1';
Pv\_mensaje\_error:= 'ERROR Msg: ' || UTL\_HTTP.GET\_DETAILED\_SQLCODE || '\[' ||SUBSTR(UTL\_HTTP.GET\_DETAILED\_SQLERRM, 1, 100) || '\]';
WHEN UTL\_HTTP.REQUEST\_FAILED THEN
Pv\_Codigo\_Error :='-1';
Pv\_mensaje\_error:= 'ERROR Msg: ' || UTL\_HTTP.GET\_DETAILED\_SQLCODE || '\[' ||SUBSTR(UTL\_HTTP.GET\_DETAILED\_SQLERRM, 1, 100) || '\]';
WHEN UTL\_HTTP.TOO\_MANY\_REQUESTS THEN
Pv\_Codigo\_Error :='-1';
Pv\_mensaje\_error:= 'ERROR Msg: ' || UTL\_HTTP.GET\_DETAILED\_SQLCODE || '\[' ||SUBSTR(UTL\_HTTP.GET\_DETAILED\_SQLERRM, 1, 100) || '\]';
--UTL\_HTTP.END\_RESPONSE(Lu\_Response);
WHEN UTL\_HTTP.TRANSFER\_TIMEOUT THEN
Pv\_Codigo\_Error :='-1';
Pv\_mensaje\_error := 'HTTP\_REQUEST\_TIME\_OUT: \[' || SUBSTR(SQLERRM,1,1500) ||'\]';
WHEN OTHERS THEN
Pv\_Codigo\_Error :='-1';
Pv\_mensaje\_error:= 'ERROR Msg: ' || UTL\_HTTP.GET\_DETAILED\_SQLCODE || '\[' ||SUBSTR(UTL\_HTTP.GET\_DETAILED\_SQLERRM, 1, 100) || '\]';
END;
IF Pv\_Codigo\_Error = '-1' THEN
RETURN;
END IF;
BEGIN
DBMS\_LOB.CREATETEMPORARY(L\_Clob, FALSE);
UTL\_HTTP.SET\_HEADER(Lu\_Request, 'Content-Type', 'text/xml;charset=UTF-8');
UTL\_HTTP.SET\_HEADER(Lu\_Request, 'Content-Length', LENGTH(Lc\_Request\_Data));
UTL\_HTTP.SET\_HEADER(Lu\_Request,'SOAPAction','[http://miurl');](http://miurl');)
UTL\_HTTP.SET\_TRANSFER\_TIMEOUT(3000);
UTL\_HTTP.WRITE\_TEXT(Lu\_Request, Lc\_Request\_Data);
--Consumir el Servicio
BEGIN
Lu\_Response := UTL\_HTTP.GET\_RESPONSE(Lu\_Request);
EXCEPTION
WHEN UTL\_HTTP.REQUEST\_FAILED THEN
Pv\_Codigo\_Error := '-1';
Pv\_mensaje\_error := 'Error realizando consumo: \[' || SUBSTR(SQLERRM||DBMS\_UTILITY.FORMAT\_ERROR\_BACKTRACE,1,1500) ||'\]';
WHEN UTL\_HTTP.TRANSFER\_TIMEOUT THEN
Pv\_Codigo\_Error := '-1';
Pv\_mensaje\_error := 'HTTP\_REQUEST\_TIME\_OUT: \[' || SUBSTR(SQLERRM||DBMS\_UTILITY.FORMAT\_ERROR\_BACKTRACE,1,1500) ||'\]';
WHEN OTHERS THEN
Pv\_Codigo\_Error := '-1';
Pv\_mensaje\_error := 'Error realizando consumo : \[' || SUBSTR(SQLERRM||DBMS\_UTILITY.FORMAT\_ERROR\_BACKTRACE,1,1500) ||'\]';
UTL\_HTTP.END\_REQUEST (Lu\_Request);
END;
IF Pv\_Codigo\_Error = '-1' THEN
RETURN;
END IF;
--Leer Respuesta
BEGIN
LOOP
UTL\_HTTP.READ\_TEXT(Lu\_Response, Lc\_Response\_Data, 32000);
DBMS\_LOB.WRITEAPPEND(L\_Clob,LENGTH(Lc\_Response\_Data),Lc\_Response\_Data);
END LOOP;
UTL\_HTTP.END\_RESPONSE(Lu\_Response);
UTL\_HTTP.DESTROY\_REQUEST\_CONTEXT(Lu\_Response);
EXCEPTION
WHEN UTL\_HTTP.END\_OF\_BODY THEN
UTL\_HTTP.END\_RESPONSE(Lu\_Response);
WHEN OTHERS THEN
Pv\_Codigo\_Error := '-1';
Pv\_mensaje\_error := 'Error realizando consumo: \[' || SUBSTR(SQLERRM||DBMS\_UTILITY.FORMAT\_ERROR\_BACKTRACE,1,1500) ||'\]';
UTL\_HTTP.END\_REQUEST (Lu\_Request);
END;
IF Pv\_Codigo\_Error = '-1' THEN
RETURN;
END IF;
--Crear XML de Response
BEGIN
Lx\_XML := SYS.XMLTYPE.CREATEXML(L\_Clob);
Lc\_Response\_Data := L\_Clob;
EXCEPTION
WHEN OTHERS THEN
Pv\_Codigo\_Error := '-1';
Pv\_mensaje\_error := 'Error XML Invalido en Consumo : \[' || SUBSTR(SQLERRM||DBMS\_UTILITY.FORMAT\_ERROR\_BACKTRACE,1,1500) ||'\]';
UTL\_HTTP.END\_REQUEST (Lu\_Request);
END;
IF Pv\_Codigo\_Error = '-1' THEN
RETURN;
END IF;
LX\_XML\_HEADER := XMLTYPE.createxml(Lc\_Response\_Data);
LX\_XML\_BODY := XMLTYPE.createxml(Lc\_Response\_Data);
LX\_XML\_BODY := LX\_XML\_BODY.EXTRACT ('/soap:Envelope/soap:Body/child::node()', 'xmlns:soap="[http://www.w3.org/2003/05/soap-envelope"');](http://www.w3.org/2003/05/soap-envelope"');)
Lv\_Codigo\_Error := LX\_XML\_BODY.EXTRACT('//code/text()','xmlns="[http://miurl"').GETSTRINGVAL();](http://miurl"').GETSTRINGVAL();)
Lv\_success\_indicator := LX\_XML\_BODY.EXTRACT('//message/text()','xmlns="http:miurl.org/"').GETSTRINGVAL();
dbms\_output.put\_line('Lv\_Codigo\_Error: '||Lv\_Codigo\_Error);
dbms\_output.put\_line('Lv\_success\_indicator: '||Lv\_success\_indicator);
IF Lv\_Codigo\_Error = '0' THEN
BEGIN
Lv\_resultEncrypted := SUBSTR(LX\_XML\_BODY.EXTRACT('//resultEncrypted/text()','xmlns="[http://miurl.org/"').GETSTRINGVAL(),1,700);](http://miurl.org/"').GETSTRINGVAL(),1,700);)
EXCEPTION
WHEN OTHERS THEN
Lv\_resultEncrypted :='';
END;
Pn\_ResultEncrypted :=Lv\_resultEncrypted;
Pv\_Codigo\_Error :=Lv\_Codigo\_Error;
Pv\_mensaje\_error :=Lv\_success\_indicator;
dbms\_output.put\_line('Lv\_resultEncrypted: '||Lv\_resultEncrypted);
END IF;
IF Lv\_Codigo\_Error != '0' THEN
Pv\_Codigo\_Error :=Lv\_Codigo\_Error;
Pv\_mensaje\_error :='Respuesta WS '||Lv\_success\_indicator;
RETURN;
END IF;
EXCEPTION
WHEN UTL\_HTTP.REQUEST\_FAILED THEN
Pv\_Codigo\_Error := '-1';
Pv\_mensaje\_error := 'Error realizando consumo1: \[' || SUBSTR(SQLERRM||DBMS\_UTILITY.FORMAT\_ERROR\_BACKTRACE,1,1500) ||'\]';
UTL\_HTTP.END\_REQUEST (Lu\_Request);
UTL\_HTTP.END\_RESPONSE(Lu\_Response);
WHEN OTHERS THEN
Pv\_Codigo\_Error := '-1';
Pv\_mensaje\_error := 'Error realizando consumo2 : \[' || SUBSTR(SQLERRM||DBMS\_UTILITY.FORMAT\_ERROR\_BACKTRACE,1,1500) ||'\]';
END;
EXCEPTION
WHEN OTHERS THEN
Pv\_Codigo\_Error := '-1';
Pv\_mensaje\_error := 'Error general: ' || sqlcode || ', ' || sqlerrm || DBMS\_UTILITY.FORMAT\_ERROR\_BACKTRACE;
END;