Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Help with wallet for UTL_HTTP (s)

MonkeyThinkDec 5 2017 — edited Dec 5 2017

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 ;

This post has been answered by Vlad Visan-Oracle on Dec 5 2017
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jan 2 2018
Added on Dec 5 2017
8 comments
835 views