UTL_HTTP and SSL (HTTPS) using Oracle Wallets

 

1- Install Wallets in Oracle

In order to make connections to a secured resource, we need to get the necessary certificate.

Using the browser, go to the URL you are attempting to access from PL/SQL.

In this case "https://allotaxi.com.lb/".

Click the lock icon in the URL bar to display the certificate menu and click on the "Connection" tab.

Click the "Certificate information" link and click the "Certification Path" tab on the resulting dialog.

For the root node in the "Certification path", highlight the node and click the "View Certificate" button.

On the resulting dialog, click the "Details" tab and click the "Copy to File..." button to save the certificate information.

On the resulting wizard, do the following.

●     Click the "Next" button on the welcome screen.

●     Select the "wallet (.CER)" option and click the "Next" button. Other formats work, but I've found this to be the most consistent.

●     Enter suitable file name and click the "Next" button.

●     Click the "Finish" button.

2- Create an Oracle Wallet Containing the Certificates

Create a new location to hold the wallet.

$ mkdir -p /u01/app/oracle/admin/12c/wallet

Create a new wallet.

$ orapki wallet create -wallet /u01/app/oracle/admin/12c/wallet -pwd WalletPasswd123 -auto_login

Test Secured Connection.

We are now ready to access the secured resource, but we must provide the UTL_HTTP package with the wallet details so it can make the secured connections. This is done using the UTL_HTTP.SET_WALLET procedure.

SET SERVEROUTPUT ON

EXEC UTL_HTTP.set_wallet('file:/u01/app/oracle/admin/12c/wallet', 'WalletPasswd123');

EXEC show_html_from_url('https://allotaxi.com.lb/');

 

... HTML output removed ...

 

PL/SQL procedure successfully completed.

 

SQL>

3- Authentication

If you are accessing a site that requires authentication, you will need to do one of two things depending on the type of authentication used.

If the site uses basic authentication, simply specify the credentials in the call to SHOW_HTOM_FROM_URL, which will use them in the UTL_HTTP.SET_AUTHENTICATION call.

SET SERVEROUTPUT ON

EXEC UTL_HTTP.set_wallet('file:/u01/app/oracle/admin/12c/wallet', 'WalletPasswd123');

EXEC show_html_from_url('https://allotaxi.com.lb/', 'username', 'password');

 

... HTML output removed ...

 

PL/SQL procedure successfully completed.

 

SQL>

If the page uses digest authentication, then you will need to will need to install the digest_auth_api package.

Test code.

SET SERVEROUTPUT ON

EXEC show_html_from_url('https://allotaxi.com.lb/', 'username', 'password', '/u01/app/oracle/admin/12c/wallet', 'WalletPasswd123');

 

... HTML output removed ...

 

PL/SQL procedure successfully completed.

 

SQL>

4- Firewall port 443 needs to be opened for outbound.