I'm trying to use PL/SQL and UTL_HTTP to connect to a vendor's site (URL). My initial connection attempt is via a simple GET request, but eventually I will need to move on to using http and POST to transfer a file. I have been supplied with a URL by the vendor. If I plug the URL into a browser I get the following response:
<LiaisonServiceResponse xmlns:ns2="http://foo.url.com/xsd/request/" xmlns="http://foo.url.com/xsd/response/">
<ServiceName>service-router</ServiceName>
<ServiceVersion>13420</ServiceVersion>
<ServiceRequestDate>2014-10-21T09:34:53</ServiceRequestDate>
<ServiceRequestID>VB16811843</ServiceRequestID>
<ServiceResult>
<Success>true</Success>
<Message>Successfully submitted the document to DXP: messageID=UG16811844</Message>
</ServiceResult>
</LiaisonServiceResponse>
When I try this using PL/SQL based upon example code posted by Billy~Verreynne I get the following output involving an ACL error:
--
URL: http://foo.url.com/soi/rest/marvwd01409301219039/eastman_cust/service-router/marvin-eastman-to-dxp
Ref 1
ACL_ERROR DETAILED SQL ERROR CODE: -24247
ACL_ERROR DETAILED SQL ERROR MSG: ORA-24247: network access denied by access control list (ACL)
Call Stack: ----- PL/SQL Call Stack -----
object line object
handle number name
0x65a33e70 62 anonymous
block
Error Stack: ORA-24247: network access denied by access control list (ACL)
Error Backtrace: ORA-06512: at "SYS.UTL_HTTP", line 1128
ORA-06512: at line 25
Below is the code used. Line 25 equates to the BEGIN_REQUEST function. The vendor is looking for specific IP address ranges from us and I have confirmed that with regard to the IP addresses we are sending what they are expecting. There is no other authentication involved that I am aware of.
set serveroutput on size unlimited;
SET DEFINE OFF;
set echo on;
DECLARE
C_USER_AGENT constant varchar2(4000) := 'Mozilla/5.0+(Windows+NT+6.3;+WOW64)+AppleWebKit/537.36+(KHTML,+like+Gecko)+Chrome/33.0.1750.154+Safari/537.36';
Resp_v UTL_HTTP.RESP;
RespOut XMLTYPE;
Req_v UTL_HTTP.REQ;
ReadResp CLOB;
URL_v VARCHAR2(500);
FExist BOOLEAN;
FLen NUMBER;
FNamePath VARCHAR2(750);
BSize BINARY_INTEGER;
Buffer_v VARCHAR2(32767);
EndOFile BOOLEAN;
BEGIN
URL_v := 'http://foo.url.com/soi/rest/marvwd01409301219039/eastman_cust/service-router/marvin-eastman-to-dxp';
--
DBMS_OUTPUT.PUT_LINE('URL: '||URL_v);
UTL_HTTP.SET_RESPONSE_ERROR_CHECK(TRUE);
UTL_HTTP.SET_DETAILED_EXCP_SUPPORT(TRUE);
UTL_HTTP.SET_COOKIE_SUPPORT(TRUE);
UTL_HTTP.SET_TRANSFER_TIMEOUT( 10 );
UTL_HTTP.SET_FOLLOW_REDIRECT( 3 );
UTL_HTTP.SET_PERSISTENT_CONN_SUPPORT( TRUE );
DBMS_OUTPUT.PUT_LINE('Ref 1');
Req_v := UTL_HTTP.BEGIN_REQUEST(url => URL_v, method => 'GET', http_version => utl_http.HTTP_VERSION_1_0);
--
DBMS_OUTPUT.PUT_LINE('2');
UTL_HTTP.SET_HEADER( Req_v, 'User-Agent', C_USER_AGENT );
UTL_HTTP.SET_HEADER(Req_v, 'Content-Type', 'text/xml; charset=utf-8');
--UTL_HTTP.SET_HEADER(Req_v, 'Content-Length', FLen);
Resp_v := UTL_HTTP.GET_RESPONSE(Req_v);
DBMS_OUTPUT.PUT_LINE('5');
DBMS_LOB.CREATETEMPORARY(ReadResp, TRUE);
EndOFile := FALSE;
LOOP
EXIT WHEN EndOFile;
BEGIN
UTL_HTTP.READ_LINE(Resp_v, Buffer_v);
IF LENGTH(Buffer_v) > 0 THEN
DBMS_LOB.WRITEAPPEND(ReadResp, LENGTH(Buffer_v), Buffer_v);
END IF;
EXCEPTION WHEN UTL_HTTP.END_OF_BODY THEN
DBMS_LOB.WRITEAPPEND(ReadResp, LENGTH(Buffer_v), Buffer_v);
UTL_HTTP.END_RESPONSE(Resp_v);
EndOFile := TRUE;
END;
END LOOP;
UTL_HTTP.END_RESPONSE(Resp_v);
-- UTL_HTTP.END_REQUEST(Req_v);
DBMS_OUTPUT.PUT_LINE('Persistent Conn: '||TO_CHAR(UTL_HTTP.GET_PERSISTENT_CONN_COUNT));
DBMS_OUTPUT.PUT_LINE(ReadResp);
DBMS_LOB.FREETEMPORARY(ReadResp);
EXCEPTION
WHEN UTL_HTTP.NETWORK_ACCESS_DENIED THEN
DBMS_OUTPUT.PUT_LINE('ACL_ERROR DETAILED SQL ERROR CODE: '||TO_CHAR(UTL_HTTP.GET_DETAILED_SQLCODE));
DBMS_OUTPUT.PUT_LINE('ACL_ERROR DETAILED SQL ERROR MSG: '||UTL_HTTP.GET_DETAILED_SQLERRM);
DBMS_OUTPUT.PUT_LINE('Call Stack: '||DBMS_UTILITY.FORMAT_CALL_STACK||Chr(10));
DBMS_OUTPUT.PUT_LINE('Error Stack: '||DBMS_UTILITY.FORMAT_ERROR_STACK||Chr(10));
DBMS_OUTPUT.PUT_LINE('Error Backtrace: '||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE||Chr(10));
IF Req_v.url IS NOT NULL THEN
UTL_HTTP.END_REQUEST(Req_v);
END IF;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('SQL ERROR CODE: '||TO_CHAR(UTL_HTTP.GET_DETAILED_SQLCODE));
DBMS_OUTPUT.PUT_LINE('SQL ERROR MSG: '||UTL_HTTP.GET_DETAILED_SQLERRM);
DBMS_OUTPUT.PUT_LINE('Call Stack: '||DBMS_UTILITY.FORMAT_CALL_STACK||Chr(10));
DBMS_OUTPUT.PUT_LINE('Error Stack: '||DBMS_UTILITY.FORMAT_ERROR_STACK||Chr(10));
DBMS_OUTPUT.PUT_LINE('Error Backtrace: '||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE||Chr(10));
UTL_HTTP.END_REQUEST(Req_v);
END;
/
show errors
SET DEFINE ON;
set echo off;
Thanks for your help in advance
Paul