Is anyone able to make the following work? ...
I am using UTL_HTTP to grab some info from at site which has recently enforced https so my process has stopped working with 'ORA-29024 Certificate validation error'.
I know that an Oracle wallet is required which contains the correct certificates but I've not been able to create one that works. I've been following the info in this link (amongst others):
http://blog.whitehorses.nl/2010/05/27/access-to-https-via-utl_http-using-the-orapki-wallet-command/
The site in question is https://www.aktietorget.se (as an aside, and I'm still wondering if this is having a bearing on what's going on, their certificate expires on 28th Dec 2017).
So you obviously have to create the database ACL's to allow the code to get out to the site. Done that, and would be getting a different error if this was the problem.
I've seen mention that database/wallet versions prior to 11.2 can't handle SHA2 certificates. My development database is XE but is 11.2.0.2. I'm dubious if this should work or not, so I've also been trying it on a cloud based production machine which is 12.1.0.2.
There is conflicting information as to whether you need to import the site certificate as well as the full chain of trust / CA certificates. I've tried both.
Every time I still get an ORA-29024 Certificate validation error.
Any hints/tips/suggestions or confirmation that someone is (or indeed, isn't) able to make it work in their environment would be much appreciated. I've spent too much time on this already and it's quite frustrating now.
Thanks in advance.
Regards
Adam
Here's the script for creating the ACL:
begin
dbms_network_acl_admin.create_acl (
acl => 'utl_http.xml',
description => 'HTTP Access',
principal => 'SCOTT',
is_grant => TRUE,
privilege => 'connect',
start_date => null,
end_date => null
);
dbms_network_acl_admin.add_privilege (
acl => 'utl_http.xml',
principal => 'SCOTT',
is_grant => TRUE,
privilege => 'resolve',
start_date => null,
end_date => null
);
dbms_network_acl_admin.assign_acl (
acl => 'utl_http.xml',
host => 'www.aktietorget.se',
lower_port => 443,
upper_port => 443
);
end;
PL/SQL Test script:
DECLARE
lvcReturn CLOB := NULL ;
req UTL_HTTP.REQ;
resp UTL_HTTP.RESP;
value VARCHAR2(1024);
BEGIN
UTL_HTTP.set_wallet('file:/user/oracle/aktitorget_wallet/', 'password1234');
BEGIN
req := UTL_HTTP.BEGIN_REQUEST('https://www.aktietorget.se/');
UTL_HTTP.SET_HEADER(req, 'User-Agent', 'Mozilla/4.0');
resp := UTL_HTTP.GET_RESPONSE(req);
LOOP
UTL_HTTP.READ_LINE(resp, value, TRUE);
lvcReturn := lvcReturn || value ;
END LOOP;
UTL_HTTP.END_RESPONSE(resp);
EXCEPTION
WHEN UTL_HTTP.END_OF_BODY THEN
UTL_HTTP.END_RESPONSE(resp);
WHEN OTHERS THEN
RAISE ;
END ;
dbms_output.put_line (lvcReturn);
END ;