Cannot reach web service with Oracle PL/SQL

Question

I would call a web service from PL/SQL Oracle 11g but the following script is not working:

declare
v_Endpoint varchar2(500) := '<my_endpoint>';
v_Http_Req Utl_Http.Req;
begin
Utl_Http.Set_Wallet('<my_wallet_path>','<my_wallet_password>');
Utl_Http.Set_Proxy('<proxy_url>:<proxy_password>@<proxy-url>:8080');
v_Http_Req := Utl_Http.Begin_Request(v_Endpoint, 'POST', 'HTTP/1.1');

exception
when others then
DBMS_OUTPUT.PUT_LINE('EXCEPTION: '||SQLERRM);
DBMS_OUTPUT.PUT_LINE('EXCEPTION: '||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
DBMS_OUTPUT.PUT_LINE('EXCEPTION: '||UTL_HTTP.GET_DETAILED_SQLERRM);
end;

EXCEPTION: ORA-29273: richiesta HTTP non riuscita
ORA-06512: a "SYS.UTL_HTTP", line 1130
ORA-53203: violazione di sicurezza
EXCEPTION: ORA-06512: a "SYS.UTL_HTTP", line 1130
ORA-06512: a line 7

EXCEPTION: ORA-53203: violazione di sicurezza

Procedura PL/SQL completata correttamente.

The connection to the endpoint is possible with SOAPUI without problems. I can open the URL in a browser with the proxy <proxy_url>:8080/script_proxy.pac The wallet contains the certificate that has been downloaded from . I've inserted the URL test.salesforce.com in the ACL and granted access to the user that runs my script.

How can I figure out the issue?

Answer 1

You probably need to define an ACL to access external network services. Check the output of:

SELECT * FROM DBA_NETWORK_ACLS;
SELECT * FROM DBA_NETWORK_ACL_PRIVILEGES;

And check the documentation to the CREATE_ACL Procedure:

DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
   acl             IN VARCHAR2,
   description     IN VARCHAR2,
   principal       IN VARCHAR2,
   is_grant        IN BOOLEAN,
   privilege       IN VARCHAR2,
   start_date      IN TIMESTAMP WITH TIMEZONE DEFAULT NULL,
   end_date        IN TIMESTAMP WITH TIMEZONE DEFAULT NULL );