If you have used the UTL_HTTP package in PL/SQL to call upon external web pages or services, you might have seen following error message come by:
ORA-29273: HTTP request failed
ORA-06512: at “SYS.UTL_HTTP”, line 1130
ORA-29024: Certificate validation failure
It indicates that the web site you are trying to access is in fact SSL encrypted and requires a valid certificate to read.
Most modern browsers download this automatically when visiting any encrypted page, but to do this in a PL/SQL procedure, a couple of manual steps need to be taken.
This is where Oracle Wallet Manager comes in. It is shipped with the DBMS software and can typically be found in the $ORACLE_HOME/bin folder.
What this program does, is to facilitate the process of storing certificates in a single file in PKCS #12 format, called a Wallet.
Next, it suffices to simply add a reference to the wallet in your PL/SQL code to be able to use these certificates when accessing SSL encrypted content.
Let’s take the following piece of PL/SQL code:
DECLARE lo_req UTL_HTTP.req; lo_resp UTL_HTTP.resp; BEGIN lo_req := UTL_HTTP.begin_request('http://www.google.be'); lo_resp := UTL_HTTP.get_response(lo_req); dbms_output.put_line(lo_resp.status_code); UTL_HTTP.end_response(lo_resp); END; /
This will output the status code “200”. This implies the page returned HTTP 200: OK, indicating the request succeeded.
From the command line, start the wallet manager by simply entering ‘owm’ after having set the correct Oracle environment parameters.
In Windows the program can be found in the start menu under /<oracle version folder>/Integrated Management Tools/Wallet Manager.
A prompt is shown to enter a password for the Wallet. For this example, we’ll use “test1234”.
After entering the password, a prompt asks to create a new certificate request, which we will not do for now.
By default, a number of trusted certificates are created within the wallet. These can be seen in the overview screen.
First thing to do, is to export the actual certificate from this website, together with the rest of the trust chain. The easiest way to do this is by using a regular browser application. Following screenshots are from Firefox, but a similar approach can be taken with Internet explorer or Chrome.
Finally, save the wallet to a desired location. Make sure it’s a folder that can be accessed by the Oracle user, and is preferably not accessible by unauthorized users!
In this example, /home/oracle/Wallet/ is chosen.
Now we can return to our SQL*plus session. One extra statement should be added to the script:
UTL_HTTP.SET_WALLET (‘file:<path to the wallet folder, don’t include the filename!>’,<the wallet password>);
This statement should be executed before the begin_request step.
Here’s the full output of the script:
[oracle@myorcl12c ~]$ sqlplus / as sysdba SQL*Plus: Release 220.127.116.11.0 Production on Tue Aug 27 14:11:43 2013 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 18.104.22.168.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> SET SERVEROUTPUT ON SQL> DECLARE 2 lo_req UTL_HTTP.req; 3 lo_resp UTL_HTTP.resp; 4 BEGIN 5 UTL_HTTP.SET_WALLET ('file:/home/oracle/Wallet/','test1234'); 6 lo_req := UTL_HTTP.begin_request('https://www.google.com'); 7 lo_resp := UTL_HTTP.get_response(lo_req); 8 dbms_output.put_line(lo_resp.status_code); 9 UTL_HTTP.end_response(lo_resp); 10 END; 11 / 200 PL/SQL procedure successfully completed. SQL>
In a follow up post, I will explain how to achieve the same result using the orapki command line utility instead of Wallet manager.