Certificate validation failure (missing or misconfigured Wallet)

Symptoms: 

Your Oracle PLSQL routine is trying to access a https website and you get such an error:

Exception in "begin_request":

Error Stack: ORA-29273: HTTP request failed

ORA-29024: Certificate validation failure

ORA-06512: at "SYS.UTL_HTTP", line 380

ORA-06512: at "SYS.UTL_HTTP", line 1189

Backtrace: ORA-06512: at "SYS.UTL_HTTP", line 380

ORA-06512: at "SYS.UTL_HTTP", line 1189

Cause: 

This is because there is no handshaking with encryptation channel between your database connection and the website that only respondes via https.

Solution:

All you need to do is to download the cert files from the https website you are trying to access (preferebly via firefox that is much easier to download).

Figure 1

Figure 2.

Figure 3.

• So now, go to your server and create a specific directory for your wallet and create one:

mkdir /u01/oracle-wallet -p

• Copy your certificate files to this folder

cp $STAGE_DIR/certificados/*.pem /u01/oracle-wallet

• Create the wallet

cd /u01/oracle-wallet

orapki wallet create -wallet https_wallet -pwd <create_new_password> -auto_login

• Add the certificate files to the wallet

cd /u01/oracle-wallet

orapki wallet add -wallet https_wallet -cert <your-cert-file>.pem -trusted_cert -pwd <set_your_password_created>

• List all added certificates into the wallet

cd /u01/oracle-wallet/https_wallet

orapki wallet display -wallet .

• Certainly you will need to configure the ACL (Access Control List) from your database, adjusting the code below.

$ sqlplus system/<password_user>

BEGIN

    DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(

        host => '*',

        ace => xs$ace_type(privilege_list => xs$name_list('connect','resolve','http'),

                           principal_name => '<YOUR_SCHEMA_HERE>',

                           principal_type => xs_acl.ptype_db));

END;

/

• To test that is working, adapt your code to include the wallet path and password, such example:

DECLARE

  req   UTL_HTTP.req;

  resp  UTL_HTTP.resp;

BEGIN

  UTL_HTTP.SET_WALLET('file:<wallet_path_here>', '<your_password>';

  req := UTL_HTTP.begin_request('https://<https_address>');

  resp := UTL_HTTP.get_response(req);

  UTL_HTTP.end_response(resp);

END;

/

Hope this help.