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>
Thanks. It helped me a lot and I successfully created wallet for at least two servers. However I have a problem with third server/website and it is throwing:
ORA-29273: HTTP request failed
ORA-06512: at “SYS.UTL_HTTP”, line 1722
ORA-28857: Unknown SSL error
ORA-06512: at line 1
I tried many times from scratch and even created a wallet from a non-xe version and it also throws the same error. Any ideas?
Hi! Thanks for dropping by!
I have come across this myself a few times. Unfortunately, it seems to me that the wallet sometimes does not play nice with certain certificates. I have not yet able to identify the root cause.
I found the cause. Oracle up to 11.2.0.2 (which Oracle XE is) doesn’t support SHA-2 based certificates. In non-XE version the solution is to apply patch or upgrade to the latest version but unfortunately with XE it is not an option. I therefore set up Apache Reverse Proxy pointing to https traffic and then called Apache server in https from Oracle. In this case you don’t need a wallet anymore.
Hi, I would like to use the Apache Reverse Proxy solution to eliminate the wallet. Can you explain me how you achieved this?
Thanks!
Thanks
Hello, Could you please share setting up the reverse proxy with us?
Regards, Kim
To use the Apache Reverse Proxy first install Apache. If you want to access https://api.paypal.com with UTL_HTTP you need to create something like following in httpd.conf (Apache configuration file).
SSLProxyEngine On
ProxyPass /paypal https://api.paypal.com
ProxyPassReverse /paypal https://api.paypal.com
Now you can access it with http:///paypal . Since this will be http and not https there will be no need to use Oracle Wallet
You can also using nginx as the reverse proxy.
https://orclcs.blogspot.ca/2017/11/ssl-reverse-proxy-using-nginx.html
hi, could you please tell me how did you manage the Apache Reverse Proxy approach? I’m facing the same problem that you, please let me know. Thanks!
The reverse proxy solution works perfect!
Just added this to httpd.conf:
SSLProxyEngine on
ProxyPass /my-selected-merchant-site https://api.my-selected-merchant-site.com
ProxyPassReverse /my-selected-merchant-site https://api.my-selected-merchant-site.com
*make sure that you have mod_ssl installed (with default options) and “ProxyRequests Off”
Aftar that, I change the package from this:
utl_http.begin_request(‘https://api.my-selected-merchant-site.com/payment’,’POST’,’HTTP/1.1′);
to this:
utl_http.begin_request(‘http://myhost/my-selected-merchant-site/’,’POST’,’HTTP/1.0′);
And forget about the wallet.
Thanks!!!
Excelent! it works like hell!!!
Thanks Leo. It works!
Hello
My oracle express edition version is 11.2.0.2 and
In your example you solve the https problem in oracle XE and i have performed same steps but i also got error of
ORA-29273: HTTP request failed
ORA-06512: at “SYS.UTL_HTTP”, line 1722
ORA-28857: Unknown SSL error
ORA-06512: at line 1
so how can i solve this ?
please help me
thanks in advance
The workaround is the Proxypass example given above.
Thanks Zulqarnain
But is there any way to do it with only oracle wallet ?
HI!
You wrote:
1. “You First, grab the certificate and all the certificates in its chain by using a browser:
”
So I understand, we need to grab = certificate + all the certificates.
2. “Select the certificate at the bottom of the chain (www.oracle.com) and click ‘Export…”. and “Change the ‘Save as type’ to ‘X.509 Certificate with chain (PEM)’ and save the certificates to a file.”
So, the question is: How many certs we have to grab and how many files to create for further final step with openssl command? If many, should we combine the grabbed certs to the single file? Please, explain!
Soap url failing with below error from 11g DB using wallet
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-06512: at “SYS.UTL_HTTP”, line 1720
ORA-12541: TNS:no listener
ORA-06512: at line 1
ORA-29273: HTTP request failed
ORA-06512: at “SYS.UTL_HTTP”, line 1722
ORA-29106: Cannot import PKCS #12 wallet.
ORA-06512: at line 1
29273. 00000 – “HTTP request failed”
*Cause: The UTL_HTTP package failed to execute the HTTP request.
*Action: Use get_detailed_sqlerrm to check the detailed error message.
Fix the error and retry the HTTP request.
In your example of connecting to https://www.oracle.com, you say to export the “bottom” server certificate and import/add it into the wallet.
This is wrong, you should not add the site’s server certificate to a truststore.
When creating a truststore you should only add the root CA certificate, i.e. the “top” one “GeoTrust Global CA”.
Hope this helps…
Hi,
My Oracle XE is installed on windows 7 professional , I followed your steps mention
and i am able to create ewallet.p12 file using openssl but still database is keep throwing Certificate validation failure when call https url.
Please help
Regards,
Nand