“The simple things are also the most extraordinary things, and only the wise can see them.” – Paulo Coelho
There is a scary amount of amazing stuff in Oracle Database 23ai, but for me, you could take all of that away and leave me with just one particular feature that is in 23ai and I would still upgrade to this latest version the moment I could!
What is this one thing? Well, it is not really the addition of something, it is the removal of something. Confused? Let me explain with something that has prompted so many AskTOM questions over the years I’ve lost count.
I have a simple requirement: I just need to get some data from a website, and like any good website, it is accessed via HTTPS because we don’t want nasty people looking over our shoulders while we surf the net. I’ll use UTL_HTTP for that
SQL> set serverout on
SQL> declare
2 p_url varchar2(100) := 'https://www.oracle.com';
3 l_http_request utl_http.req;
4 l_http_response utl_http.resp;
5 l_text varchar2(32767);
6 begin
7 l_http_request := utl_http.begin_request(p_url);
8 l_http_response := utl_http.get_response(l_http_request);
9 begin
10 loop
11 utl_http.read_text(l_http_response, l_text, 32766);
12 dbms_output.put_line (l_text); exit;
13 end loop;
14 exception
15 when utl_http.end_of_body then
16 utl_http.end_response(l_http_response);
17 end;
18 end;
19 /
declare
*
ERROR at line 1:
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 1148
ORA-06512: at line 7
It doesn’t work. UTL_HTTP originated in a time where HTTP was the norm, and it had to evolve to handle modern security requirements. To access a site via SSL we need to have the correct certificates. So now I have to head to that website, and find its certificate. First I need to Google to work out how to that on my particular browser version
Then once I’ve worked that out, I need to export the certificate to a flat file. That in itself is prone to error, because I have to make I export the certificate at the root level, otherwise things might not work. Similarly, there are several export formats that are supported, and not all of them are necessarily supported to be used in my Oracle Wallet.
“Your what?” I hear you ask. Yes, a wallet. A special file that sits outside the database but still on the database server. So next I have to get that exported certificate file over to my database server and make sure that this is in a secure place but still backed up etc.
Now I have to create a wallet. Another set of Googling to work out whether its the “mkstore” command (now deprecated) or the “orapki” command. Then some more Googling to work out how to import my certificate file into the wallet. Once that is done, I’m ready to try again.
SQL> set serverout on
SQL> declare
2 p_url varchar2(100) := 'https://www.oracle.com';
3 l_http_request utl_http.req;
4 l_http_response utl_http.resp;
5 l_text varchar2(32767);
6 begin
7 utl_http.set_wallet('file:c:\oracle\wallet', 'MyWalletSecret999');
8 l_http_request := utl_http.begin_request(p_url);
9 l_http_response := utl_http.get_response(l_http_request);
10 begin
11 loop
12 utl_http.read_text(l_http_response, l_text, 32766);
13 dbms_output.put_line (l_text); exit;
14 end loop;
15 exception
16 when utl_http.end_of_body then
17 utl_http.end_response(l_http_response);
18 end;
19 end;
20 /
declare
*
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-28759: failure to open file
ORA-06512: at "SYS.UTL_HTTP", line 380
ORA-06512: at "SYS.UTL_HTTP", line 1148
ORA-06512: at line 8
Yup, I’ve done something wrong. Maybe it is the permissions on the wallet, or perhaps on the directory that the wallet is in. Maybe the path name format is not right. It is deliberately obtuse because that is what security is meant to be – we do not want to be giving would be attackers any clues that might help them bypass our controls. The database does not know that the “attacker” in this case is me! The person that wrote the damn code!!!
I play around some more and finally I think I’ve got the file specified correctly and the permissions correct and then:
SQL> set serverout on
SQL> declare
2 p_url varchar2(100) := 'https://www.oracle.com';
3 l_http_request utl_http.req;
4 l_http_response utl_http.resp;
5 l_text varchar2(32767);
6 begin
7 utl_http.set_wallet('file:c:\oracle\wallet', 'MyWalletSecret999');
8 l_http_request := utl_http.begin_request(p_url);
9 l_http_response := utl_http.get_response(l_http_request);
10 begin
11 loop
12 utl_http.read_text(l_http_response, l_text, 32766);
13 dbms_output.put_line (l_text); exit;
14 end loop;
15 exception
16 when utl_http.end_of_body then
17 utl_http.end_response(l_http_response);
18 end;
19 end;
20 /
declare
*
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-29106: Cannot import PKCS #12 wallet.
ORA-06512: at "SYS.UTL_HTTP", line 380
ORA-06512: at "SYS.UTL_HTTP", line 1148
ORA-06512: at line 8
If you have ever been frustrated I have been with the nuances of wallets and HTTPS and web service calls and the like, then you are going to love 23ai.
Let me start afresh on a 23ai instance
SQL> declare
2 l_url varchar2(100) := 'https://www.oracle.com';
3 l_req utl_http.req;
4 l_response utl_http.resp;
5 l_buffer varchar2(4000);
6 begin
7 l_req := utl_http.begin_request(l_url);
8 l_response := utl_http.get_response(l_req);
9 begin
10 loop
11 utl_http.read_text(l_response, l_buffer, 4000);
12 dbms_output.put_line (l_buffer); exit;
13 end loop;
14 exception
15 when utl_http.end_of_body then
16 utl_http.end_response(l_response);
17 end;
18 end;
19 /
<!DOCTYPE html><html lang="en-US"><head><meta charSet="utf-8"/>
...
...
PL/SQL procedure successfully completed.
Just…like…that…. No wallet, no orapki, no hassles!
With 23ai, the database can now utilise the certificates available at the operating system level. Since most operating systems need to support a browser talking to the internet, they come with the majority of commonly used certificate providers out there. There still might be the occasional need to tinker about with wallets like we currently do, but for most cases, we can now just call our HTTPS services with so much less fuss.
Enjoy!
Learning is not a spectator sport 
Leave a reply to Connor McDonald Cancel reply