In Oracle, the UTL_HTTP package is used to fetch pages from the internet directly by the database. For example, using the UTL_HTTP.REQUEST function, the first 2000 bytes of a web page can be displayed:
SQL> SET PAGESIZE 100
SQL> select UTL_HTTP.REQUEST('http://www.google.com') from dual;
UTL_HTTP.REQUEST('HTTP://WWW.GOOGLE.COM')
--------------------------------------------------------------------------------
<!doctype html><html itemscope="" itemtype="http://schema.org/WebPage"><head><me
ta content="Search the world's information, including webpages, images, videos a
nd more. Google has many special features to help you find exactly what you're l
ooking for." name="description"><meta content="noodp" name="robots"><meta itempr
op="image" content="/images/google_favicon_128.png"><title>Google</title><script
>(function(){
window.google={kEI:"X057UtKqGYvprQeorYDgAQ",getEI:function(a){for(var b;a&&(!a.g
etAttribute||!(b=a.getAttribute("eid")));)a=a.parentNode;return b||google.kEI},h
ttps:function(){return"https:"==window.location.protocol},kEXPI:"17259,146147,40
00116,4006293,4006727,4007231,4007661,4007714,4007830,4008067,4008133,4008142,40
08365,4008725,4009033,4009378,4009565,4009663,4009671,4010061,4010806,4010899,40
10938,4011063,4011107,4011125,4011228,4011256,4011258,4011334,4011376,4011522,40
11524,4011525,4011529,4011559,4011619,4011683,4011709,4011863,4011872,4011911,40
11971,4012001,4012003,4012011,4012090,4012095,4012141,4012145,4012147,4012302,40
12306,4012316,4012318,4012342,4012361,4012365,4012422,4012544,4012585,4012593,40
12632",kCSI:{e:"17259,146147,4000116,4006293,4006727,4007231,4007661,4007714,400
7830,4008067,4008133,4008142,4008365,4008725,4009033,4009378,4009565,4009663,400
9671,4010061,4010806,4010899,4010938,4011063,4011107,4011125,4011228,4011256,401
1258,4011334,4011376,4011522,4011524,4011525,4011529,4011559,4011619,4011683,401
1709,4011863,4011872,4011911,4011971,4012001,4012003,4012011,4012090,4012095,401
2141,4012145,4012147,4012302,4012306,4012316,4012318,4012342,4012361,4012365,401
2422,4012544,4012585,4012593,4012632",ei:"X057UtKqGYvprQeorYDgAQ"},authuser:0,ml
:function(){},kHL:"en",time:function(){return(new Date).getTime()},log:function(
a,b,c,l,k){var d=new Image,f=google.lc,e=google.li,g="",h="gen_204";k&&(h=
k);d.onerror=d.onload=d.onabort=function(){delete f[e]};f[e]=d;c||-1!=b.search("
&ei=")||(g="&ei="+google.getEI(l));c=c||"/"+h+"?atyp=i&ct="+a+"&cad=
SQL>
However, for pages served with SSL i.e. the https protocol, UTL_HTTP will need access to an Oracle Wallet that stores the trusted certificates for the encryption. Attempting to connect without a wallet will result in a certificate validation error:
SQL> select UTL_HTTP.REQUEST('https://support.oracle.com') from dual;
select UTL_HTTP.REQUEST('https://support.oracle.com') from dual
*
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1722
ORA-29024: Certificate validation failure
ORA-06512: at line 1
SQL>
An Oracle Wallet can be created by the Oracle Wallet Manager program. Unfortunately, the Oracle Wallet Manager program is not distributed with Oracle XE. I think this is because historically encryption has been part of Oracle Advanced Security, a paid optional Oracle feature. The good news is SSL/TLS encryption is no longer part of Oracle Advanced Security, as per the Oracle 11gR2 Licensing Guide:
Network encryption (native network encryption and SSL/TLS) and strong authentication services (Kerberos, PKI, and RADIUS) are no longer part of Oracle Advanced Security and are available in all licensed editions of all supported releases of the Oracle database.
So far we have established that using TLS/SSL with Oracle XE is not violating any Oracle licensing issue. We just have to find a way to create the wallet to be used by Oracle XE. Of course you can run Oracle Wallet Manager from a non-XE Oracle installation somewhere, and transfer the wallet to your Oracle XE installation. But what if you do not have access to any properly licensed installation of Oracle?
From the Oracle 11gR2 Advanced Security Guide:
Oracle Wallet Manager stores X.509 certificates and private keys in PKCS #12 format, and generates certificate requests according to the PKCS #10 specification. These capabilities make the Oracle wallet structure interoperable with supported third-party PKI applications and provide wallet portability across operating systems.
Therefore, we can use OpenSSL to create a PKCS #12 archive and it should work with Oracle XE.
In Oracle terminology, a wallet is a directory containing a file named ewallet.p12. Therefore, the PKCS #12 file must be named as such.
In this example I will use the Oracle Support web page as the target page. First, grab the certificate and all the certificates in its chain by using a browser. In Firefox, this is done by clicking the padlock icon in the address bar, then clicking on ‘More Information…’.
Then click on ‘View Certificate’
Go to the ‘Details’ tab
Select the certificate at the bottom of the chain (www.oracle.com) and click ‘Export…’.
Change the ‘Save as type’ to ‘X.509 Certificate with chain (PEM)’ and save the certificates to a file.
Transfer this file (mine is named www.oracle.crt) to your Oracle XE server where you want your wallet to be (I chose /u01/app/oracle/wallet).
Now use OpenSSL’s pkcs12 command to create the wallet file using the certificate file as input. Use the -export flag to create a PKCS #12 file instead of reading one, and use the -nokeys flag to indicate that a private key does not exist inside the input file. Enter a suitable password when prompted. This password will have to be provided when using UTL_HTTP later.
[oracle@oraclexe ~]$ cd /u01/app/oracle/wallet [oracle@oraclexe wallet]$ openssl pkcs12 -export -in www.oracle.crt -out ewallet.p12 -nokeys Enter Export Password: Verifying - Enter Export Password: [oracle@oraclexe wallet]$
Now we can make SSL calls to the Oracle Support website from the database by providing the wallet and password to UTL_HTTP:
SQL> set pagesize 100
SQL> select UTL_HTTP.REQUEST('https://support.oracle.com',null,'file:/u01/app/oracle/wallet','password') Output from dual;
OUTPUT
--------------------------------------------------------------------------------
<HTML>
<HEAD>
<title>Oracle Configuration Support Manager</title>
<meta http-equiv="REFRESH" content="0;url=/epmos/faces/MosIndex.jspx"></HEAD>
<BODY>
</BODY>
</HTML>