Hi Gurus,
I am trying to call webservice from DB using UTL_HTTP, but I continue to get the following error. Please note I am calling only HTTP and HTTPS.
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1369
ORA-29259: end-of-input reached
ORA-06512: at "APL_GLOGEXTN.APL_INT_BEM", line 20
ORA-06512: at "APL_GLOGEXTN.APL_INT_BEM", line 50
ORA-06512: at line 1
29273. 00000 - "HTTP request failed"
*Cause: The UTL_HTTP package failed to execute the HTTP request.
*Action: Use get_detailed_sqlerrm to check the detailed error message.
Fix the error and retry the HTTP request.
Status code: 1
I am calling a webservice using the below code
exec utl_http.set_proxy ('172.10.0.00:1524');
SET SERVEROUTPUT ON
EXECU DTS_INT_BEM.CALL_BEM();
and the details of the PL/SQL package is as under.
create or replace
package body "DTS_INT_BEM" as
PROCEDURE INVOKE_BPEL_WS(
P_ENDPOINT_URL IN VARCHAR2,
P_SOAP_REQUEST IN VARCHAR2 ,
P_RETURN_STS OUT VARCHAR2 )
IS
REQUEST_ENVELOPE VARCHAR2(30000) := P_SOAP_REQUEST ;
RESPONSE_ENVELOPE VARCHAR2(30000);
HTTP_REQUEST UTL_HTTP.REQ;
HTTP_RESPONSE UTL_HTTP.RESP;
BEGIN
HTTP_REQUEST := UTL_HTTP.BEGIN_REQUEST( P_ENDPOINT_URL ,'POST', 'HTTP/1.1' );
UTL_HTTP.SET_HEADER( R => HTTP_REQUEST, name => 'Content-Type', value => 'text/xml;charset=utf-8' );
UTL_HTTP.SET_HEADER( r => http_request, name => 'Content-Length', value => LENGTH(REQUEST_ENVELOPE) );
UTL_HTTP.SET_HEADER( R => HTTP_REQUEST, name => 'SOAPAction', value => 'start' );
UTL_HTTP.WRITE_TEXT(R => HTTP_REQUEST, data => REQUEST_ENVELOPE);
dbms_output.put_line ('Status code: 1' );
HTTP_RESPONSE := UTL_HTTP.GET_RESPONSE(r => HTTP_REQUEST);
UTL_HTTP.END_RESPONSE(HTTP_RESPONSE);
P_RETURN_STS := HTTP_RESPONSE.reason_phrase ;
/*EXCEPTION
WHEN UTL_HTTP.END_OF_BODY THEN
UTL_HTTP.END_RESPONSE(HTTP_RESPONSE);
WHEN UTL_HTTP.REQUEST_FAILED THEN
P_RETURN_STS := 'Request Failed: ' || UTL_HTTP.GET_DETAILED_SQLERRM ;
WHEN UTL_HTTP.HTTP_SERVER_ERROR THEN
P_RETURN_STS := 'Server Error: ' || UTL_HTTP.GET_DETAILED_SQLERRM ;
WHEN UTL_HTTP.HTTP_CLIENT_ERROR THEN
P_RETURN_STS := 'Client Error: ' || UTL_HTTP.GET_DETAILED_SQLERRM;
WHEN OTHERS THEN
P_RETURN_STS := 'Client Error: ' || UTL_HTTP.GET_DETAILED_SQLERRM;*/
END INVOKE_BPEL_WS;
--------------------------------
Procedure CALL_BEM /* (--p_object_type IN VARCHAR2,
--p_process_name in varchar2,
--p_response out varchar2
)
*/
IS
l_endpoint_url VARCHAR2(200) :='http://172.10.0.00:10101/soa-infra/services/default/ShipmentBusinessRule/BusinessRuleProcess.service?WSDL';
l_soap_request VARCHAR2(30000) := NULL;
l_bpel_return_sts VARCHAR2(3000);
BEGIN
l_soap_request := '<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:bus="http://xmlns.oracle.com/bpmn/bpmnProcess/BusinessRuleProcess"><soapenv:Header/><soapenv:Body><bus:start><Transmission xmlns="http://xmlns.oracle.com/apps/otm" targetNamespace="http://xmlns.oracle.com/apps/otm"><TransmissionBody><GLogXMLElement><PlannedShipment xmlns="http://xmlns.oracle.com/apps/otm"><Shipment><ShipmentHeader><ShipmentGid><Gid><DomainName>DTS/EXE</DomainName><Xid>01409</Xid></Gid></ShipmentGid><ShipmentRefnum><ShipmentRefnumQualifierGid><Gid><Xid>GLOG</Xid></Gid></ShipmentRefnumQualifierGid><ShipmentRefnumValue>DTS/EXE.01409</ShipmentRefnumValue></ShipmentRefnum><ShipmentRefnum><ShipmentRefnumQualifierGid><Gid><Xid>BM</Xid></Gid></ShipmentRefnumQualifierGid><ShipmentRefnumValue>MAEU-000235</ShipmentRefnumValue></ShipmentRefnum><TransactionCode>NP</TransactionCode><ServiceProviderGid><Gid><DomainName>DTS/DATA</DomainName><Xid>MAEU</Xid></Gid></ServiceProviderGid><ServiceProviderAlias><ServiceProviderAliasQualifierGid><Gid><Xid>GLOG</Xid></Gid></ServiceProviderAliasQualifierGid><ServiceProviderAliasValue>DTS/DATA.MAEU</ServiceProviderAliasValue></ServiceProviderAlias><RateServiceGid><Gid><DomainName>DTS/PCM</DomainName><Xid>VESSEL_SERVICE</Xid></Gid></RateServiceGid><TransportModeGid><Gid><Xid>VESSEL-CO</Xid></Gid></TransportModeGid><TotalWeightVolume><WeightVolume><Weight><WeightValue>57.0</WeightValue><WeightUOMGid><Gid><Xid>KG</Xid></Gid></WeightUOMGid></Weight><Volume><VolumeValue>0.3</VolumeValue><VolumeUOMGid><Gid><Xid>CUMTR</Xid></Gid></VolumeUOMGid></Volume></WeightVolume></TotalWeightVolume><TotalShipUnitCount>8</TotalShipUnitCount><StartDt><GLogDate>20140217124300</GLogDate><TZId>Asia/Hong_Kong</TZId><TZOffset>+08:00</TZOffset></StartDt><EndDt><GLogDate>20140311234300</GLogDate><TZId>America/Chicago</TZId><TZOffset>-05:00</TZOffset></EndDt><SourceLocationRef><LocationRef><LocationGid><Gid><DomainName>DTS/DATA</DomainName><Xid>HKHKG</Xid></Gid></LocationGid></LocationRef></SourceLocationRef></ShipmentHeader></Shipment></PlannedShipment></GLogXMLElement></TransmissionBody></Transmission></bus:start></soapenv:Body></soapenv:Envelope>';
invoke_bpel_ws(l_endpoint_url,l_soap_request,l_bpel_return_sts );
dbms_output.put_line('----------'||l_bpel_return_sts);
end CALL_BEM;
end DTS_INT_BEM;
Thanks in advance for the help.
Shobz